Combo box selection question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kathy - 15 May 2007 03:28 GMT

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.

Signature

Kbelo

Reply to this Message

Graham Mandeno - 15 May 2007 03:36 GMT

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)

Signature

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
[quoted text clipped - 11 lines]
> tblMedicationRecord.
> Any help is appreciated very much. Thank you.

Reply to this Message

Kathy - 15 May 2007 04:36 GMT

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!

Signature

Kbelo

> Hi Kathy
>
[quoted text clipped - 23 lines]
> > tblMedicationRecord.
> > Any help is appreciated very much. Thank you.

Reply to this Message

Pixie78 - 15 May 2007 15:41 GMT

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

> That was IT...many thanks!
> Thus I am wondering if I could ask another "small" question...the first
[quoted text clipped - 31 lines]
> > > tblMedicationRecord.
> > > Any help is appreciated very much. Thank you.

Reply to this Message

Kathy - 15 May 2007 16:08 GMT

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.

Signature

Kbelo

> 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
[quoted text clipped - 39 lines]
> > > > tblMedicationRecord.
> > > > Any help is appreciated very much. Thank you.

Reply to this Message

Kathy - 15 May 2007 22:43 GMT

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.

Signature

Kbelo

> 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
[quoted text clipped - 48 lines]
> > > > > tblMedicationRecord.
> > > > > Any help is appreciated very much. Thank you.

Reply to this Message

Graham Mandeno - 16 May 2007 03:44 GMT

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.

Signature

Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

> Hi and thank you for your help....at this time, it looks like what you
> have
[quoted text clipped - 69 lines]
>> > > > tblMedicationRecord.
>> > > > Any help is appreciated very much. Thank you.

Reply to this Message