Problems with VBA select query

Eric the Viking

Registered User.
Local time
Today, 08:42
Joined
Sep 20, 2012
Messages
70
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
 
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!!
 
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.
 

Users who are viewing this thread

Back
Top Bottom