limit combo box

[Guest]

I have 2 tables, medication list and medication dose. they are linked by the
name of the medication, a field called medication.

in the table medication list, i want to select the doses available. i tried
a combo box and used the SQL query builder but I can not figure out how to
filter the doses based on teh medication.

how do i tell it to find the dose based on teh value in the medication column?

thanks

 

[Wayne Morgan]

In the Row Source of the dose combo, set it to something like:

SELECT Dose FROM [Medication Dose] WHERE Medication = [cboMedications] ORDER
BY Dose

In the AfterUpdate event of the first combo (i.e. cboMedications) add the
following line:

Me.cboDoseCombo.Requery

This will cause the Dose combo to reread its Row Source when you change the
Medication selection.

 

[Guest]

Wayne,
i do not see an afterupdate box. do i have to do this in a form or can i do
it in a table?

also, what do you mean by cbomedication? why is it cbo and not just
medication?

thanks
russ

In the Row Source of the dose combo, set it to something like:

SELECT Dose FROM [Medication Dose] WHERE Medication = [cboMedications] ORDER
BY Dose

In the AfterUpdate event of the first combo (i.e. cboMedications) add the
following line:

Me.cboDoseCombo.Requery

This will cause the Dose combo to reread its Row Source when you change the
Medication selection.

--
Wayne Morgan
MS Access MVP

I have 2 tables, medication list and medication dose. they are linked by
the
name of the medication, a field called medication.

in the table medication list, i want to select the doses available. i
tried
a combo box and used the SQL query builder but I can not figure out how to
filter the doses based on teh medication.

how do i tell it to find the dose based on teh value in the medication
column?

thanks

 

[Fritz Haubrich via AccessMonster.com]

Hi,

I had the same problem. Waynes hint helped me a lot.

You have to say Me.cboDoseCombo.Requery when you display the form where both combo boxes are to appear (in German it is the "Beim Anzeigen" event of the form).

cbomedication means the name of the combo box in the form which displays the medicine.

Fritz

 

[Wayne Morgan]

No, this can't and shouldn't be done in a table. It has to be done in a
form. Tables are just for storing the data. Any additional work has to be
done using a query or form.

The AfterUpdate is an event procedure on the Events tab of the combo box's
Properties sheet in form design view. You set it for [Event Procedure] then
click the ... button to go to the VBA editor. The example was VBA code. The
name was just a "placeholder". You would have to replace it with the actual
name of your combo box (Other tab of the Properties sheet).

The cbo is short for Combobox. Naming controls with a prefix helps tell what
type of control they are when you see them in code. It also helps prevent
the control from having the same name as the field it is bound to, which can
sometimes cause a problem. Here are some other examples.

http://www.mvps.org/access/general/gen0012.htm

 

[Larry Daugherty]

Hi Russ,

Absolutely, positively do it on a form and not in a table. There should
never be a need for a user (even the person who designed the application) to
get into the tables to massage the data. Access is not like Excel where the
user is usually permitted, even required to get into the cells and enter
data or play with the formulas. While you can create a finished application
with Excel and provide a slick user interface, it's rarely done. In Access,
it's expected that all data entry and massaging and the queuing of reports
will be done via forms.

When you design your form and place controls on it you will see the events
listed on the Event tab of the Property sheet. If these are mew ideas to
you then you're at the starting point of a very long and steep learning
curve! Access is a very useful tool for what it sounds like you're trying
to do. If you haven't yet read a book on Access you should visit your local
Barnes & Noble or Borders, etc. and visit the computer books section. I
recommend starting with "Access [YourVersion] Step By Step". If you can't
find that, you might try "Access for Dummies". No, you're not a dummy but
it starts at a basic level. Starting from scratch you can get something
useful in a couple of weeks or more of applied effort. But it won't be very
good and as you learn more about Access you'll probably continue to revisit
it.

As well as this newsgroup there are other groups that focus on just parts of
Access. They all start out with the name of this newsgroup but then add a
period and you'll find groups such as .gettingstarted, .tablesdesign,
..forms, etc.

This response is intended to be helpful, not to discourage you. Far from
it. I do encourage you to learn Access and to create great applications.
--
HTH

-Larry-
--

Wayne,
i do not see an afterupdate box. do i have to do this in a form or can i do
it in a table?

also, what do you mean by cbomedication? why is it cbo and not just
medication?

thanks
russ

In the Row Source of the dose combo, set it to something like:

SELECT Dose FROM [Medication Dose] WHERE Medication = [cboMedications] ORDER
BY Dose

In the AfterUpdate event of the first combo (i.e. cboMedications) add the
following line:

Me.cboDoseCombo.Requery

This will cause the Dose combo to reread its Row Source when you change the
Medication selection.

--
Wayne Morgan
MS Access MVP

I have 2 tables, medication list and medication dose. they are linked by
the
name of the medication, a field called medication.

in the table medication list, i want to select the doses available. i
tried
a combo box and used the SQL query builder but I can not figure out how to
filter the doses based on teh medication.

how do i tell it to find the dose based on teh value in the medication
column?

thanks