Automatically fill a group of text boxes based on another text box

[Sara]

Hello,

I need help with a data entry form that should bound to a table called
"Inquiries", I have 4 text boxes on the form, one is a text box where the
user enters the customer ID, then after the user enters the the customer ID
the other 3 boxes automatically should populate with the name, phone number,
and email, this information comes from a "customer" table. What is the best
way to accomplish this?
I appreciate your help

 

[Klatuu]

This is how I would do this. I would add an unbound combo box to my form
with it's row source being the customer table. I would have a column for
each of the 4 fields in the row source, but make all except the customer ID
invisible. Then in the After Update event of the combo, I would assign the
value of each column to the corresponding text box on the form.

Also, I would make the text boxes involved Not enabled and Locked so
incorrect values can't be entered.

This does a couple of things for you. It validates the customer ID you
entered, and make loading the text boxes easier. You can also use the
combo's Not In List event to warn the user of an invalid entry and allow them
to create a new customer record, if necessary.

If you need more detail, please post back

 

[Sara]

THANKS, I thought about that but it has to be a text box, there are about
4000 customer ID's and they all start with the same character, so a combo box
would not be helpful as if the user types the 4 character ID. Please HELP!!!

 

[Sara]

Thanks, I thought about that but it has to be a text box, there are about
4000 customer ID's and they all start with the same character, so a combo box
would not be helpful as if the user types the 4 character ID. Please HELP!!!

 

[Klatuu]

I disagree with you about whether it should be a combo; however, since you
insist, here is an example of how you would use a text box to look up an
existing customer id. This would be the Before Update event of the text box:

Private Sub txtCustId_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset

Set rst = Me.RecordsetClone
With rst
.FindFirst "[CUST_ID] = '" & Me.txtCustId & "'"
If .NoMatch Then
If MsgBox("Customer " & Me.txtCustID & " not found" & vbNewLine
& _
"Add This Customer", vbQuestion + vbYesNo") = vbNo Then
Cancel = True
Me.Undo
End If
Else
Me.BookMark = .BookMark
End If
End With
End Sub