VBA OpenRecordset

[Jamster45]

Hi There,

I want to retrieve a value from a table. The value have to go to an other field on the same form.

However, its not working because of error ''syntax error, operator is missing".

The value in the table (FactuurFactuurnummerFN) is text and contains letters and numbers.

Here is the code:

Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM factuur WHERE FactuurFactuurnummerFN=" & Me.factuurnummerFN)

Me.StatusFactuur = rs!Factuur_status
End sub

Can someone help me and explain whats going wrong?

 

[MajP]

I am assuming you want tableName.FieldName
Factuur.FactuurnummerFN
but since only one table the tablename is not needed.

 

[Gasman]

Why grab all that data, when a DLookup would work just as well.?
Alternatively, just use Select Factuur_status

 

[The_Doc_Man]

As to the error, is there any chance that the Me.factuurnummerFN control happens to be blank at the moment this is run? Or can it contain quotes or apostrophes as a form of punctuation? Or can there be a space embedded in the control's contents? For the latter, I don't mean leading or trailing spaces, but rather something in the middle of the imported string.

Whenever you are doing substitution from a form or something you have input another way, you are bringing something "foreign" into the string that you are building. If you don't take steps to protect yourself from unexpectedly oddball strings, you invite trouble.

"Missing operator" USUALLY means there is an extraneous character somewhere and you have confused the string parser.

 

[Jamster45]

Ok guys, Thanks for your advice.

I was missing some quotes, stupid me

 

[Gasman]

Ok guys, Thanks for your advice.

I was missing some quotes, stupid me

I tend to put my constructed sql into a string, then debug.print it to see if it turns out as I expect it should.

 

[The_Doc_Man]

Excellent advice, Gasman!