Dropdown box on on name rather than number?

[CJ]

Patients-physician-querry
relational database with 2 tables, physicians and Clients,
In the Client DB, there is a number that implies the physician,

I can easily make a form where you put in the physicians number,
but..
If I want the dropdown box to show the name of the physician I have to
base it on the other table. (the physician table),

Now when I choose a different last name it will update the wrong
table. (the physician table),

In the client form, is it possible to choose the physician based on
his name rather than the number?

thanks,
crzzy1

 

[Bob Quintal]

(e-mail address removed) (CJ) wrote in

Patients-physician-querry
relational database with 2 tables, physicians and Clients,
In the Client DB, there is a number that implies the
physician,

I can easily make a form where you put in the physicians
number, but..
If I want the dropdown box to show the name of the physician I
have to base it on the other table. (the physician table),

Now when I choose a different last name it will update the
wrong table. (the physician table),

In the client form, is it possible to choose the physician
based on his name rather than the number?

thanks,
crzzy1

Yes, this can easily be done with the Combobox wizard, when you
create a new combobox.

Just make the first column the number, and the second (and third)
last name (and firstname). You can make the width of the number
column 0, so it doesn't show in the combobox, but stores it in the
table..

..
Bob Q

 

[CJ]

(e-mail address removed) (CJ) wrote in

Yes, this can easily be done with the Combobox wizard, when you
create a new combobox.

Just make the first column the number, and the second (and third)
last name (and firstname). You can make the width of the number
column 0, so it doesn't show in the combobox, but stores it in the
table..

.
Bob Q

Hmm that is good,
When I base it on my querry,
I get many instances of the same doctor's name in the dropbox, (one
for each patient)
I need to base the physicianID on the "patient table" (the foreigh
key) (that is the field I want the form to update)
I need to base the physician name on the "physician table".
When I do base both on the physician table, I can get a good listing
of non-duplicated last names, but it won't let me update the form as
it thinks I am trying to change the physician's name in that table.
any more ideas?

 

[Bob Quintal]

(e-mail address removed) (CJ) wrote in

Hmm that is good,
When I base it on my querry,
I get many instances of the same doctor's name in the dropbox,
(one for each patient)
I need to base the physicianID on the "patient table" (the
foreigh key) (that is the field I want the form to update)
I need to base the physician name on the "physician table".
When I do base both on the physician table, I can get a good
listing of non-duplicated last names, but it won't let me
update the form as it thinks I am trying to change the
physician's name in that table. any more ideas?

The combobox RowSource must be based on the physicians table. The
combobox ControlSource must point to the physicianID in the the
Clients table.

Then everything will work as you wish.

Bob Q.