Relating fields in a form

[cwebb]

I am trying to use a combo box in a form with a pull-down menu (i.e. Business Name). I need the remaining fields (i.e. Business Address, City, State, Zip, etc.) to update when the business in the pull-down menu changes. How can this be done? I have been through 3 books and can not find a solution.

 

[DougP]

Add this to the combo boes &quot;After Update&quot; Event procedure.<br>
<br>
Private Sub Combo8_AfterUpdate()<br>
Dim db As Database<br>
Dim rst As Recordset<br>
Set db = CurrentDb<br>
SQL = &quot;Select * From Business Where BusID = &quot; & Combo8.Column(0)<br>
Set rst = db.OpenRecordset(SQL)<br>
Me!BusName = rst.Fields(&quot;BusName&quot<br>
Me!BudAddr = rst.Fields(&quot;BudAddr&quot<br>
Me!BusCity = rst.Fields(&quot;BusCity&quot<br>
<br>
End Sub<br>
<br>
<br>
Now in your combo box put both the Business's unique ID and their name<br>
Put the ID in the first column and give it a 0&quot; width so it will not show.<br>
When you click on the Business name it will pass the ID to the SQL code and find the exact match.<br>
<br>
<br>
OK<br>
<br>
<br>
<br>

 

[elizabeth]

If it's for display only, here's another method I once used, saved a lot of space on the form: <br>
<br>
Build a query for combo's RowSource that contains a concatenated string with all the data. In column 1 of the query put your key, in column 2 put something like [BusinessName] & &quot; &quot; & [Address] & &quot; &quot; & [City] etc. Set your Bound column to 1, your Column Count to 2, and Column Widths to 0;x (x=width in inches needed to display all that stuff), set the width of the combo box to the same number.