Syntax Error 3075 - works on one form but not the other!

[wheeledgoat]

I would greatly appreciate some help on this error I'm getting:

Run-time error '3075':
Syntax error (missing operator) in query expression 'visitID = '.

In the linked database, this code runs on the click event of the UPDATE cmd button:

DoCmd.OpenForm "frmUpdate", , , "visitID = " & visitID

It works from frmFrontDesk, but not from frmExam - which is completely infuriating me because I can't distinguish a meaningful difference between them.

I cannot for the life of me figure out why it works for one form but not the other!!! :banghead:

Any help would be GREATLY appreciated. I don't want to pull out any more hair. And I already shot the dog.

Surprisingly, the db's file size of 6mb exceeds the forum's upload limit of 2. I put it on google drive.
Sigh. My post count isn't high enough to put links in a message. Please cut and paste... Sorry.

https://drive.google.com/open?id=1s6yHuFK_mWXhJNCMb6ShUhuXZ2lUX-MT

Thank you!!

 

[theDBguy]

Hi. Try changing it to this:

, "visitID=" & Nz(Me.visitID,0)

Hope it helps...

 

[CJ_London]

responders are not keen to go to other sites to download stuff - particularly from new users.

you need to remove from your db all tables/forms etc which are not relevant to your question, from what's left, remove surplus records, then compact it and finally zip it - then you should be able to upload.

With regards your error, this

Syntax error (missing operator) in query expression 'visitID = '.

is saying you have not supplied an ID

 

[Mark_]

Where are you putting a value into visitID and where is it declared?

 

[The_Doc_Man]

When something works from point A but not from point B, there is a scope/visibility difference between the two points. But the question doesn't stop there. Others have suggested that you didn't supply a value for visitID or that it is not declared. Those are worthy of consideration, but I am bothered by the fact that it claims you have a missing OPERATOR, not a missing operand. Those ARE different error message with different numbers in Access, and it is rare for Access to not recognize the difference.

As a suggestion to let you show us at least SOME code context, be sure to enable error notifications (or don't disable them) and then for the offending forms, disable any error handlers. Run the one the fails and let it take the error. When you get the popup that offers it, click DEBUG. Then take a screen shot, save that using the PAINT utility (use output format .JPG or .WMF for space reasons), zip THAT, and upload it. Then we can see the error in its proper context.

 

[JHB]

Put Me! in the front of the code then it works.

DoCmd.OpenForm "frmUpdate", , , "visitID = " & [B][COLOR=Red]Me![/COLOR][/B]visitID

 

[wheeledgoat]

Hi. Try changing it to this:

, "visitID=" & Nz(Me.visitID,0)

Hope it helps...

Thanks for the idea! But the visitID is a key field and this will only be run on existing records, so I don't anticipate needing null handling at all.

 

[wheeledgoat]

responders are not keen to go to other sites to download stuff - particularly from new users.

you need to remove from your db all tables/forms etc which are not relevant to your question, from what's left, remove surplus records, then compact it and finally zip it - then you should be able to upload.

With regards your error, this

is saying you have not supplied an ID

Understood. I was doing my best to type a clear and concise post that would alleviate such concerns. It's not my style to spam a bunch of posts when I have nothing to offer. I subscribe to the "two ears, one mouth" approach, usually finding answers by searching.

Unfortunately, this db is on a work computer which has been scrubbed of zip capabilities. Too bad it didn't occur to me to zip it from home until now.

 

[wheeledgoat]

Where are you putting a value into visitID and where is it declared?

The visitID is the key field, passed to the form through a query in exactly the same way (I thought) as another form/query on which this works just fine.

I can't for the life of me work out why it works on one but not the other as they are designed the same.

 

[wheeledgoat]

Put Me! in the front of the code then it works.

DoCmd.OpenForm "frmUpdate", , , "visitID = " & [B][COLOR=Red]Me![/COLOR][/B]visitID

I wish I could give you more than 1 Thank. I'm a little ashamed I didn't work that out myself - I feel like I should have known that.

Just for my own understanding, though, can you tell me why the exact same line worked on the FRONT DESK form without the 'Me!'?

In any case, I'm deeply grateful and I tip my hat to you. Have a great weekend!

 

[JHB]

You're welcome.

The form frmExam seems to be a "little" corrupt, so ...

 

[wheeledgoat]

You're welcome.

The form frmExam seems to be a "little" corrupt, so ...

Please forgive my persistence but I'm not sure I know what you mean. Is it not opening for you? I'm always looking to improve - to learn the proper way of doing things and avoid bad habits. I would greatly appreciate any advice you would have the time and inclination to offer.

 

[JHB]

Please forgive my persistence but I'm not sure I know what you mean...

No problem - what I mean is the code you had in frmExam, should work but it didn't, therefore I wrote a "little" corrupt.
Too much corrupt that your code worked, but not so much corrupt that the form would not open or other errors appeared.
The proper way should have been that you included a control in the form and setting the reference, (control source) to visitID.
Then use the following code, (use a dot (.), not a !):

DoCmd.OpenForm "frmUpdate", , , "visitID = " & Me[B][COLOR=Red].[/COLOR][/B]visitID

 

[wheeledgoat]

The proper way should have been that you included a control in the form and setting the reference, (control source) to visitID.

I've always wondered that, thanks for the explanation. In my efforts to troubleshoot, creating a referenced control didn't seem to make a difference but I shall endeavor to do that in the future. (I assume it makes no difference that the control is marked not visible.)

Thanks very much for the tip!

 

[JHB]

I've always wondered that, thanks for the explanation. In my efforts to troubleshoot, creating a referenced control didn't seem to make a difference but I shall endeavor to do that in the future. (I assume it makes no difference that the control is marked not visible.)
..

Yes you've created one, you call it Text24, but you're NOT using it, so it really doesn't help.
Below the code shows how it should look like!

DoCmd.OpenForm "frmUpdate", , , "visitID = " & [B][COLOR=Red]Me.Text24[/COLOR][/B]