Combo box selection question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a subform with two combo boxes. The combo boxes have been set up so
that the selection in the first combo box limits the selection list of the
second combo box. These boxes seem to be working propoerly, but I would like
to make a small change and that is where I am having a problem.
I would like that when you change the first box (Medication name) then the
second box (Dosages) would become blank or immediately remove the previous
dosage. Then when you pulled the drop down menu, you would select from the
new list.
At this time, the second cbo shows the "old" dosage until you select from
the new drop down list. I am currently using a macro Requery On Change event
to refresh the second cbo. Both cbos are bound to fields in a
tblMedicationRecord.
Any help is appreciated very much. Thank you.
 
Hi Kathy

Your first combo box probably has an AfterUpdate event procedure that says
something like this:
cboDosage.Requery

(Either that or some code that sets cboDosage.RowSource)

Before that line, add this:
cboDosage = Null

(of course, "cboDosage" should be replaced by the name of your combo box)
 
That was IT...many thanks!
Thus I am wondering if I could ask another "small" question...the first
combo box (Medication Name) is stored on the table as a MedID (medication
number), not as the text name..this is probably happening (I am guessing) by
the way the combo boxes are linked in order to limit the selections. Is
there any way to have the text name stored, instead of the medID?
Thank you again and I hope I have not abused your goodwill. Best wishes!
--
Kbelo


Graham Mandeno said:
Hi Kathy

Your first combo box probably has an AfterUpdate event procedure that says
something like this:
cboDosage.Requery

(Either that or some code that sets cboDosage.RowSource)

Before that line, add this:
cboDosage = Null

(of course, "cboDosage" should be replaced by the name of your combo box)
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Kathy said:
I have a subform with two combo boxes. The combo boxes have been set up so
that the selection in the first combo box limits the selection list of the
second combo box. These boxes seem to be working propoerly, but I would
like
to make a small change and that is where I am having a problem.
I would like that when you change the first box (Medication name) then the
second box (Dosages) would become blank or immediately remove the previous
dosage. Then when you pulled the drop down menu, you would select from the
new list.
At this time, the second cbo shows the "old" dosage until you select from
the new drop down list. I am currently using a macro Requery On Change
event
to refresh the second cbo. Both cbos are bound to fields in a
tblMedicationRecord.
Any help is appreciated very much. Thank you.
Click to expand...
Click to expand...
 
In your table with the MedID do you also have a field for the MedName? When
you create the combo box it pulls a wizard and you select what fields you
want to use. Choose MedID and MedName and it will have a check box that asks
if you want to 'hide the key column (recommended)'. Once you finish the
wizard you will have a drop down box of the name of the medication that is
technically pulling and storing by the ID.
Hope that helped

Kathy said:
That was IT...many thanks!
Thus I am wondering if I could ask another "small" question...the first
combo box (Medication Name) is stored on the table as a MedID (medication
number), not as the text name..this is probably happening (I am guessing) by
the way the combo boxes are linked in order to limit the selections. Is
there any way to have the text name stored, instead of the medID?
Thank you again and I hope I have not abused your goodwill. Best wishes!
--
Kbelo


Graham Mandeno said:
Hi Kathy

Your first combo box probably has an AfterUpdate event procedure that says
something like this:
cboDosage.Requery

(Either that or some code that sets cboDosage.RowSource)

Before that line, add this:
cboDosage = Null

(of course, "cboDosage" should be replaced by the name of your combo box)
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Kathy said:
I have a subform with two combo boxes. The combo boxes have been set up so
that the selection in the first combo box limits the selection list of the
second combo box. These boxes seem to be working propoerly, but I would
like
to make a small change and that is where I am having a problem.
I would like that when you change the first box (Medication name) then the
second box (Dosages) would become blank or immediately remove the previous
dosage. Then when you pulled the drop down menu, you would select from the
new list.
At this time, the second cbo shows the "old" dosage until you select from
the new drop down list. I am currently using a macro Requery On Change
event
to refresh the second cbo. Both cbos are bound to fields in a
tblMedicationRecord.
Any help is appreciated very much. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Hi and thank you for your help....at this time, it looks like what you have
described is exactly what is happening. So I see the MedName in the combo box
on the form, but it is stored as the MedID in the table. I am wondering if I
can somehow store the MedName instead. The reason is that another form also
pulls from this table field, so instead of "Procrit" in the new form, it
comes up as the MedID, "2". Is there something I can do to correct this
problem?
Thank you again for your help and advice.
--
Kbelo


Pixie78 said:
In your table with the MedID do you also have a field for the MedName? When
you create the combo box it pulls a wizard and you select what fields you
want to use. Choose MedID and MedName and it will have a check box that asks
if you want to 'hide the key column (recommended)'. Once you finish the
wizard you will have a drop down box of the name of the medication that is
technically pulling and storing by the ID.
Hope that helped

Kathy said:
That was IT...many thanks!
Thus I am wondering if I could ask another "small" question...the first
combo box (Medication Name) is stored on the table as a MedID (medication
number), not as the text name..this is probably happening (I am guessing) by
the way the combo boxes are linked in order to limit the selections. Is
there any way to have the text name stored, instead of the medID?
Thank you again and I hope I have not abused your goodwill. Best wishes!
--
Kbelo


Graham Mandeno said:
Hi Kathy

Your first combo box probably has an AfterUpdate event procedure that says
something like this:
cboDosage.Requery

(Either that or some code that sets cboDosage.RowSource)

Before that line, add this:
cboDosage = Null

