Auto-populate fields based on changing one.

[Will]

This has probably been ascked and answered before but my searches have
turned up blank so here goes...

I have an existing database system that requires 3 fields updated in
one table (hardware) from another(clients) when creating a new record
(ClientName,ClientID,ClientLoc) I have a Combobox so the user can
select the ClientName but I am having trouble figuring out how to
update the other 2 fields to match.

I have tried adding the other 2 colums to the ClientName filed and
using a SetValue macro to assign ClientName(2) and ClientName(3) to
ClientID and ClientLoc. I can see the other columns in the ClientName
list but I seem to be unable to assign them to the other field.

Thanks in advance for any help,
Will

 

[Al Campagna]

Will,
Actually, you should be capturing the ClientID in your combobox... not
the name. Names are not unique enough to always be sure of no dupes.
Also, there's no need to capture the ClientName or ClientLoc. Once
you've cpatured the ClientID, those associated values can be derived (and
"displayed")on your form.

Here's a better method...
Create a 3 column combo with ClientID, Client Name, and ClientLoc. (ex.
cboClientID)
Bind that combo to the ClientID field.
Setup the combo with...
NoOfCols = 3
ColWidths = 0"; 2"; 2"
Width = 4"
What this combo setup will do is... display the ClientName and ClientLoc
so that the user can easily select the correct Client. After a selection is
made, the combo will still show the ClientName...BUT... it is actually
storing the ClientID in the ClientID field of your table.
Now, just add an unbound calculated text control to your form (ex.
ClientLoc) with a ControlSource of...
= cboClientID.Column(2)
(combo colums are numbered 0, 1, 2, 3...)

So... we use the cboClientID ControlSource to capture the ClientID, and
also use the combo to "display" the ClientName, and an unbound calculated
text control to "display" the Client Location on the form.

Remember... if you have the ClientID, you can always re-derive any
associated client data in any subsequent Form, Query, or Report... via that
ClientID.

--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."