Filter three combo boxes

[DBH]

I have a database with 4 tables and have a form with 4 combo boxes. I want
to filter the 2nd, 3rd, and 4th based upon information in each table using a
field from each table. I can make it working fine for the first 2 combo
boxes but when I try using the same filtering techniques for the 3rd and 4th
one, the rules don't seem to work.
Is this some type of technical limitation
Thanks!
Dave Hauer

 

[Graham Mandeno]

Hi Dave

It would help if you told us what technique you are using and how it is not
working.

No, there is no technical limitation - this is common practice. There are
two common ways to implement "cascading combo boxes":

1. Base the "child" combo's RowSource on a query which includes a reference
to the "parent" combo in the WHERE clause. Then requery the child combo in
the parent's AfterUpdate event. For example:

cboChild.RowSource: Select RecID, RecText from ChildTable
where ForeignKey=Forms![MyForm]![cboParent];

Private Sub cboParent_AfterUpdate()
cboChild.Requery
End Sub

2. Respecify the RowSource of the child combo in the parent's AfterUpdate
event. For example:

Private Sub cboParent_AfterUpdate()
Dim s as String
If Not IsNull(cboParent) Then
s = "Select RecID, RecText from ChildTable " _
& "where ForeignKey=" & cboParent & ";"
End If
cboChild.RowSource = s
End Sub

[Note that if ForeignKey is a text field then the cboParent value will need
to be enclosed in quotes]

I prefer method 2 because, although it involves more coding, it always
works. Method 1 poses problems if the combo boxes are on a subform, because
the reference to the parent becomes:
Forms![MainFormName]![SubformControlName].Form![cboParent]

This is unwieldy, but workable. However, if the same subform is used on two
or more different main forms, the method is impossible to implement.

If you still can't get it working then please post back saying which method
you are trying to use and how it is failing.

 

[DBH]

Hi Graham!

First thanks for responding! I really appreciate it! I am troubleshooting
in Access to prove the concept and I am using the after update property. I
would then run a macro to refresh the particular combo box but it acted like
it was using the wrong column of the table. For instance, all four of my
tables have a code that I used as the filter and each item in that line of
the table also had a system code that we would use for post analysis for my
application. My problem was that even though I used the filter column for
my filtering, it was using the system code ( a four digit unique number)
instead. I am eventually moving this to a custom outlook form so your code
examples will help tremendously!!!
But, for what it's worth, do you see anything that I am doing that would
cause the problem that I am seeing?
Thanks again!
Dave Hauer

Hi Dave

It would help if you told us what technique you are using and how it is
not working.

No, there is no technical limitation - this is common practice. There are
two common ways to implement "cascading combo boxes":

1. Base the "child" combo's RowSource on a query which includes a
reference to the "parent" combo in the WHERE clause. Then requery the
child combo in the parent's AfterUpdate event. For example:

cboChild.RowSource: Select RecID, RecText from ChildTable
where ForeignKey=Forms![MyForm]![cboParent];

Private Sub cboParent_AfterUpdate()
cboChild.Requery
End Sub

2. Respecify the RowSource of the child combo in the parent's AfterUpdate
event. For example:

Private Sub cboParent_AfterUpdate()
Dim s as String
If Not IsNull(cboParent) Then
s = "Select RecID, RecText from ChildTable " _
& "where ForeignKey=" & cboParent & ";"
End If
cboChild.RowSource = s
End Sub

[Note that if ForeignKey is a text field then the cboParent value will
need to be enclosed in quotes]

I prefer method 2 because, although it involves more coding, it always
works. Method 1 poses problems if the combo boxes are on a subform,
because the reference to the parent becomes:
Forms![MainFormName]![SubformControlName].Form![cboParent]

This is unwieldy, but workable. However, if the same subform is used on
two or more different main forms, the method is impossible to implement.

If you still can't get it working then please post back saying which
method you are trying to use and how it is failing.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database with 4 tables and have a form with 4 combo boxes. I
want to filter the 2nd, 3rd, and 4th based upon information in each table
using a field from each table. I can make it working fine for the first
2 combo boxes but when I try using the same filtering techniques for the
3rd and 4th one, the rules don't seem to work.
Is this some type of technical limitation
Thanks!
Dave Hauer

 

[Graham Mandeno]

Hi Dave

But, for what it's worth, do you see anything that I am doing that would
cause the problem that I am seeing?

Well, the simple answer is "No" because I can't *see* what you are doing.

How about posting some of your code, along with the SQL for the RowSources
of the combo boxes that are not working?

--
Cheers

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham!

First thanks for responding! I really appreciate it! I am
troubleshooting in Access to prove the concept and I am using the after
update property. I would then run a macro to refresh the particular combo
box but it acted like it was using the wrong column of the table. For
instance, all four of my tables have a code that I used as the filter and
each item in that line of the table also had a system code that we would
use for post analysis for my application. My problem was that even
though I used the filter column for my filtering, it was using the system
code ( a four digit unique number) instead. I am eventually moving this
to a custom outlook form so your code examples will help tremendously!!!
But, for what it's worth, do you see anything that I am doing that would
cause the problem that I am seeing?
Thanks again!
Dave Hauer

Hi Dave

It would help if you told us what technique you are using and how it is
not working.

No, there is no technical limitation - this is common practice. There
are two common ways to implement "cascading combo boxes":

1. Base the "child" combo's RowSource on a query which includes a
reference to the "parent" combo in the WHERE clause. Then requery the
child combo in the parent's AfterUpdate event. For example:

cboChild.RowSource: Select RecID, RecText from ChildTable
where ForeignKey=Forms![MyForm]![cboParent];

Private Sub cboParent_AfterUpdate()
cboChild.Requery
End Sub

2. Respecify the RowSource of the child combo in the parent's AfterUpdate
event. For example:

Private Sub cboParent_AfterUpdate()
Dim s as String
If Not IsNull(cboParent) Then
s = "Select RecID, RecText from ChildTable " _
& "where ForeignKey=" & cboParent & ";"
End If
cboChild.RowSource = s
End Sub

[Note that if ForeignKey is a text field then the cboParent value will
need to be enclosed in quotes]

I prefer method 2 because, although it involves more coding, it always
works. Method 1 poses problems if the combo boxes are on a subform,
because the reference to the parent becomes:
Forms![MainFormName]![SubformControlName].Form![cboParent]

This is unwieldy, but workable. However, if the same subform is used on
two or more different main forms, the method is impossible to implement.

If you still can't get it working then please post back saying which
method you are trying to use and how it is failing.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a database with 4 tables and have a form with 4 combo boxes. I
want to filter the 2nd, 3rd, and 4th based upon information in each table
using a field from each table. I can make it working fine for the first
2 combo boxes but when I try using the same filtering techniques for the
3rd and 4th one, the rules don't seem to work.
Is this some type of technical limitation
Thanks!
Dave Hauer