> I want to get a customer name, address and so forth from a customer table[color=blue]
> 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?[/color]
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 "AfterUpdate" 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.RecordsetClone
'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
bthmpson@mvps.org (See the Access FAQ at
http://www.mvps.org/access)[color=blue][color=green]
>> NO Email Please. Keep all communications[/color][/color]
within the newsgroups so that all might benefit.<<
Binding text boxes to fields in a query 
