Combo Box

[Guest]

If I make a selection from a combo box on a form I want other fields to be
automatically completed with the related information. How can I do that
without making other columns on the combo box (so that the info can be stored
in the control table)?

 

[Guest]

What is your objection to having multiple columns in the combo box? That is
the usual way to do it?
What do you mean by Control Table?

 

[Guest]

There are 30 fields that need to be completed and I would like that
corresponding information to be stored in a table (something that I've
realized is difficult if you use the columns technique). I want the
information to be stored in that table that is the control source, that is
what I meant by the control table. Any ideas?

 

[Guest]

I still don't quite get your reference to control source. In Access
terminology, a control source is the property of a control that bind a field
in the form's record source to the control.

I'm I to understand that what you are calling the control table is not the
record source of the form, but the table that is used for the row source of
the combo?

If this is the case and you don't want to use columns, there are a few
different ways to do it.

One would be to create a recordset from the control table filtered on the
value of the combo that would return the fields you want. Then you could use
VBA to populate the controls on your form.

Another would be to create an Update or Append query depending on whether
the new record had been created. It would use the same table and filtering
as the previous method. Then you would have to do the following steps:
Run the Action Query.
Save the primary key value of the current record for the form.
Requery the form.
Use the saved primary key to reposition your form's recordset to make it the
current record.

Another possibility would be to make the DeFault Value properties of all the
controls you want to populate a DLookup based on the filtered Control table.
It this will work correctly, it may be the easiest. Each would be something
like:

=DLookup("[SomeField]", "TheControlTable", "[KeyField] = '" & MyCombo & "'")

I suggest trying the DLookup method first. Let me know how it works out.