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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JASPNZ" <JASPNZ@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:78060F31-D7D9-4411-B7C7-8559B065A83C@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Apply filter to sub form when main form is filtered
    ... I have a form with customer information called "customer", it has two sub ... forms with there transactions that is filtered by default to remove inactive ... filter command on the bottom of the sub form is disabled. ...
    (microsoft.public.access.formscoding)
  • Apply filter to sub form when main form is filtered
    ... I have a form with customer information called "customer", it has two sub ... forms with there transactions that is filtered by default to remove inactive ... filter command on the bottom of the sub form is disabled. ...
    (microsoft.public.access.gettingstarted)
  • Re: Select only visible tasks
    ... Create a new field in your plan which contains a unique identifier - I have ... applicable task, the name of the sub project, or the sub team within my ... code I used to filter for one of several projects - you can see the filtering ... > of the filtered set is a summary line, all the subtasks of that summary ...
    (microsoft.public.project)
  • EXAMPLE: fill menu from path
    ... I wanted the ability to store any given filter to a user file, and be able to display it in a user organized menu system. ... Dim testPathsAs String ... Sub initForm() ...
    (microsoft.public.dotnet.languages.vb)
  • Help with filling listbox from a function in another class
    ... I have a form that is supose to display customer names in a listbox and then ... Private address As String ... Public Shared Sub Initialize() ... Dim adpcustomer As New OleDbDataAdapter ...
    (microsoft.public.dotnet.framework.windowsforms.databinding)