By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,520 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,520 IT Pros & Developers. It's quick & easy.

syntax error !

P: 12
Expand|Select|Wrap|Line Numbers
  1. Dim rstremoveAs Recordset
  2. Set rstremove = Database.OpenRecordset("SELECT * FROM tblitemhire WHERE itemhireno = " & ListBox & ";")
I am getting a syntax error (missing operator) in query expression 'itemhireno = '
Would anyone know what this problem could be, ive been through it over and over again but the same problem remains

Thanks
Feb 26 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,701
Dim rstremoveAs Recordset
Set rstremove = Database.OpenRecordset("SELECT * FROM tblitemhire WHERE itemhireno = " & ListBox & ";")

I am getting a syntax error (missing operator) in query expression 'itemhireno = '
Would anyone know what this problem could be, ive been through it over and over again but the same problem remains

Thanks
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, rstremove As DAO.Recordset
  2.  
  3. Set MyDB = CurrentDb
  4.  
  5. 'If itemhireno is NUMERIC
  6. Set rstremove = MyDB.OpenRecordset("SELECT * FROM tblitemhire WHERE [itemhireno] = " & Me![<List Box Name>], dbOpenDynaset)
  7.  
  8. 'If itemhireno is TEXT
  9. Set rstremove = MyDB.OpenRecordset("SELECT * FROM tblitemhire WHERE [itemhireno] = '" & Me![<List Box Name>] & "'", dbOpenDynaset)
  10.  
  11. Do While Not rstremove.EOF
  12.   'processing of the Recordset goes here
  13.   rstremove.MoveNext
  14. Loop
  15.  
  16. rstremove.Close: Set rstremove = Nothing
Feb 26 '08 #2

NeoPa
Expert Mod 15k+
P: 31,768
There's no space between rstremove and As on line 1.
Feb 28 '08 #3

Scott Price
Expert 100+
P: 1,384
In addition, and to clarify: You need to fully qualify where the ListBox exists. In other words, looking closely at ADezii's code you'll see Me!<list box name>. This needs to reflect if the list box is on the form where you are running this code from, or if it is on another form.

Me!... assumes the same form,

Forms!<form name>!<list box name>... assumes a separate form.

Regards,
Scott
Feb 28 '08 #4

NeoPa
Expert Mod 15k+
P: 31,768
ListBox is equivalent programatically to Me.ListBox (or even Me!ListBox) though. Many prefer to have the code be more explicit though, of course.
Feb 28 '08 #5

Scott Price
Expert 100+
P: 1,384
You're correct, NeoPa the two are equivalent, however for good programming practices...

The problem with unqualified controls is the possibility of, say, a variable with the same name existing in the same sub procedure or form module. The unqualified code will point to the variable instead of the control, which can lead to undesirable and unpredictable results!

As well, if the list box is on another form entirely, the code will not be able to find the specified list box, leading to a syntax error.

Regards,
Scott
Feb 28 '08 #6

NeoPa
Expert Mod 15k+
P: 31,768
You're correct, NeoPa the two are equivalent, however for good programming practices...
Agreed.
The problem with unqualified controls is the possibility of, say, a variable with the same name existing in the same sub procedure or form module. The unqualified code will point to the variable instead of the control, which can lead to undesirable and unpredictable results!
Agreed.
As well, if the list box is on another form entirely, the code will not be able to find the specified list box, leading to a syntax error.
This doesn't pertain.
Remember we are comparing[ListBox] with Me.[ListBox]. Clearly neither would be correct syntax for that eventuality.
Feb 28 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.