Problems with VBA select query (1 Viewer)

[Eric the Viking]

Dear All
I am trying to use this code in a new database having used it in another to generate a list of records in an unbound list box (ListContacts) by typing the record number or surname in an unbound control (TextSurname):

Private Sub TextSurname_AfterUpdate()
If IsNumeric(Me.TextSurname) Then
ListContacts.RowSource = "Select tblContacts.ContactRegNo,tblContacts.Surname,tblContacts.Firstname,tblContacts.Address1,tblContacts.AddressPostcode," & _
"FROM tblContacts " & _
"WHERE tblContacts.ContactRegNo = " & TextSurname.Value & _
"ORDER BY tblContacts.FirstName;"

Else
ListContacts.RowSource = "Select tblContacts.ContactRegNo,tblContacts.Surname,tblContacts.Firstname,tblContacts.Address1,tblContacts.AddressPostcode," & _
"FROM tblContacts " & _
"WHERE tblContacts.Surname Like '*" & TextSurname.Value & "*' " & _
"ORDER BY tblContacts.FirstName;"

End If

End Sub

This is the error message I get:

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

Having spent a goodly while checking and double checking I cant work out why the method works in one database but not the other.....

Help would be much appreciated!

Cheers

Eric

 

[pbaldy]

Here's a common debugging method:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, the comma before FROM needs to be deleted.

 

[jdraw]

You have an extra comma before FROM.
Also watch your spacing, recommend you start or end each line with a space.


OOOps: Paul is quick!!

 

[Eric the Viking]

Many thanks I have no idea why I couldn't spot that! Exactly as I expected a fresh pair of eyes spotted the blindingly obvious! Many thanks Paul.

 

[Eric the Viking]

Thanks both your a quick on the draw lol

 

[pbaldy]

Happy to help!