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
Mia said:
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!