Fields auto filling based on dropdown selection

[klbrownie]

I am trying to create a form where the user will select a name from a dropdown, and then other fields will populate based on the name selected. Ex: User selects "Smith, John" and then Cust#, Site and Specialty will then populate with John Smith's info. I have the initial dropdown pulling from a query which formats the name field the way I want it. (The source table has the first and last names separated.) The query also has these other fields I want, but I am having a hard time getting them into the form. I have been trying to use DLookup, but I am not having luck getting it to work. If someone came help me get it the work or suggest a better way it would be greatly appreciated!

[TheSmileyCoder]

Do you need the information to be stored in a new record, or do you just want to display it on the form for easy reference?

If I just need to display the information I usually use one-way joins in my form query to get the information.
Example:

SELECT      tbl_HazardDetails.*,
            tbl_User.tx_UserInitials AS tx_CreatedBy, 
            tbl_User_1.tx_UserInitials AS tx_ChangedBy
 
FROM        tbl_User AS tbl_User_1 
RIGHT JOIN  (tbl_User 
RIGHT JOIN  tbl_HazardDetails 
ON          tbl_User.KEY_User = tbl_HazardDetails.ID_CreatedBy) 
ON          tbl_User_1.KEY_User = tbl_HazardDetails.ID_ChangedBy;
 

This lets me display the userinitials of a person for example "tx_CreatedBy", while the actual value in the record is stored as a long in tbl_HazardDetails.ID_CreatedBy. You can use this approach to pull in multiple information, such as user's address & phone number. I can then bind the ID_CreatedBy to a combobox, and whenever I change my selection in the combobox, the fields bound to tx_CreatedBy will also update automatically.



If however you want to store the information in your new record, you need a different approach. In general this is not advised (see articles about database normalisation), but there are a few cases where it is required.

[klbrownie]

The form pulls it's data from a single query, so I don't have the multiple sources. My biggest problem has been getting the data into the form. What is the easiest/best way to do this?

[klbrownie]

I've changed my database araound a bit now to make this a little easier for me. I now have the fields in a table and I think DLookup is the way to go. (I could be wrong here... ) Anyway, I am trying to use the following statement bur I get "#Error" as a result:

=DlookUp("[Provider#]","tblProviderInfo","[Provider]=forms!frmMainForm![Provider]")

Any thoughts?

[OldBirdman]

I may have completely misread this, but it seems like this thread is about the relationship between a table (or query, which is really a temporary table) and a form.
A form is a window into a table. The purpose is to format, position, and display fields contained in a record of a table.
I am going to assume you want to view and/or edit the fields of your table. I'm also going to assume that table is tblCustomers and contains fields ID, FName, LName, Cust#, Site, and Speciality. ID is the primary key and is Autonum.
I am going to assume the form is frmCustomer, and the Record Source is tblCustomers. There would be fields such as txtFName, txtSite, and any others to display. I'll get to the combobox later. Each textbox on the form should be bound to the table. For textbox txtSite, the Name property should be txtSite, and the ControlSource property should be Site. See assumptions above. This should be done for all the textboxes.
Now you can navigate tblCustomers using the form frmCustomer. The small first, previous, next, and last buttons at the bottom-left of the form border will allow you to do that. Not necessarily in order. The record number isn't necessarily the ID field, but might be. Not as convenient as you want, but at this point it should do everything EXCEPT jump to a specific record based on a name in a combobox.
If your project now works as you need it to, you are ready to add the combobox, which I'm assuming is named cbxSelectName. cbxSelectName will have 2 columns, the ID and the composite name. Set the ColumnWidths property to 1";2" for now, although you will change this to 0";2" later to hide the ID column. ColumnCount will be 2, and BoundColumn will be 1. RowSource will be changed to include the ID field, and should look something like

SELECT ID, [LName] & ", " & [FName] AS SelName FROM tblCustomers ORDER BY LName, FName

And finally, the OnClick event will need some VBA code to show the chosen record. A simple way is

Private Sub cboSelectName_Click()
  Me.Filter = "ID=" & cboSelectName
End Sub

This will render the Access-supplied navigation buttons worthless. If you need them, we can slightly modify the above.