Reading textbox contents using VB with Microsoft Access

[jnhutchi]

It seems that what I'm trying to do should be pretty simple, but I can't get it to work. I've created a form with a textbox and a button in Microsoft Access. When you press the button, I want to search my Access database table (called Customer) for the Customer ID number that I entered in the textbox (which I named txtCustomerID). In order to search the Customer table, I created a Recordset. The code is below, which is executed when the button is clicked. The line that keeps giving me an error is:

.Find "[Customer_ID] = txtCustomerID.Text"

If I enter a customer ID (ex: 444444), instead of txtCustomerID.Text, it works fine:

.Find "[Customer_ID] = '444444'"

I just can't get it to work when I try to use a textbox variable instead of a string, which is what I need to do. I would greatly appreciate any help that anybody can offer. Thank you.

Private Sub CheckCustomerRecord_Click()

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open "SELECT * FROM Customer", _
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

With rst
.Find [Customer_ID] = txtCustomerID.Text
End With

rst.Close
Set rst = Nothing

End Sub

 

[AndyWatt]

Replace

  .Find "[Customer_ID] = txtCustomerID.Text"

With

  .Find "[Customer_ID] = '" & txtCustomerID.Text & "'"

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"Why does my program keep showing error messages every time something goes wrong?"