Filtering drop down selection

[Mia]

I have table 'Bed Status' with lookup fields for field 'Division' & 'Unit'
from table 'DivisionUnitBedCapacity'

Division has 5 choices and units belong to on of these five.

Trying to filter the unit choices by the division that the end user chooses?

I can't seem to get the filter to work any solutions?

 

[Mia]

So here is what my DivisonUnitBedCapacity table looks like (minus a lot of
entries),

ID Division Unit Bed Capacity
1 BHS 1JPE 15
14 CWS Acute 2JCP 35
15 CWS Acute 3JCW 26
21 CWS Acute NNSY-2
22 CWS ICU NIC-1 12
23 CWS ICU NIC-2-5 57
27 ISS ICU SICU-2 8
28 ISS ICU SICU-3 8
34 ISS Inter 4JCW 27
35 ISS Inter 4RCW 16
44 MSS 7RCW 14
45 MSS 6RCE 24

Here is what the Bed Status table looks like.

ID Date Division Unit Occupied Beds % Occupied 11/19/2008 BHS 4SE 0.00%

I want on my form for the user to be able to chose the "Division", then the
Unit be filtered down to include only those units under that Division. (ex.
BHS is chosen for the Division, then they would have 1 unit choice from the
above table.) Also, I am trying to calculate the percentage of beds left.
Each unit has a standard bed capacity that is in the DivisionUnitBedCapacity
Table and the user will enter the occupied beds and would like the % of
occupied beds to populate? I am so sorry this is confusing, but I am really
new to this so you just responding to my previous post is a huge help!

Thanks a bunch

 

[Mia]

Steve,

Thanks this has helped, and I followed your instructions to the tee. Now
when I choose Division in my form, then my only choices under the Unit box is
a Division not the units? It is showing the division the same amount of
times as records I have under that division?

And yes I did put the Unit field first on the grid because that was the
first thing I checked when this happend? Any suggestions?

Mia,

Make a query based on your DivisonUnitBedCapacity table, and add the Unit
and Division fields (in that order) to the query design grid. In the
Criteria of the Division column, enter the equivalent of this:
[Forms]![NameOfYourForm]![Division]
Replace with the actual name of your form, and also assumes that Division is
the name of the division combobox on the form.

Close and save the query. Then you will assign this query on the Row Source
property of the Unit combobox.

Next, make a macro using the Requery action to requery the Unit combobox on
the After Update event of the Division combobox.

As regards the % Occupied, first of all it is not a good idea to use a %
symbol as part of the name of a field. But in any case, this field should
not be in your table. This is a derived or calculated value, which should
normally not be stored in a table. Whenever you need this PercentOccupied
value for your purposes on forms or reports, it should be calculated "on the
fly" using a calculated field in a query.

--
Steve Schapel, Microsoft Access MVP

So here is what my DivisonUnitBedCapacity table looks like (minus a lot of
entries),

ID Division Unit Bed Capacity
1 BHS 1JPE 15
14 CWS Acute 2JCP 35
15 CWS Acute 3JCW 26
21 CWS Acute NNSY-2
22 CWS ICU NIC-1 12
23 CWS ICU NIC-2-5 57
27 ISS ICU SICU-2 8
28 ISS ICU SICU-3 8
34 ISS Inter 4JCW 27
35 ISS Inter 4RCW 16
44 MSS 7RCW 14
45 MSS 6RCE 24

Here is what the Bed Status table looks like.

ID Date Division Unit Occupied Beds % Occupied 11/19/2008 BHS 4SE 0.00%

I want on my form for the user to be able to chose the "Division", then
the
Unit be filtered down to include only those units under that Division.
(ex.
BHS is chosen for the Division, then they would have 1 unit choice from
the
above table.) Also, I am trying to calculate the percentage of beds left.
Each unit has a standard bed capacity that is in the
DivisionUnitBedCapacity
Table and the user will enter the occupied beds and would like the % of
occupied beds to populate? I am so sorry this is confusing, but I am
really
new to this so you just responding to my previous post is a huge help!

 

[Mia]

Steve,

Got it! I just switched the division field infront of the unit field in the
query.
Thanks so much again for your help!

-Mia

Steve,

Thanks this has helped, and I followed your instructions to the tee. Now
when I choose Division in my form, then my only choices under the Unit box is
a Division not the units? It is showing the division the same amount of
times as records I have under that division?

And yes I did put the Unit field first on the grid because that was the
first thing I checked when this happend? Any suggestions?

Mia,

Make a query based on your DivisonUnitBedCapacity table, and add the Unit
and Division fields (in that order) to the query design grid. In the
Criteria of the Division column, enter the equivalent of this:
[Forms]![NameOfYourForm]![Division]
Replace with the actual name of your form, and also assumes that Division is
the name of the division combobox on the form.

Close and save the query. Then you will assign this query on the Row Source
property of the Unit combobox.

Next, make a macro using the Requery action to requery the Unit combobox on
the After Update event of the Division combobox.

As regards the % Occupied, first of all it is not a good idea to use a %
symbol as part of the name of a field. But in any case, this field should
not be in your table. This is a derived or calculated value, which should
normally not be stored in a table. Whenever you need this PercentOccupied
value for your purposes on forms or reports, it should be calculated "on the
fly" using a calculated field in a query.

--
Steve Schapel, Microsoft Access MVP

So here is what my DivisonUnitBedCapacity table looks like (minus a lot of
entries),

