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]

A search of the web reveals that error 3131 indicates a syntax error in your FROM clause.

It looks to me that you have your right parend in the wrong place. I think it should look like:

Set rst = db.OpenRecordset("Select * From " & table1 & " where PoNumber =" & Trim(Ponumber))

Also, be sure that your table1 and Ponumber fields are defined and set to something valid.

One good way to see if your SQL statement is valid is to send it to a message box so that you can inspect it, i.e.

MsgBox "Select * From " & table1 & " where PoNumber =" & Trim(Ponumber))

 

[mushmoom]

thanks mate it is got the same error message comming up let me give you all my find code have a look where i am going wrong .


thnaks



If Ponumber.Value = "" Then MsgBox "Nothing To Search": Exit Sub


Set db = CurrentDb

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


With rst
Ponumber = !Ponumber
Date = !Date
Requested_by = !Requested_by
Cost_Description = !Cost_Description
Expense_Code = !Expense_Code
Suppliers = !Suppliers
Telephone_No = !Telephone_No
Cost_Code = !Cost_Code
Expense_Code_Description = !Expense_Code_Description
Supplier_Contact = !Supplier_Contact
Detail1 = !Detail1
Detail2 = !Detail2
Detail3 = !Detail3
Detail4 = !Detail4
Detail5 = !Detail5
Detail6 = !Detail6
Net1 = !Net1
Net2 = !Net2
Net3 = !Net3
Net4 = !Net4
Net5 = !Net5
Net6 = !Net6
Total_Net = !Total_Net
VAT = !VAT
Gross = !Gross

End With

 

[Joe4]

I do not see the value of the "table1" variable defined anywhere in your code.

The way you way written your statement, it is looking for a variable named "table1". If "table1" is actually the name of your table and not a variable, then try writing it like this:

Set rst = db.OpenRecordset("Select * From [table1] where PoNumber =" & Trim(Ponumber))

If you place the second set of code in my first post (the message box) just before this statement, you can see exactly what you are building and that kind of error should be very evident.

 

[Joe4]

A programming tip:

If you place the phrase "Option Explicit" at the very top of your module before your first Sub Procedure/Function, this will require you to declare all variables, i.e.

Dim Ponumber as String

What this then does is if you try to use a variable that you haven't declared, or if you misspell a variable name, you will get an error message telling you that the particular variable has not been defined.

Its a great help to the debugging process, and many programmers use it all the time.

 

[mushmoom]

i am going bonkers now

it is giving me runtime error 3061
too few parameter.expected 2

what does that mean i am really going bonkers here help

 

[Joe4]

Can you please post here what the message box is returning so we can see what code it is trying to run? Make sure the Select statement is being built the same way that your OpenRecordset statement is, so we get a true depiction of what is happening.

 

[mushmoom]

Select * From where PoNumber = DO17194495


this this the message comming up when i click on the find button

 

[Joe4]

See how there is no table name after the word "FROM"? Just as I was alluding to earlier, the way it is written it is looking for a variable named "table1" which does not exist because you have not defined it. You either need to define the variable, or make it hard-coded like I showed you in an earlier post.

Also, since your Ponumber is a text variabler, you need to surround it values with quotes. You can do this with CHR(34).

So, if you write it like this:

Set rst = db.OpenRecordset("Select * From [table1] where PoNumber =" Chr(34) & Trim(Ponumber) & Chr(34))

It should return a statement looking like this, which should be a valid SQL statement:

Select * From [table1] where PoNumber = "DO17194495"

Edit: If you want to see this, make sure you update the code in BOTH the "Set" statement and "MsgBox" statement.

 

[mushmoom]

Thanks mate

i am going to try this and let you know how i get on .


thanks for the help

Mushmoom