Use DLookup to autofill values in fields, based on value chosen by Select function

[JoshPerkins]

Hi!


I'm new around these parts so, be nice please!
I have a database I'm currently designing for a client, and I'd like to be able for them to be able to choose a customer's account number and for the form to then be automatically updated with the customer's details.
I have constructed a DLookup function:

Private Sub CustomerAccountNumber_AfterUpdate()
Title = DLookup("Title", "Customers", "CustomerAccountNumber")
First_Name = DLookup("First_Name", "Customers", "CustomerAccountNumber")
Surname = DLookup("Surname", "Customers", "CustomerAccountNumber")
Email_Address = DLookup("Email_Address", "Customers", "CustomerAccountNumber")
Telephone = DLookup("Telephone", "Customers", "CustomerAccountNumber")
Mobile = DLookup("Mobile", "Customers", "CustomerAccountNumber")
Address_Line_1 = DLookup("Address_Line_1", "Customers", "CustomerAccountNumber")
Address_Line_2 = DLookup("Address_Line_2", "Customers", "CustomerAccountNumber")
Address_Line_3 = DLookup("Address_Line_3", "Customers", "CustomerAccountNumber")
Town = DLookup("Town", "Customers", "CustomerAccountNumber")
County = DLookup("County", "Customers", "CustomerAccountNumber")
Postcode = DLookup("Postcode", "Customers", "CustomerAccountNumber")
Country = DLookup("Country", "Customers", "CustomerAccountNumber")
End Sub

But whenever I try to change the customer account number in my drop-down (Select) field, none of the information is updated. Also, when I create a new record in the table, and select a different account number, the form will only display the information held against the customer in the first row of the table...?
Any help would be appreciated!
Thanks,
Josh

[ssanfu]

Welcome to the forum

Search for "DLookup" in Help.

You are missing the [CustomerAccountNumber] in the DLookup function.

