Auto populate fields

L

LG

These are the fields I have in a form. I already have it that where we have
a query set up to populate Reason1,2, and 3 once a number is entered into the
box the description shows in the field for a mail merge.(Reason Code field is
set up as number) Now I need to be able to type in ex.5000 into the CMS
field and run a query and it will populate Client, AppealTTY, Comp, Add2 etc.
(The field is not always all number some are alpha numeric.) There is form
that will be constructed of 3 pages total and have the fields below fill in
the appropriate places. Is this possible?
Fields:
QCP_ID, Last_Name, First_Name, Address1, Address2 ,City ,State, ZipCode,
Mbr_ID,
Rcvd, Meds, Reason1, Reason2, Reason3 ,Proc, CMS, Client, Appeals TTY, Comp,
Add2, City2, State2, Zipcode2, Date
 
A

Al Campagna

LG,
On my website (below), I have an Access 97 and 2003 sample file called
Combobox Populates Multiple Fields. You select a value from a combo or
list,
and fields related to that value, from external tables, can be displayed on
the form.

Be aware that these "associated" controls/fields do not capture this
ancillary
information... just display it. (by calculation)

Example: Select a Customer ID (CustID) from a combobox, and display
the associated CustName, CustAddress, and CustCity.
Since we capture the CustID to our table, we don't need to also capture
the "related" information... just display it for the user to see.
We simply use the "captured" CustID to reunite with the associated
infromation..."on the fly,"
in any subsequent query, form, or report.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

John W. Vinson

There is form
that will be constructed of 3 pages total and have the fields below fill in
the appropriate places. Is this possible?

It's possible. It's also almost surely a VERY BAD IDEA!!!

You're using a relational database; use it relationally! Store this address
and other information *once, and once only*, in the table you're using as the
source of the combo box. In any other table, store only one field - the
foreign key to the Primary Key of that table. You can then base a report (or
an export, or a form, or another query, or an export) on a Query joining the
two tables, without storing the information redundantly.
 
L

LG

I would like to only store the CMS field and run a query to populate the
other information when printing the form. In the table the only thing that
needs to be stored is the CMS Field. I am not sure how to get the other
fields to populate in a query if I have the CSM field filled even with a
combo box the appeals2 and tty etc have to be seperate fields since they
poplulate different places in the form I have 2 tables set up 1 for the
member and the other are the CSM clients.
 
A

Al Campagna

LG,
Well, that's what John and I are suggesting...
Did you try my sample file, to see how I did it?

If the CMS is your unique key field then use it, in your query design,
to relate to any other table/s that contain the ancillary information
associated
with that CMS.
Form Table Some other Related Table
CMS ----------> CMS
Address
City
Zip ..etc
Try my sample, and you'll see how I relate the ancillary info... and the
Key Value... right in the query behind the combo box. After the combo
is updated, my calculated "other fields" display the correct information.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

John W. Vinson

I would like to only store the CMS field and run a query to populate the
other information when printing the form. In the table the only thing that
needs to be stored is the CMS Field. I am not sure how to get the other
fields to populate in a query if I have the CSM field filled even with a
combo box the appeals2 and tty etc have to be seperate fields since they
poplulate different places in the form I have 2 tables set up 1 for the
member and the other are the CSM clients.

What Al said.

The only thing I'll add is that Forms are for onscreen viewing - Reports are
for printing. Create a Query joining the two tables, select fields from either
or both tables, and base a report on that Query.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top