Query question involving AutoNumber data type

[MichaelJohnson168]

The primary key in the customers table is an AutoNumber field type. I
have a form which is to perform a search from a textbox named
CustomerSearch and is to be compared to the CustomerID in the table
until it finds the CustomerID or reaches the EOF.

The problem is that the textbox is sending the number as a string type
and the field is an Autonumber. Therefore I am getting the following
error message:

Syntax error (missing operator) in query expression '* Customers.
[CustomerID]'


Set dbsRDP = CurrentDb
strSQL = "SELECT * Customers.[CustomerID], Customers.[CustomerFName],
Customers.[CustomerMI], Customers.[CustomerLName],Customers.
[CustomerAddress], Customers.[CustomerCity], Customers.
[CustomerState], Customers.[CustomerZip], Customers.[CustomerPhone],
Customers.[CustomerEmail] " & _
"FROM Customers " & _
"WHERE Customers.[CustomerID]='" & Me.txtCustomerSearch.Value & " ';"

Set rstCustomers = dbsRDP.OpenRecordset(strSQL, dbOpenDynaset) <----
This is where the error message occurs


Can someone let me know how I can perform this search properly?

Thanks in advance.

 

[akphidelt]

You are creating a string type for the ID by doing '" &
Me.txtCustomerSearch.Value

Try doing

=" & Me.txtCustomerSearch.Value & ";"

A better way would be to tell vba it's an integer

Dim MyID as Integer

MyID = Me.txtCustomerSearch

Then the formula would be =" & MyID & ";"