Synchronizing other Form fields with a Combo Box?

[jrtaylor]

Synchronizing fields on a form.

I have a form where comments will be entered. I have a ComboBox on the form that looks up values in a table named “tblName”. Also in the “tblName” are other values I want looked up based on the name that is selected in the ComboBox. These values include Group, Area, and Question to be answered. These fields are in the table “tblName” and will not be changed.

How can I have the person’s Group, Area, and Question appear when the name is selected? This is driving me crazy because it is probably very simple.

Please, I need help as soon as possible.
Thanks!
jrtaylor

 

[WildHare]

Use DLOOKUP to grab other fields on the record being chosen by the combo box. (This assumes your combo is called Combo1, contains the key field from tblName, and it's called "MyKey"

xGroup = Dlookup("Group", "[tblName]", "[TblName]![MyKey] = '" & me.Combo1 & "'&quot

The extra single quotes are needed if MyKey is a text field. Otherwise just use

xGroup = Dlookup("Group", "tblName", "[TblName]![MyKey] = " & me.Combo1 )

Similar syntax for the other fields.

The other way to do this is to INCLUDE your other fields in the query that populates the combo box. You can set their columns to Zero width in the combo properties window, but refer to them after the combo box is picked from thusly:

MyGroup = Combo1.Column(1)
MyDept = Combo1.Column(2)
etc

or whatever, keeping in mind that combo box columns are zero-based in their enumeration. Depending on the amount of data being pulled back for your combo, one or the other of these methods may prove to be more efficient.




Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[jrtaylor]

Thanks! I am on my way now!