Binding text boxes to fields in a query

[Serious_Practitioner]

Hello, all -

I want to get a customer name, address and so forth from a customer table
using either a SELECT statement in a VBA procedure or in a query. The user
should be able to put a customer number in a text box and I'd like the
information to pop up in text boxes on a form.

Is it possible to bind text boxes to, say, the fields containing the
CustName and CustAddress in a query? I want to use text boxes for
appearance; I don't care for the look of combo boxes or subforms.

I've fiddled with this far too long, without success. Any suggestions?

Thanks in advance.
Steve E.

[Bruce M. Thompson]

> I want to get a customer name, address and so forth from a customer table

using either a SELECT statement in a VBA procedure or in a query. The user
should be able to put a customer number in a text box and I'd like the
information to pop up in text boxes on a form.

Is it possible to bind text boxes to, say, the fields containing the
CustName and CustAddress in a query? I want to use text boxes for
appearance; I don't care for the look of combo boxes or subforms.

I've fiddled with this far too long, without success. Any suggestions?

Sure. Just set the form's RecordSource property to a query that returns all of
the relevant fields (including the customer number, of course) and then place
textboxes bound to those fields that you want to display on the form. Then place
an unbound textbox/combobox on the form (named "txtFind" in the following
example) and insert code similar to the following in the unbound
textbox/combobox's "AfterUpdat e" event procedure:

'***Access 2000, or later
With Me.Recordset
'If the customer number field is a number
.FindFirst "CustomerNumber =" & Me.txtFind

'If the customer number field is text
'.FindFirst "CustomerNumber =""" & Me.txtFind & """"
End With
'***

'***Any version of Access
Dim rst As Object
Set rst = Me.RecordsetClo ne
'The following line assumes the customer number field is text
rst.FindFirst "CustomerNumber =""" & Me.txtFind & """"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "The specified customer was not found.", _
vbOKOnly + vbInformation
End If
'***

Although a text box will work, it might be more productive using a combo box to
help ensure entry of an existing value.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.o rg (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<