Linking Combo Boxes

[Sardonic]

Novice user, so apologies if this is a common question

I have a subform with Combo Boxes - (Job Category) and (Job Role). I want
to select a subset of Job Role based on the user selection in Job Category.
I've attempted to use the solution outlined in the Office Online Assistance
under "basing one combo box on another,
(http://office.microsoft.com/en-gb/assistance/HA011730581033.aspx) but I end
up with subforms that

1) Reset *all* Job Categories based on the first choice made
2) Doesn't connect the choice made in Job Category nor with the lookup table
called "LKUP Job Role" with the Job Role Combo Box

I don't think it helps that the example outlined in assistance uses the same
name for the combo boxes and tables!

Do I need to redesign the subfrom from scratch, as I've noticed Access
doesn't like changing Table fields after a form has been designed.

 

[Al Camp]

Sardonic,
1. Your JobCategory combo has not been "bound" to a field in your
table... I would guess your JobCategory field. It is "unbound" now, and
that's why all JobCatergory fields in the subform show the mosst recent
choice.
Make the Control source of the JobCategory combo (you should use the
name cboJobCategory) your JobCategory field.

2. Your JobRole combo (name it cboJobRole) should use the value from
cboJobCategory to filter the query behind it, and only deliver those JobRole
values where the associated JobCategory = cboJobCategory.
It also should be "bound" to the JobRole field from your table

On my web site, under Access Tips, I have a downloadable ("Synched
Combo Boxes") zip file that demostrates this technique. There's an Access
97 and Access 2003 version.

 

[Sardonic]

Al,

Thank you very much for your help.

This worked very well oin a single-record form.

I then tried to do the same for a form containing a sub-form showing many
associated records. On each row of the sub-form, I have two combo boxes, the
first one controlling the options in the second. When I open the form, this
is correct. Each second combo-box has the options appropriate to the
selected value in the first combo-box.

However, when I change the selection in the first box, ALL the second boxes
on ALL the rows of the sub-form are changed. The REQUERY appears to mean
that ALL of the second combo-boxes are now limited to the options dictated by
the selection of the first combo-box on the line which has just changed.

Is there any way that I can restrict the REQUERY and similar actions on the
second combo-box to the box on the CURRENT line (i.e. the same line as the
line in which the first combo-box is changed)?

 

[Lee Robinson]

You have run into the problem of the difference in concept between single
forms and continuous forms. On a single form, Access knows what to put into
the bound textboxes and comboboxes because it has a single current record
and the values from that record go into the appropiate bound controls
(textboxes and comboboxes). The UNBOUND textboxes and comboboxes show
whatever the most recent value is for them. They are like variables in the
VBA code in that they only have one place in memory for their contents.
Unbound controls on single forms appear just like bound controls because we
are only seeing one record at a time. If you don't have something which
changes them as the records change, you would notice that their value stays
the same even when you flip from one record to the next using the record
selector. They do not change value based on the record. Some event changes
them. Some VBA code or macro or something changes them based on the current
record or some event such as selecting something in a combobox.

Continous forms on the other hand are showing multiple records at a time. A
copy of the detail section of the form is created for each record, including
copies of the unbound controls. But, only one of these records is current
at any one time. The unbound controls are still showing the most recent
value (since there is only one place in memory holding the value, each copy
of the unbound textbox is showing that one value). They are still behaving
the same way they did with the single forms. They are still showing the
most recent value. What looks different is that a copy of the textbox
appears in each with each record. This is a copy of the unbound control,
there is still only one control.

To get around this problem, I create a temporary table when the form opens.
This temporary table includes the fields from the records and fields for the
what were the unbound controls. This makes all of the controls "bound" to
fields in the temporary table. The only thing left is to figure out how to
update the real records rather than just the ones in the temporary table.
How I update the records depends on the application.

Lee Robinson

 

[Kevin]

Hi,
Like Sardonic, I need to link two combo boxes. I've followed the advice you
gave that person and had a look at the examples on your website and tried to
apply them to my database. The problem for me is that I already have the two
combo boxes ('Select a Region' and 'Select a Country')filtered using the
"Me.Filter" string to display records on my form. For eg:

Private Sub Combo65_AfterUpdate()
Me.Filter = "chrRegion = '" & Me.Combo65 & "'"
Me.FilterOn = True
End Sub

and when i try to add your (modified) code nothing happens. I tried putting:

Private Sub Combo65_AfterUpdate()
Combo92 = Null
Combo92.Requery
Me.Filter = "chrRegion = '" & Me.Combo65 & "'"
Me.FilterOn = True
End Sub

Is the Me.Filter string incompatible with your code? Also, when I bind the
combo boxes to the related field on the form, that field changes but nothing
else does anymore.
I guess I'm doing something completely wrong here, so If you could help me I
would be very grateful!

Best Regards