help with run-time error 3131

[mushmoom]

i am try to write a find fun i am useing the fellow code but it keep giving me runtime error 3131

Set db = CurrentDb

Set rstOrder = New ADODB.Recordset
rstOrder.Open "table1", CurrentProject.Connection, adOpenStatic, adLockOptimistic
Set rst = db.OpenRecordset("Select * From " & table1 & " where PoNumber = ") & Trim(Ponumber)


help

thanks
mushmoom

 

[Joe4]

Good luck. Post back if you still encounter problems.

If you do post back, please post your updated code, and the results from the new message box.

 

[mushmoom]

thanks mate but it still dosen't work i used the following code but it gives me like only the 2 fields but rest are blank. so i went back to access built code and thats not working either .

but i did manage to get my update working with out it creatting the record every time i enter something new here is my code for updating and for finding the record.


this is to update

Private Sub cmdamd_Click()

Dim rstOrder As ADODB.Recordset
Set rstOrder = New ADODB.Recordset
rstOrder.Open "table1", CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
With rstOrder
.Update
.Fields("PoNumber") = Ponumber
.Fields("requested by") = Requested_by.Column(0)
.Fields("cost description") = Cost_Description.Column(1)
.Fields("date") = Date
.Fields("Expense Code") = Expense_Code.Column(1)
.Fields("suppliers") = Suppliers.Column(0)
.Fields("telehone no") = Telephone_No
.Fields("Cost code") = Cost_Code
.Fields("expense code description") = Expense_Code_Description
.Fields("Supplier contact") = Supplier_Contact
.Fields("invoice number") = Invoice_Number
.Fields("Payment date") = Payment_Date
.Fields("Detail1") = Detail1
.Fields("Detail2") = Detail2
.Fields("detail3") = Detail3
.Fields("detail4") = Detail4
.Fields("detail5") = Detail5
.Fields("detail6") = Detail6
.Fields("net1") = Net1
.Fields("net2") = Net2
.Fields("net3") = Net3
.Fields("net4") = Net4
.Fields("net5") = Net5
.Fields("net6") = Net6
.Fields("total net") = Total_Net
.Fields("VAT") = VAT
.Fields("gross") = Gross
.Fields("suppcode") = suppcode
.Fields("Supplier payment") = supplier_payment
.Update
End With
End If

rstOrder.Close


ClearForm
End Sub


and i am trying to use this to find the record which is MS access code but it still not working.


On Error GoTo Err_Command44_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command44_Click:
Exit Sub

Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click


End Sub

 

[Joe4]

I am going to step back here a minute. I just want to inquire why you are using ADODB to add and find records? Can't you just create an entry Form directly from the Table itself instead of using ADODB?

Also, for searching, I usually use a different method (I don't think I have ever used ADODB). In this thread here, I posted some code for finding records using a different method. See my second to last post with the code for Finding Records:
http://www.mrexcel.com/forum/showthread.php?t=308630&page=2

Hope that helps.

 

[Andrew Fergus]

Is DLookUp an option? If you are wanting to return some particular fields from a table for a particular record, then the DLookup should work just as well. No ADO or DAO is needed when using the DLookup function.

HTH, Andrew