Automatic Population of Cells Based on Drop-Down Selection

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

I am working in a client database (CLIENT). In CLIENT, most of the clients
have a CPA whose information needs to be kept with their particular entry
for mail merges and other purposes. Currently, I just populate those fields
manually for all entries on an as needed basis. However, I realize that
there are only going to be about 100 CPA names total, just entered several
times throughout the table. Additionally, very soon the entire office will
be able to add entries. Far too often, people will supply incorrect or
incomplete information. To ensure the integrity of the data, to ensure that
all available information for each CPA is under each client entry (i.e.,
people don't leave the phone number off if we have it), expedite the
process, and remove the chance of typos, I want to automate the CPA entry as
much as possible. What I would like to do is have people choose the name of
the CPA from a drop-down list (a combo box requiring they choose from the
list) and have all of the relevant fields that apply to that particular CPA
populated with the proper information.



I know how to make a combo box requiring them to pick a CPA name, but I don'
t know how to populate the rest of the information for that CPA based on
that selection.



I have already created a CPA TABLE that contains "CPA Name," "CPA Corporate
Name," "CPA Address Line," "CPA City," "CPA State," "CPA Zip," "CPA Phone,"
and "CPA E-mail" columns. How do I get the information from the cells in
CPA TABLE into CLIENT based on the CPA selection in CLIENT? Ideally,
whatever the method, I would be able to change the information for a given
CPA in CPA TABLE (i.e, a new address) and have the change be reflected
anywhere that CPA is referenced in CLIENT.



Please keep in mind: 1) once the information is populated in the cells in
CLIENT, it is going to be used for a mail merge, and 2) not all of the
entries in CLIENT have a CPA, and not all of the CPAs have information in
all of the possible fields.



Thank you in advance.
 
sounds like you are "committing spreadsheet". you need to normalize your
table structure. roughly speaking, Client info goes in tblClients. CPA info
goes in tblCPAs. each CPA's information should be entered in the database
*only once* - not multiple times. if each client *always* has only one CPA,
then add the primary key field from tblCPAs to tblClients, as a foreign key.
in a report, you can show the CPA info for a specific client by linking the
tables in a query; in a form, you can show it with a subform.
suggest you search the newsgroups and/or Google for "normalization" or
"table normalization" to learn more about the topic.

hth
 
Okay - I understand not wanting to commit spreadsheet, but I need to be able
to complete rather extensive and complex mail merges for all of the clients,
and have their CPA information be included. I only know how to link a mail
merge to one table or query. How would I be able to have the CPA information
populate the appropriate places in the mail merge?

Thank you.
 
link the two tables, on the matching primary key/foreign key, in one query.
use that query for your mail merge.
if you've never created multiple-table queries, suggest you get the
Microsoft Access <version> Bible by Prague and Irwin
it will teach you how to set table relationships, build multi-table queries,
and a whole lot more.
also, a normalized table structure is always recommended, but especially
important in a multi-user database - you don't want to deal with the many
problems of unnormalized data in a database that other people use and depend
on! suggest you get a copy of
Database Design for Mere Mortals by Hernandez
it's a much recommended text in these newsgroups.

hth
 
also, here's the text of a post from MVP Albert Kallal, dated 8/23/04
10:13AM. he was answering somebody else's question, but you may find the
info useful in your situation, as well as other useful info on his website.

* * * * * * * *
Download my sample word merge.

It is setup to merge the current record you are looking at, but the sample
also shows how to send sql to the merge.

It is easy to use.

Try the sample with the data to see if it what you are looking for.

you can find it at:
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

* * * * * * * *

hth
 
tina said:
sounds like you are "committing spreadsheet". you need to normalize your
table structure. roughly speaking, Client info goes in tblClients. CPA info
goes in tblCPAs. each CPA's information should be entered in the database
*only once* - not multiple times. if each client *always* has only one CPA,
then add the primary key field from tblCPAs to tblClients, as a foreign key.
in a report, you can show the CPA info for a specific client by linking the
tables in a query; in a form, you can show it with a subform.
suggest you search the newsgroups and/or Google for "normalization" or
"table normalization" to learn more about the topic.

hth


entry name
Click to expand...
 
Back
Top