(of course, "cboDosage" should be replaced by the name of your combo box)
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a subform with two combo boxes. The combo boxes have been set up so
that the selection in the first combo box limits the selection list of the
second combo box. These boxes seem to be working propoerly, but I would
like
to make a small change and that is where I am having a problem.
I would like that when you change the first box (Medication name) then the
second box (Dosages) would become blank or immediately remove the previous
dosage. Then when you pulled the drop down menu, you would select from the
new list.
At this time, the second cbo shows the "old" dosage until you select from
the new drop down list. I am currently using a macro Requery On Change
event
to refresh the second cbo. Both cbos are bound to fields in a
tblMedicationRecord.
Any help is appreciated very much. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
PS: On the second form, I tried to change the control source of the Medicaton
text box to "=cboMeds.Column(2)", but without any luck. It "felt right" but I
must be missing something? Thank you again.
--
Kbelo


Kathy said:
Hi and thank you for your help....at this time, it looks like what you have
described is exactly what is happening. So I see the MedName in the combo box
on the form, but it is stored as the MedID in the table. I am wondering if I
can somehow store the MedName instead. The reason is that another form also
pulls from this table field, so instead of "Procrit" in the new form, it
comes up as the MedID, "2". Is there something I can do to correct this
problem?
Thank you again for your help and advice.
--
Kbelo


Pixie78 said:
In your table with the MedID do you also have a field for the MedName? When
you create the combo box it pulls a wizard and you select what fields you
want to use. Choose MedID and MedName and it will have a check box that asks
if you want to 'hide the key column (recommended)'. Once you finish the
wizard you will have a drop down box of the name of the medication that is
technically pulling and storing by the ID.
Hope that helped

Kathy said:
That was IT...many thanks!
Thus I am wondering if I could ask another "small" question...the first
combo box (Medication Name) is stored on the table as a MedID (medication
number), not as the text name..this is probably happening (I am guessing) by
the way the combo boxes are linked in order to limit the selections. Is
there any way to have the text name stored, instead of the medID?
Thank you again and I hope I have not abused your goodwill. Best wishes!
--
Kbelo


:

Hi Kathy

Your first combo box probably has an AfterUpdate event procedure that says
something like this:
cboDosage.Requery

(Either that or some code that sets cboDosage.RowSource)

Before that line, add this:
cboDosage = Null

(of course, "cboDosage" should be replaced by the name of your combo box)
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a subform with two combo boxes. The combo boxes have been set up so
that the selection in the first combo box limits the selection list of the
second combo box. These boxes seem to be working propoerly, but I would
like
to make a small change and that is where I am having a problem.
I would like that when you change the first box (Medication name) then the
second box (Dosages) would become blank or immediately remove the previous
dosage. Then when you pulled the drop down menu, you would select from the
new list.
At this time, the second cbo shows the "old" dosage until you select from
the new drop down list. I am currently using a macro Requery On Change
event
to refresh the second cbo. Both cbos are bound to fields in a
tblMedicationRecord.
Any help is appreciated very much. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Hi Kathy

There are two possible approaches:

One is to create a query which joins the two tables on the MedID field, and
include the MedName field in your query. That way, the "translation" from
MedID to MedName is done by your query, and all you need to do is include
that field on your form or report, or whatever.

If you really want to store the text in your many-side table, then your
MedID primary key serves no purpose whatsoever. You could delete it, and
make MedName the "natural" primary key. However, all related tables would
need to be changed to store the name instead of the ID.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Kathy said:
Hi and thank you for your help....at this time, it looks like what you
have
described is exactly what is happening. So I see the MedName in the combo
box
on the form, but it is stored as the MedID in the table. I am wondering if
I
can somehow store the MedName instead. The reason is that another form
also
pulls from this table field, so instead of "Procrit" in the new form, it
comes up as the MedID, "2". Is there something I can do to correct this
problem?
Thank you again for your help and advice.
--
Kbelo


Pixie78 said:
In your table with the MedID do you also have a field for the MedName?
When
you create the combo box it pulls a wizard and you select what fields you
want to use. Choose MedID and MedName and it will have a check box that
asks
if you want to 'hide the key column (recommended)'. Once you finish the
wizard you will have a drop down box of the name of the medication that
is
technically pulling and storing by the ID.
Hope that helped

Kathy said:
That was IT...many thanks!
Thus I am wondering if I could ask another "small" question...the first
combo box (Medication Name) is stored on the table as a MedID
(medication
number), not as the text name..this is probably happening (I am
guessing) by
the way the combo boxes are linked in order to limit the selections.
Is
there any way to have the text name stored, instead of the medID?
Thank you again and I hope I have not abused your goodwill. Best
wishes!
--
Kbelo


:

Hi Kathy

Your first combo box probably has an AfterUpdate event procedure that
says
something like this:
cboDosage.Requery

(Either that or some code that sets cboDosage.RowSource)

Before that line, add this:
cboDosage = Null

(of course, "cboDosage" should be replaced by the name of your combo
box)
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a subform with two combo boxes. The combo boxes have been set
up so
that the selection in the first combo box limits the selection list
of the
second combo box. These boxes seem to be working propoerly, but I
would
like
to make a small change and that is where I am having a problem.
I would like that when you change the first box (Medication name)
then the
second box (Dosages) would become blank or immediately remove the
previous
dosage. Then when you pulled the drop down menu, you would select
from the
new list.
At this time, the second cbo shows the "old" dosage until you
select from
the new drop down list. I am currently using a macro Requery On
Change
event
to refresh the second cbo. Both cbos are bound to fields in a
tblMedicationRecord.
Any help is appreciated very much. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top