syntax error !

[compwizard]

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

[ADezii]

Dim rstremoveAs Recordset
Set rstremove = Database.OpenRe cordset("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

[CODE=vb]
Dim MyDB As DAO.Database, rstremove As DAO.Recordset

Set MyDB = CurrentDb

'If itemhireno is NUMERIC
Set rstremove = MyDB.OpenRecord set("SELECT * FROM tblitemhire WHERE [itemhireno] = " & Me![<List Box Name>], dbOpenDynaset)

'If itemhireno is TEXT
Set rstremove = MyDB.OpenRecord set("SELECT * FROM tblitemhire WHERE [itemhireno] = '" & Me![<List Box Name>] & "'", dbOpenDynaset)

Do While Not rstremove.EOF
'processing of the Recordset goes here
rstremove.MoveN ext
Loop

rstremove.Close : Set rstremove = Nothing[/CODE]

[NeoPa]

There's no space between rstremove and As on line 1.

[Scott Price]

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

[NeoPa]

ListBox is equivalent programatically to Me.ListBox (or even Me!ListBox) though. Many prefer to have the code be more explicit though, of course.

[Scott Price]

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

[NeoPa]

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.