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]

Re: Dropdown box on on name rather than number?

cozzmo1@hotmail .com (CJ) wrote in
news:10c555fa.0 404181816.593eb bdc@posting.goo gle.com:
[color=blue]
> 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
>[/color]
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

[Bob Quintal]

Re: Dropdown box on on name rather than number?

cozzmo1@hotmail .com (CJ) wrote in
news:10c555fa.0 404181816.593eb bdc@posting.goo gle.com:
[color=blue]
> 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
>[/color]
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]

Re: Dropdown box on on name rather than number?

Bob Quintal <bquintal@gener ation.net> wrote in message news:<35bacb076 457a833cbbca96a 4f60f49f@news.t eranews.com>...[color=blue]
> cozzmo1@hotmail .com (CJ) wrote in
> news:10c555fa.0 404181816.593eb bdc@posting.goo gle.com:
>[color=green]
> > 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
> >[/color]
> 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[/color]




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]

Re: Dropdown box on on name rather than number?

cozzmo1@hotmail .com (CJ) wrote in
news:10c555fa.0 404191253.56809 76e@posting.goo gle.com:
[color=blue]
> Bob Quintal <bquintal@gener ation.net> wrote in message
> news:<35bacb076 457a833cbbca96a 4f60f49f@news.t eranews.com>...[color=green]
>> cozzmo1@hotmail .com (CJ) wrote in
>> news:10c555fa.0 404181816.593eb bdc@posting.goo gle.com:
>>[color=darkred]
>> > 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
>> >[/color]
>> 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[/color]
>
>
>
>
> 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?
>[/color]

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.