ok im kind of new to this program so forgive me if i sound stupid, hehe

i am making a medical record database that has pull down fields. now
everytime i add content to the pull down field it makes me start over, so
what i want to know is how can i have lets say another form where all the
primary care physicians' names are and then by simply typing a two digit
number, have the name pop up, and if this can be done, where can i find out
how to do this?

rick

what you want is a drop down that is based on a table of primary care
physicians where the table structure includes a Phy_ID field and a
Name field. Your 2 digit code becomes the primary key in this table
(indexed and unique).

tblPriMed
---------
Phy_ID Number Primary Key Required No Dupes
DrName Text 50
PrimaryPhone Text 12

Phy_ID DrName
10 Sanjay Gupta
11 Noone Knowsme
12 Bill Yalatah


Then when you create your dropdown and the wizard pops up direct it to
use the values in this table. The problem you are going to have is in
getting a combobox to behave like a dropdownlist control, they are NOT
the same thing. A combobox in access will not let you type in the
column1 value (Phy_ID) and display the column2 value (DrName). In
order to do this you will need to handle the events in code, you could
use the OnNomatch (because an Phy_ID will never match a DrName and
then try changing the bound control and displaying the selected row in
the newly bound DrName column. sounds a bit funky.


Now lets say you have a listbox and you want to fill it with
PatientName, HomePhone, DrName, PrimaryPhone

Of course you need a Patients table, I'll assume Phy_ID values are in
the range 0f 10-99 and Patient_ID values in the range of 1000-9999

tblPatients
------------
Patient_ID Number Primary Key Required No Dupes
Phy_ID Number
HomePhone Text 12

Patient_ID Phy_ID PatientName HomePhone
1000 10 Sam Wise (888) 777-6666
1001 10 Solomon Wiser (203) 333-4567
1002 12 Upto Nogood (777) 666-5555
1004 12 Paya Latah (222) 666-0000

Then you can create a query (SQL) which shows a list of patients for
the selected doctor, or, vice-versa, the doctors name for the selected
patient. As an example:

qryPatientDoctorContactList
-----------------
Select PatientName, HomePhone, DrName, PrimaryPhone
FROM tblPatients, tblPriMed
WHERE tblPatients.Phy_ID = tblPriMed.Phy_ID
;

OR using uglier but more correct SQL syntax:

SELECT PatientName, HomePhone, DrName, PrimaryPhone
FROM tblPriMed INNER JOIN tblPatients ON tblPriMed.Phy_ID =
tblPatients.Phy_ID;

Both would give you a list of all patients, their phone numbers, and
their doctors name from the other table like this:

PatientName HomePhone DrName PrimaryPhone
Sam Wise (888) 777-6666 Sajay Gupta (203) 377-4231
Solomon Wiser (203) 333-4567 Sajay Gupta (203) 377-4231
Upto Nogood (777) 666-5555 Bill Yalatah (888) 888-8888
Paya Latah (222) 666-0000 Bill Yalatah (888) 888-8888

If you set the SQL query as the contents of the listboxes RowSource
prpoerty and open the form you would get just such a listing there.


happy coding,

John


"RICHARD MALLIA" <rmallia (AT) bellsouth (DOT) net> wrote