VBA OpenRecordset (1 Viewer)

Jamster45 · Mar 24, 2019

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 · Mar 24, 2019

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

Gasman · Mar 24, 2019

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

The_Doc_Man · Mar 24, 2019

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 · Mar 24, 2019

Ok guys, Thanks for your advice.

I was missing some quotes, stupid me

Gasman · Mar 24, 2019

Jamster45 said:
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 · Mar 24, 2019

Excellent advice, Gasman!

VBA OpenRecordset (1 Viewer)

Jamster45

New member

MajP

You've got your good things, and you've got mine.

Gasman

Enthusiastic Amateur

The_Doc_Man

Immoderate Moderator