Form question: 1

[corrado]

I have a form and would like to have a drop down box appear that holds a list of values (names). I have additional columns in the form that were taken from a table which holds the (names) address, age, phone number, car, etc.. Upon selecting a name, I would like the remaining boxs to have the correct data for the name in which is pulled down from the list box. Basically, the name that is chosen from the box will be bound to the remaining data (age, address, etc...) Do I have to change from unbound object to bound? I cannot find help on this????? How would I do this (without coding)- since I do not know coding..??? or is that the only way???

 

[ssecca]

With out coding a bound form is the best solution. Use the combo box wizard and follow the directions.

Now if there is never a need to update or change the information in the other fields (ie. address age ect.) then an alternative would be to use this approach.

Set up the recordsource of the pull down to include all the information you want displayed. You can conseal it by set the column width properties for the various columns to 0". Then with the after_update event of the combobox use the following

me.address.value = me.MycomboNAme.Column(1)
me.City.value = me.mycomboname.column(2)

ect.

What this does is read eacj
h column of the underlying revordsource for the combobox starting from column 0 and places that value into the desired text box on the form.

Just remember with out extensive VBA coding you can not allow updating or editing of unbound forms because the change will not go back to the table.

good luck
ssecca

 

[FancyPrairie]

Another way of doing this is to set the Control Source Property of each of the text box controls to the corresponding column of the combo box.

txtAddress...ControlSource =cboName.column(1)
txtAge.......ControlSource =cboName.column(2)
and so on

Note that the combobox columns start with index 0. I'm assuming that the name is in the first column (column 0).

 

[corrado]

Thank you both for your replies. I will be trying both approaches. I will not be needing to change data right away but may need to change the data in the future. I am still gathering my requirements and will take these into consideration. Thank you very much.. DJ

 

[patcashman]

There's another way to do this which is totally dynamic, ie it updates real time with all the data:

Let's say I have a job database. There is a table called job. Another table is contact. contact has the names (contact_name), addresses, and so on for each contact.

1. Make a lookup field in job that looks for contact_name.
2. Make a form based on job, using the 'justified' option. The form will contain the pull down labeled contact_name that will enable pull down to select the contact for the job.
3. Make a query that looks for all records in contact.
4. In the criteria for contact_name, enter the following text:

=Forms![job]![contact_name]

where [contact_name] is the name of the combo box in the job form.

Uncheck the 'show' box under contact_name
5. Make a new form (also 'justified') based on the query you just made.
6. Open the job form and add a subform using the form you just created. Place and size as desired.

7. Save and close the design view of the job form. Open it in edit mode and select a contact from the contact pull down. All the associated fields from that contact's record in the contact table should appear. If you choose a different contact, the information will update.