Showing records in a Sub form based on TWO combo boxes on Main For

[JASPNZ]

Here is my problem……..



I have 3 tables.



1. Customers (i.e. Air New Zealand, Westpac etc)

2. Os Type (i.e AIX, Solaris)

3. Servers (This table holds the customer number from (1) and
the Os Type Number from (2) (i.e Os Type Number 1 = AIX, 2 = Solaris etc.

What I want is a form/Sub form where the records shown in the sub from are
filtered by whatever I select from two Combo Boxes in the main form. So for
example, in Combo 1 I might select a customer of “Air New Zealand†and in
Combo 2 an Os Type of “AIXâ€.

So, without stating the obvious, what I want to see in the sub form results
is all servers for Air New Zealand which are AIX.

I initially created my form and subform with just Combo 1 which just filters
the subform to show all the servers for Air New Zealand regardless of Os
Type. This works fine. When I change the customer in Combo 1 the results in
the subform change accordingly to only show the records relevant to that
customer.

I then thought that if I added another Combo box (i.e (2)) to the main form
which was for the Os Type, I could then further filter the form to only show
those records for whatever I select in both Combo’s 1 and 2. i.e All Servers
for Air New Zealand which are AIX.

The first Combo ie. Customer works but nothing happens when I select Combo
2. And I know I have two records in the Table Servers where the customer for
both is Air New Zealand but the Os Type is AIX for one and Solaris for the
other.

I should mention that I use Auto Numbers for Customer Number and Os Type not
Text. A separate field in each table holds a separate description of what the
auto number means i.e Autonumber 1 = Air New Zealand. I have created my
Relationships between the tables i.e One to Many for Customers >> Servers and
Os Type >> Servers.

I thought the problem would be that I need to define the “Linkchildfieldsâ€
and LinkMasterfields. But for some reason when I try to add another linking
field (i.e Combo 2), I cannot see the fields. I only am able to see the
fields from Combo 1.

I hope all the above makes sense.

I would really appreciate any suggestions to get this working. Many thanks

 

[Allen Browne]

As you found the LinkMasterFields/LinkChildFields won't cut it. If you leave
a combo blank you get no records in the subform.

Just use a form in Continuous View, rather than a form and subform. Use the
AfterUpdate event procedure of the 2 combos to filter the form. You build
the filter string from whichever combo(s) are not blank.

The code below illustrates how this could be done. It is designed to make it
easy to add more filter boxes if you wish.

Private Sub cboFilterCust_AfterUpdate
Dim strWhere As String
Dim lngLen As Long

'Save any edits
If Me.Dirty Then Me.Dirty = False

'Build the filter string from the non-blank combos
If Not IsNull(Me.cboFilterCust) Then
strWhere = strWhere &"(CustomerID = " & Me.cboFilterCust & ") AND "
End If
If Not IsNull(Me.cboFilterOS) Then
strWhere = strWhere &"(OSID = " & Me.cboFilterOS & ") AND "
End If

'Chop off the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
Me.Filter = Left(strWhere lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

Private Sub cboFilterOS_AfterUpdate
call cboFilterCust_AfterUpdate
End Sub

For a more comprehensive example that you can download and pull apart, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[JASPNZ]

Hi Allen,

Thanks very much for getting back on this so quickly.

I have done what you have suggested but I keep getting an error when I click
on my first Combo which is where I select the Customer. The Combo displays
say "Air New Zealand" which equates to field name "Customer Number" which in
this case = "1". It is this value that i want to firstly filter my form on
(from the table servers upon which the form is based)

When I select "Air New Zealand" I immediately getting a Runtime Error 2448,
which when I go into Debug, highlights in yellow the line :-

"Me.Filter = Left(strWhere, lngLen)

You may have spotted that between the brackets and between strWhere and
lngLen is a comma. This was not in your code below, but in any event, I get a
Debug error regardless of whether or not the comma is there.

I am pasting in below my full code just in case it helps. (I used exactly
the names that you used for the two combo's ie. cboFilterCust and
cboFilterOS).

Private Sub cboFilterCust_AfterUpdate()
Dim strWhere As String
Dim lngLen As Long

'Save any edits
If Me.Dirty Then Me.Dirty = False

'Build the filter string from the non-blank combos
If Not IsNull(Me.cboFilterCust) Then
strWhere = strWhere & "(Customer Number = " & Me.cboFilterCust & ") AND "
End If
If Not IsNull(Me.cboFilterOS) Then
strWhere = strWhere & "(Os Type Number = " & Me.cboFilterOS & ") AND "
End If

'Chop off the trailing " AND "
lngLen = Len(strWhere) - 5

Debug.Print strWhere

If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen) ' This is the line causing the problem I
refer to above.
Me.FilterOn = True
Else
Me.FilterOn = False
End If

End Sub

I am really grateful for your help and will try not to take up too much of
your time.

Best wishes

Paul

 

[Allen Browne]

It sounds like the filter string is not correctly formed.
To test this, enter this line just above the one that fails:
Debug.Print Left(strWhere, lngLen)
Does this look right?

I am assuming that this is a bound form (i.e. it actually gives the
results.) If it is not a bound form (e.g. if a subform gives the results),
it won't accept a filter.

 

[JASPNZ]

Hi Allen,

After much head scratching and a Google, I discovered there is an issue if a
Field name has a space in it. So (Customer Number) has to be entered as
([Customer Number]). After that your code worked a treat. Just thought I
would let you know. Thanks again for your fast and helpful work. Very much
appreciated.

Best wishes

Paul