Private Sub CustomerAccountNumber_AfterUpdate()
    Me.Title = DLookup("[Title]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.First_Name = DLookup("[First_Name]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Surname = DLookup("[Surname]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Email_Address = DLookup("[Email_Address]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Telephone = DLookup("[Telephone]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Mobile = DLookup("[Mobile]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Address_Line_1 = DLookup("[Address_Line_1]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Address_Line_2 = DLookup("[Address_Line_2]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Address_Line_3 = DLookup("[Address_Line_3]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Town = DLookup("[Town]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.County = DLookup("[County]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Postcode = DLookup("[Postcode]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Country = DLookup("[Country]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
End Sub

Using the DLookup function like this is relatively slow. I would use a record set.
Actually, I would have the data in the combo box and fill in the controls using the Columns() option of the combo box.

[Missinglinq]

...Using the DLookup function like this is relatively slow...

...Actually, I would have the data in the combo box and fill in the controls using the Columns() option of the combo box...

Steve is right on the money here! And with the CustomerAccountNumber as the first Visible Column, you don't even need to scroll through the dropdown list; with each character entered, the AutoExpand Property will take the user closer and closer to the target account number until it is found.

Set up your Combobox using the Wizard and include the Fields you need, from Left-to-Right.

If in the Combobox they appear as

Field1 | Field2 | Field3

the Column code Steve mentioned would be something along these lines:

Private Sub YourComboBox_AfterUpdate()
   Me.txtField1 =  Me.YourComboBox.Column(0)
   Me.txtField2 = Me.YourComboBox.Column(1)
   Me.txtField3= Me.YourComboBox.Column(2)
End Sub

Notice that the column index is Zero-based.

Linq ;0)>

[JoshPerkins]

Private Sub CustomerAccountNumber_AfterUpdate()
    Me.Title = DLookup("[Title]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.First_Name = DLookup("[First_Name]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Surname = DLookup("[Surname]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Email_Address = DLookup("[Email_Address]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Telephone = DLookup("[Telephone]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Mobile = DLookup("[Mobile]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Address_Line_1 = DLookup("[Address_Line_1]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Address_Line_2 = DLookup("[Address_Line_2]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Address_Line_3 = DLookup("[Address_Line_3]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Town = DLookup("[Town]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.County = DLookup("[County]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Postcode = DLookup("[Postcode]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
    Me.Country = DLookup("[Country]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
End Sub

This isn't working either
Is there something I'm doing wrong?

[ssanfu]

This isn't working either

What does that mean? Any error message?


Just to make sure (since I can't see your dB):
You have a table named "Customers"?
You have a field named "Title"?
You have a field named "CustomerAccountNumber"?
You have a control on a form named "CustomerAccountNumber"? (I would have taken the time to rename the control.... it is less confusing. Maybe "txtCustAcctNum")
What is the data type for the field "CustomerAccountNumber"? Text or number?

[JoshPerkins]

What does that mean? Any error message?

Sorry, the fields don't update, and they're still being filled in with whatever data is on the first row of the table.

What is the data type for the field "CustomerAccountNumber"? Text or number?

It's a number field

Thanks for your help!

[June7]

Sticking my 2 cents in.

Should not need code to populate these fields.

What is purpose of this form? To edit/enter customer record? Or to edit/enter some other record (customer order) and select customer from a combobox?

A combobox used as input for filter criteria must be unbound, otherwise you just change the data in record.

If you want to enter a new record, make sure you are on a new record row, not an existing record.

[JoshPerkins]

The form is to create a customer order invoice, with the combobox to select the customer, whose details I'd like populated into the other fields.
Thanks!

[June7]

Should probably not be saving those data into the invoice record, just save the customer ID.

Options that do not involve VBA for just displaying the related info:

1. customer combobox is multi-column, each column contains customer data piece, textboxes refer to the columns

2. include customer table in the form RecordSource, join type 'show all records from Orders ...", bind textboxes to fields from customer table and set them as Locked Yes to prevent edit

In both cases set the customer info boxes as TabStop No.

[ssanfu]

In my post #2, my last line was to use the combo box to get the data.
In post #3, Linq also suggested the combo box route and provided some examples
June7 also says the combo box method.
I'm just saying...



But I am curious as to why the DLookup is not changing the data.
Change your after update code temporally to

Private Sub CustomerAccountNumber_AfterUpdate()
   ' ----------------temp lines---- delete later
   Dim strString As String
   strString = "Combobox Cust Number = " & Me.CustomerAccountNumber & vbNewLine
   strString = strString & "First_Name = " & DLookup("[First_Name]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   strString = strString & "Surname = " & DLookup("[Surname]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   MsgBox strString
   Exit Sub
   ' ----------------temp lines---- delete later

   Me.Title = DLookup("[Title]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.First_Name = DLookup("[First_Name]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Surname = DLookup("[Surname]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Email_Address = DLookup("[Email_Address]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Telephone = DLookup("[Telephone]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Mobile = DLookup("[Mobile]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Address_Line_1 = DLookup("[Address_Line_1]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Address_Line_2 = DLookup("[Address_Line_2]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Address_Line_3 = DLookup("[Address_Line_3]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Town = DLookup("[Town]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.County = DLookup("[County]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Postcode = DLookup("[Postcode]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Country = DLookup("[Country]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
End Sub

Select a customer... remember the message box. Then select a different customer.
What were the customer numbers?
Did the names change?

[JoshPerkins]

In my post #2, my last line was to use the combo box to get the data.
In post #3, Linq also suggested the combo box route and provided some examples
June7 also says the combo box method.
I'm just saying...

Haha...I have been trying the combobox method, I'm just exploring my options

Private Sub CustomerAccountNumber_AfterUpdate()
   ' ----------------temp lines---- delete later
   Dim strString As String
   strString = "Combobox Cust Number = " & Me.CustomerAccountNumber & vbNewLine
   strString = strString & "First_Name = " & DLookup("[First_Name]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   strString = strString & "Surname = " & DLookup("[Surname]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   MsgBox strString
   Exit Sub
   ' ----------------temp lines---- delete later

   Me.Title = DLookup("[Title]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.First_Name = DLookup("[First_Name]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Surname = DLookup("[Surname]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Email_Address = DLookup("[Email_Address]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Telephone = DLookup("[Telephone]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Mobile = DLookup("[Mobile]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Address_Line_1 = DLookup("[Address_Line_1]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Address_Line_2 = DLookup("[Address_Line_2]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Address_Line_3 = DLookup("[Address_Line_3]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Town = DLookup("[Town]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.County = DLookup("[County]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Postcode = DLookup("[Postcode]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
   Me.Country = DLookup("[Country]", "Customers", "[CustomerAccountNumber] = " & Me.CustomerAccountNumber)
End Sub

Select a customer... remember the message box. Then select a different customer.
What were the customer numbers?
Did the names change?

The message box reads
ComboBox Cust Number = 1
First_Name = JoshSurname = Perkins

and then when I change it:
ComboBox Cust Number = 2
First_Name = JoshSurname = Perkins

Strange?
and no, none of the data changes

Thanks for your help btw!

[JoshPerkins]

Okay, so I finally got the ComboBox columns option working, and then I went on so it would control the rest of the fields I wanted it to, and now it's not working!

I added a new combobox (unbound) with all the customer's details as a "chooser" sort of field.
I want these details then to be filled in the other text fields, but whenever I change the record, it boots up the debugger?

[June7]

This unbound combobox is to input search criteria? An unbound control without a dynamic expression will show the same value for all records displayed. This unbound control should be in form header section.

Our suggestion involves use of a bound combobox to select item for entry into record. The textboxes would reference the bound combobox to display the info related to that selected item.