ID Division Unit Bed Capacity
1 BHS 1JPE 15
14 CWS Acute 2JCP 35
15 CWS Acute 3JCW 26
21 CWS Acute NNSY-2
22 CWS ICU NIC-1 12
23 CWS ICU NIC-2-5 57
27 ISS ICU SICU-2 8
28 ISS ICU SICU-3 8
34 ISS Inter 4JCW 27
35 ISS Inter 4RCW 16
44 MSS 7RCW 14
45 MSS 6RCE 24

Here is what the Bed Status table looks like.

ID Date Division Unit Occupied Beds % Occupied 11/19/2008 BHS 4SE 0.00%

I want on my form for the user to be able to chose the "Division", then
the
Unit be filtered down to include only those units under that Division.
(ex.
BHS is chosen for the Division, then they would have 1 unit choice from
the
above table.) Also, I am trying to calculate the percentage of beds left.
Each unit has a standard bed capacity that is in the
DivisionUnitBedCapacity
Table and the user will enter the occupied beds and would like the % of
occupied beds to populate? I am so sorry this is confusing, but I am
really
new to this so you just responding to my previous post is a huge help!

 

[Mia]

Steve

I got it! Thanks I just switched the Division field in front of the Unit
field in the query. Just backwards

Thanks again for all your help!
Mia

Steve,

Thanks this has helped, and I followed your instructions to the tee. Now
when I choose Division in my form, then my only choices under the Unit box is
a Division not the units? It is showing the division the same amount of
times as records I have under that division?

And yes I did put the Unit field first on the grid because that was the
first thing I checked when this happend? Any suggestions?

Mia,

Make a query based on your DivisonUnitBedCapacity table, and add the Unit
and Division fields (in that order) to the query design grid. In the
Criteria of the Division column, enter the equivalent of this:
[Forms]![NameOfYourForm]![Division]
Replace with the actual name of your form, and also assumes that Division is
the name of the division combobox on the form.

Close and save the query. Then you will assign this query on the Row Source
property of the Unit combobox.

Next, make a macro using the Requery action to requery the Unit combobox on
the After Update event of the Division combobox.

As regards the % Occupied, first of all it is not a good idea to use a %
symbol as part of the name of a field. But in any case, this field should
not be in your table. This is a derived or calculated value, which should
normally not be stored in a table. Whenever you need this PercentOccupied
value for your purposes on forms or reports, it should be calculated "on the
fly" using a calculated field in a query.

--
Steve Schapel, Microsoft Access MVP

So here is what my DivisonUnitBedCapacity table looks like (minus a lot of
entries),

ID Division Unit Bed Capacity
1 BHS 1JPE 15
14 CWS Acute 2JCP 35
15 CWS Acute 3JCW 26
21 CWS Acute NNSY-2
22 CWS ICU NIC-1 12
23 CWS ICU NIC-2-5 57
27 ISS ICU SICU-2 8
28 ISS ICU SICU-3 8
34 ISS Inter 4JCW 27
35 ISS Inter 4RCW 16
44 MSS 7RCW 14
45 MSS 6RCE 24

Here is what the Bed Status table looks like.

ID Date Division Unit Occupied Beds % Occupied 11/19/2008 BHS 4SE 0.00%

I want on my form for the user to be able to chose the "Division", then
the
Unit be filtered down to include only those units under that Division.
(ex.
BHS is chosen for the Division, then they would have 1 unit choice from
the
above table.) Also, I am trying to calculate the percentage of beds left.
Each unit has a standard bed capacity that is in the
DivisionUnitBedCapacity
Table and the user will enter the occupied beds and would like the % of
occupied beds to populate? I am so sorry this is confusing, but I am
really
new to this so you just responding to my previous post is a huge help!

 

[Steve Schapel]

Mia,

This type of thing is normally pretty straightforward. Please let us know
the relevant details. I assume you have comboboxes on your form for entry
to the Division and Unit fields. What do you have for the Row Source
properties of these comboboxes? What are the fields (and example data) in
the DivisionUnitBedCapacity table? What else have you tried so far?

 

[Steve Schapel]

Kyle,

The main problem here, I think, is that you are trying to apply the
ApplyFilter macro to the subform, but the criteria and macro event are
related to the main form.

Also you should note that for the ApplyFilter macro action, you use the
Filter Name argument *or* the Where Condition argument, not both.

What do you have in the qrySearch query? This is the Record Source of the
subform, and I would suggest writing the reference to the ComboAlloy
combobox into the query criteria. And then just use a Requery action in the
macro instead, to update the records displayed in the subform.

 

[Steve Schapel]

Mia,

Make a query based on your DivisonUnitBedCapacity table, and add the Unit
and Division fields (in that order) to the query design grid. In the
Criteria of the Division column, enter the equivalent of this:
[Forms]![NameOfYourForm]![Division]
Replace with the actual name of your form, and also assumes that Division is
the name of the division combobox on the form.

Close and save the query. Then you will assign this query on the Row Source
property of the Unit combobox.

Next, make a macro using the Requery action to requery the Unit combobox on
the After Update event of the Division combobox.

As regards the % Occupied, first of all it is not a good idea to use a %
symbol as part of the name of a field. But in any case, this field should
not be in your table. This is a derived or calculated value, which should
normally not be stored in a table. Whenever you need this PercentOccupied
value for your purposes on forms or reports, it should be calculated "on the
fly" using a calculated field in a query.

 

[Steve Schapel]

Thanks for letting me know, Mia. I can't quite understand how that would
happen. There must somehow be something unusual about the settings for the
Column Count and Bound Column and Column Widths properties of the Units
combobox. But anyway I'm happy to know that it's working for you.