Automatically Fill a Text or List Box Based on a Combo Box Selecti

[Guest]

I have a client management database. Each client has a name and an ID
number. On my forms, there is a combo box with a drop down list of all
clients. When a user selects a client, I need that client's ID number to
automatically "come up" in the client ID number field on the form. Currently
that ID number field is is a text field, though I've tried accomplishing this
by making it either a list or a combo box connected to a query. So far I
haven't been successful.

I'm using MS Access 2003 on Windows XP.

Thanks for any ideas!

Matt

 

[Al Camp]

Matt,
Use your own object names...
Create a 2 column combo. First column = CustID Second = CustName
No of Columns = 2
ColWidths = 0"; 1.5" '(hide the first column)
ListWidth = 1.5

Bind the CustID field to the ControlSource of the Combo.

What this combo does is display the customer names for easy user selection.
Once a customer name has been selected , the combo will "display" that name in the
combo.
BUT what's really stored in the bound CustID combo field is the CustID from the 1st
"hidden" column.
The form may show "Smith", but the table has stored the CustID 5241 in that CustID
field.

 

[Guest]

That's awesome. However, I also need to save CustName to the table to which
that form is saving data. I've tried doing this with a macro which has the
action SetValue, setting the value of CustName in the table to the combo box,
but that doesn't work. Any thoughts? Thanks!

 

[Al Camp]

Matt,
Since you've captured the CustID, there's no need to "capture" the CustName. The
CustName can always be re-derived from the Customer table in any future form, query, or
report. Don't carry information in fields that can be re-derived again at any time "on
the fly."

If you "must" save CustName, add a CustName field to the table you're updating with
this form, bind it to a text control, and on the AfterUpdate event of the CustID combo....
(we'll call that cboCustID)

CustName = cboCustID.Column(1)

(columns in combos are numbered 0, 1, 2, 3, etc..)
CustID is in 0, CustName is in 1.