Reading textbox variable using VBasic 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

 

[mattKnight]

jnhutchi

First, welcome to Tek-tips!

I suspect that forum222 may be a little more appropriate for this sort of question.... There is also an excellent FAQ on asking the best questions (to get the best answers) FAQ222-2244. May I suggest that you read it?

Finally, to your question...

try replacing

.Find "[Customer_ID] = txtCustomerID.Text"

with

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

What I would do, personally, is this

dim strFind as string

strFind = "[Customer_ID] = '" & txtCustomerID.Text &"'"
debug.print strFind 'just to check strFind is what we expect
.Find strFind

Hope that helps... If it doesn't, Why not repost the question if the VB forum! (if you do that, please add a link from this thread so that people can see!)




Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[jnhutchi]

I appreciate your help, but your suggestion didn't seem to work. I went ahead and posted the thread in Forum222 like you suggested. thanks