Combine multiple queries using same fields and table

Question

I have a Form for order information. It will populate the fields order date, product info, Customer ID... after selecting the Order number from a combo box on the form(Source for all this information is OrderInfo table). I have a separate table for CustomerInfo which has Customer ID, name, address... There are four Customer ID fields on the form. I want to automatically display the name, address... based on Customer ID field.

I did a query for the first customer to look for the customer ID value on the form and find the name & address. But I do not want to write four queries for the four customer ID fields. Is there any way to combine all four into one or is there a way better to do this process?

Answer 1

As June7 commented, eight lines of DLookup code might be the quickest solution.

I don't personally like to use DLookup, and I'm sure their is a more elegant solution than what I'm suggesting, but below is what I tried (and I assumed unbound textboxes).

Create a private subroutine on the form:

Private Sub PopulateNameAddress(CustomerID As Long, CustomerNameTextBox As TextBox, CustomerAddressTextBox As TextBox)
    
'CustomerNameTextBox is a variable for the names of the text boxes containing the customer's name
'CustomerAddressTextBox is a variable for the names of the text boxes containing the customer's address
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT CustomerName, CustomerAddress FROM CustomerInfo WHERE CustomerID=" & CustomerID)
    
        CustomerNameTextBox = rs!CustomerName
        CustomerAddressTextBox = rs!CustomerAddress
    
    rs.Close
    db.Close
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub

Then after whatever populates the CustomerID (as shown in the image you provided), call this subroutine, and give it the CustomerID, the name of textbox for the customer's name, and the name of the textbox for the customer's address. Could be better running a loop, but since you only have four records to fill, this might be the most straight forward:

    'Send the PopulateNameAddress subroutine the name of each text box containing the CustomerID, CustomerName, and CustomerAddress
    PopulateNameAddress Customer1IDTextBox, Customer1NameTextBox, Customer1AddressTextBox
    PopulateNameAddress Customer2IDTextBox, Customer2NameTextBox, Customer2AddressTextBox
    PopulateNameAddress Customer3IDTextBox, Customer3NameTextBox, Customer3AddressTextBox
    PopulateNameAddress Customer4IDTextBox, Customer4NameTextBox, Customer4AddressTextBox

Unfortunately, I'm not sure how you have your database setup, so this may not necessarily work. Let me know.