I have an Access Database, one of the forms has a text box that is read only (text58)this contains a customer number. I have another text box (text41), I need to display the answer to an SQL statement in text41.

This SQL statement uses the customer number in text58 to pull out the corresponding customer name I just need help the SQL statement that is going to sit in VB code behind text box text41, i think its something along the lines of

Private Sub Text41_AfterUpdate(Cancel As Integer)

Text41.text = “SELECT [customer name] FROM tblcustomers WHERE [customer number] LIKE '" + Text58.TEXT + "'”

End Sub

But I cant get it to work

Any help would be most grateful

Probably needs to be:

Text41.text = “SELECT [customer name] FROM tblcustomers WHERE [customer number] LIKE '%" & Text58.TEXT & "%'"

VB uses ampersands to concatenate strings.

The LIKE statement requests you to use the search macro % (it might be different for Access, but % is TSQL and it should work)

Hope this helps,
Chi

They mostly come at night...mostly.

I would use the dlookup function for this.

me.Text41 = dlookup("Customer Name","tblcustomers","Customer Number = " & me.Text58

I would also put this in the Form's On Current Event.