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,

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

"Allen Browne" wrote:

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.

--
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:F444E3CF-09EB-4AA3-8E51-0947E1677F52@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Searching
    ... It would be best to build a filter string for the form ... dim mFilter as string ... mfilter is a string that is being built for each condition -- but if nothing is specified in the filter control, then that addition to the filter string is skipped. ... Private Sub srcAddress_Change ...
    (microsoft.public.access.queries)
  • Re: requery as each character entered
    ... Create a function that returns the filter string and call that function from ... Private SUB CID_Change ... I could make it use a combination of the filters to filter the records. ... subform. ...
    (microsoft.public.access.forms)
  • Re: Command Buttons
    ... Error 2001 indicates that the filter string is not correct. ... >> Private Sub cmdFilter_Click ... >> Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.forms)
  • Re: Get rid of filter string
    ... > Private Sub Form_Open ... > This doesn't work because the old filter string magically stays there... ... > Pedro Bezunartea. ...
    (microsoft.public.access.forms)
  • Re: Parameter
    ... filter on. ... Private Sub cmdCustomer1_Click ... Is Customer a text field? ... Dirk Goldgar, MS Access MVP ...
    (microsoft.public.access.formscoding)