Cathi,
From what you are describing, you might have your tables set up incorrectly.
Dept and Category should be two different tables and should look something
like:
tblDept
lngDeptID (Set this field to be Autonumber)
strDeptName
....Other department specific fields would go here...
tblCategory
lngCatagoryID (Set this field to be Number of type Long Integer)
lngDeptID
strCategoryName
....Other category specific fields would go here...
The lngDepID (in tblDept) and lngCategoryID (in tblCategory) would be the
primary keys for the 2 tables. lngDeptID (in tblCategory) is the forign key.
You will need to create a relationship (Tools->Relationships) between the
two tables. The relationship would be a One-to-Many relationship (i.e. For
each department, there are many categories). Look in Access help and search
on relationships. You should be able to find a step-by-step guide on how to
set up a One-to-Many relationship.
Once the 2 tables are linked correctly, you would store the ID values into
you table for the subform (you have not specified what it is). I have just
created a fictional one to use as an example - tblEmployee.
tblEmployee
lngEmployeeID
lngDeptID
lngCategoryID
strName
strJobTitle
....Other employee specific fields would go here...
The recordsource for the sub form would be tblEmployee. Once you have all
this, you can set up the combobox's. The Control Source for the combobox is
where the data selected in it will be stored. In this case, it will be
lngDeptID (in tblEmployee) for the dept combobox and lngCategoryID (in
tblEmployee) for the category combobox. The Row Source Type for both combo's
would need to be set to Table/Query and then the Row Source would be an SQL
statement which would look something like:
for Dept combo.
SELECT tblDept.lngDeptID, tblDept.strDeptName FROM tblDept;
for Category combo.
SELECT tblCategory.lngCategoryID, tblCategory.lngDeptID,
tblCategory.strCategoryName FROM tblCategory WHERE tblCategory.lngDeptID =
Forms!MainFormName!SubFormControlName!DepartmentComboBoxName;
Notice in the WHERE clause, we are getting the value of the department
combobox. You will need to replace MainFormName with the name of the main
form. SubFormControlName with the name of the sub form control (this is
usually BUT NOT always the name of the sub form itself) and
DepartmentComboBoxName with the name of the department combobox. Also, if
you have used spaces while naming your objects, enclose the full name in []
(e.g. Forms![My Form]![My Control]![Some Comb].
You probably want to hide the ID data from the user so you would set other
properties as follows:
for Dept combo
Column Count = 2
Column Widths = 0cm;2.54cm (note 0 as the 1st width - this will hide the
column but store the value into lngDeptID in tblEmployee)
for Category combo
Column Count = 3
Column Widths = 0cm;0cm;2.54cm
Now you need to synchronise the two combos. To do this you need some code.
Goto the Dept combo's properties and click on the 'Event' Tab. Left click in
the After Update field. On the right hand side of this, there will be a
small button with ... Click on this and select Code Builder from the list
and click OK. You can now place code into the after update event for the
dept combobox. You will have 2 lines of code which will look something like
(amongst a lot of other items):
Private Sub cboDept_AfterUpdate()
End Sub
All you have to do is requery the Category combobox. Place an additional
line of code to requery the combobox
Private Sub cboDept_AfterUpdate()
Me.CategoryComboBoxName.Requery
End Sub
Again, replace Me.CategoryComboBoxName with the name of your category
combobox (use the [] with any spaces again)
One last thing to do is to make the combos synchronised again when the user
moves from record to record. Use the forms Current event to do this. Go back
to your sub form in design view and double click on the square in the top
left corner of the form (where the two rulers meet). Under 'Event' again,
repeat what u did above for the After Update event except you need to be in
the On Current Field. Modify the code to include the requery.
Private Sub cboDept_AfterUpdate()
Me.CategoryComboBoxName.Requery
End Sub
Hopefully, all will be well and you now have two working combos that are in
synch and remember the values chosen. You will probably need to make a lot
more changes to get the desired results with your check box's, but this
should give you a good idea on how to approch the solution.
I am now off for a well earned coffee!
Good Luck,
Neil.
Cathi said:
I have a continuous subform that contains that following: 2 check boxes -
pos & neg (both can be checked), a field for Dept and a field for Category.
Ideally I would like those two text boxes to be sync'd combo boxes; so that
whatever Dept the user clicks on will requery the categories combo box to
display only the categoies associated with that specific dept.
Problems encountered....Tried to make both combo boxes have bound fields
(bad plan - duh) So, at the very, very least I need to at least make the
categories box bound BUT sync'd combo boxes don't appear to like anything
"bound" - is this a fact or am I possibly doing something wrong.
The other BIGGIE is that once the record has been added I need it to
always display what has been selected (the need for the combo boxes to be
bound) so that when the user goes back into the record it displays what dept
and category(ies) have been assigned.
The same dept can be selected multiple times, but only if the checkbox
"neg" is checked does it require a category. One department can have
multiple neg categories also. So, each row in the subform needs to reflect
this.
