enable lookup combo box to query a key field of table and populate

[Chu888]

I create a combo box in a Form which query a table and get the queried result
key value but I don't know how I can populate the whole record based on that
key value on this Form.

e.g. When I select a surname in combo box in a form, the whole record in a
table is not populated on the text boxes which I create in the Form.

I don't why these text boxes do not update with the values in the table as
soon as I choose the selected value in combo box. Any idea. how can this be
achieved?

 

[Rick Brandt]

I create a combo box in a Form which query a table and get the
queried result key value but I don't know how I can populate the
whole record based on that key value on this Form.

e.g. When I select a surname in combo box in a form, the whole record
in a table is not populated on the text boxes which I create in the
Form.

I don't why these text boxes do not update with the values in the
table as soon as I choose the selected value in combo box. Any idea.
how can this be achieved?

By default a ComboBox is merely a tool to enter a value in a field, just
like a TextBox is. Would you expect other controls on a form to be
populated just because you entered a value in a TextBox? If not, then why
do you expect that behavior of a ComboBox?

Now, an *unbound* ComboBox along with some VBA code can be used to navigate
a form amongst its RecordSource positions. As you change from one record to
another you would see the value in the other controls change, but that is
due to the fact that you are now looking at a different record containing
different values, not because the ComboBox caused those controls to be
"populated" with values. Is this the behavior you are seeking? If so
delete the current ComboBox and add another with the toolbox wizard enabled
and the wizard will provide an option to create such a ComboBox for you.

A ComboBox could also use VBA code to grab values from its unbound columns
and push those values into other controls on the form. However; 90% of the
time this would be a bad idea and would indicate a flawed database design.
If this is the behavior you are looking for please describe your situation
so we can determine if you're in the 10% of situations where it is warranted
or whether you are taking the wrong path.

Finally, TextBoxes on the form could have ControlSource expressions that
grab the values from the unbound columns in the ComboBox *for display
purposes only* rather than as a means to copy them into the current form's
record. Is this the behavior you seek? If so you need ControlSource
expressions like...

=ComboBoxName.Column(1)
=ComboBoxName.Column(2)
etc...

 

[Chu888]

My goal is to set up a Access Form.

In this Form, I set up a combo box and text box controls.

With the unbound Combo Box control, it queries a table and list out all
names of all kinds of toys, being one field value of that table (unique key).
Then with this listing of names, I can choose any one of them.

ASA I choose a name from the Combo box, the text box controls which link up
with fields of that table in relating to the record that has the value of
this name will display (populate) all field values of that record in this
Form.

I failed to populate them in the Form. How can this be done?