Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

subform multiple combo filter

Status
Not open for further replies.
ali32j

ali32j

Technical User
Apr 23, 2007
97
GB
Hi All


I have been trying to use the following code to filter a form based on multiple combo boxes, problem i m having is that it keeps noting that "No Criteria", which is wrong it certainly does have crteria it should filter to....

Private Sub Combo34_AfterUpdate()

Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'Save first

If Not IsNull(Me.Combo34) Then
strWhere = strWhere & "([Manufacturer] = '" & Me.Combo34 & "') AND "
End If

If Not IsNull(Me.Combo36) Then
strWhere = strWhere & "([SSM] = '" & Me.Combo36 & "') AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lenLen > 0 Then
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "No criteria."
End If
End Sub

Can anyone tell me what problem is, this is a great bit of code which would be wonderfull if i could figure this out...

ALi
 
Sort by date Sort by votes
Hmmm
Code: 
lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lenLen ...
should probably be:
Code: 
lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If [b][COLOR=red]lngLen[/color][/b] ...

Use Option Explicit to root out these kinds of problems.



Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill
 
Upvote 0 Downvote
Still getting the same response i m afraid....
 
Upvote 0 Downvote
How are ya ali32j . . .

Put a question mark [blue]?[/blue] (no quotations please) in the [blue]Tag[/blue] property of the two comboboxes and try the following:
Code: 
[blue]   Dim ctl As Control, Fltr As String, fld As String
   
   If Me.Dirty Then Me.Dirty = False [green]'Save first[/green]
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         fld = Switch(ctl.Name = "Combo34", "[Manufacturer] = '", _
                      ctl.Name = "Combo36", "[SSM] = '")
         If Fltr <> "" Then
            Fltr = " AND (" & fld & Me(ctl.Name) & "')"
         Else
            Fltr = "(" & fld & Me(ctl.Name) & "')"
         End If
      End If
   Next
   
   If Fltr <> "" Then
      Me.Filter = Fltr
      Me.FilterOn = True
   Else
      MsgBox "No Criteria! . . ."
   End If[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Hi Aceman

I am good thanks hope your are well to

Tried your approach and i am getting the following errors

Run-time error '3075'

Syntax error (missing operator) in query expression @ AND ([SSM] = ")'

upon entering debug its high lighting "Me.Filter = Fltr"

any ideas whats going wrong??

Ali
 
Upvote 0 Downvote
ok if i take the tag property "?" out of combo36 it filters to manufacturer fine but nothing happens when i select from the second combo36...
 
Upvote 0 Downvote
ali32j . . .

Post the codeas you have it! . . .

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Hi Aceman

code as follows:

Private Sub Combo34_AfterUpdate()

Dim ctl As Control, Fltr As String, fld As String

If Me.Dirty Then Me.Dirty = False 'Save first

For Each ctl In Me.Controls
If ctl.Tag = "?" Then
fld = Switch(ctl.Name = "Combo34", "[Manufacturer] = '", _
ctl.Name = "Combo36", "[SSM] = '")
If Fltr <> "" Then
Fltr = " AND (" & fld & Me(ctl.Name) & "')"
Else
Fltr = "(" & fld & Me(ctl.Name) & "')"
End If
End If
Next

If Fltr <> "" Then
Me.Filter = Fltr
Me.FilterOn = True
Else
MsgBox "No Criteria! . . ."
End If

End Sub
 
Upvote 0 Downvote
ali32j . . .

I didn't check for empty combo's. Try the following and if it fails post back whats printed in the immediate window by the debug.print line . . .


Code: 
[blue]   Dim ctl As Control, Fltr As String
   Dim Nam As Floor, fld As String
   
   If Me.Dirty Then Me.Dirty = False 'Save first
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         Nam = ctl.Name
         
         If Trim(Me(Nam) & "") <> "" Then
            fld = Switch(Nam = "Combo34", "[Manufacturer] = '", _
                         Nam = "Combo36", "[SSM] = '")
            If Fltr <> "" Then
               Fltr = " AND (" & fld & Me(Nam) & "')"
            Else
               Fltr = "(" & fld & Me(Nam) & "')"
            End If
         End If
      End If
   Next
   
   If Fltr <> "" Then
      [purple][b]Debug.Print Fltr[/b][/purple]
      Me.Filter = Fltr
      Me.FilterOn = True
   Else
      MsgBox "No Criteria! . . ."
   End If[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Hi Aceman

The following is displayed

Compile error:

User-defined type not defined


Hope this helps

Ali
 
Upvote 0 Downvote
ali32j . . .

Sorry about the error.
Code: 
[blue][tt]Change: Dim Nam As Floor
To:     Dim Nam As [purple][b]String[/b][/purple][/tt][/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Hi Aceman

OK so combo34 (manufacturer) works fine if combo36 is empty, if theres a value present in combo 36 it does not work, likewise after selecting from combo36, i get the following:

Runtime 3075
Syntax Error (missing operator) in query 'AND ([ssm] = '100')'

seems to be ok if either oneof the 2 combos is blank but when both have values there is an issue...


Ali
 
Upvote 0 Downvote
ali32j . . .

I don't believe I didn't see the error.
Code: 
[blue][b]Change:[/b]
   Fltr = " AND (" & fld & Me(Nam) & "')"
[b]To:[/b]
   Fltr = [purple][b]Fltr &[/b][/purple] " AND (" & fld & Me(Nam) & "')"[/blue]
Sorry about the mess! . . .



Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Hi Aceman

Thats wonderful, works like a dream, i have posted the full code with the addition of multiple combo's, i ve looked for solution to this for ages, so thought would post for everyones else benefit who maybe looking to do similar:

SO place below code on the first combo afterupdate, in my case combo34 -

Private Sub Combo34_AfterUpdate()

Dim ctl As Control, Fltr As String
Dim Nam As String, fld As String

If Me.Dirty Then Me.Dirty = False 'Save first

For Each ctl In Me.Controls
If ctl.Tag = "?" Then
Nam = ctl.Name

If Trim(Me(Nam) & "") <> "" Then
fld = Switch(Nam = "Combo34", "[Manufacturer] = '", _
Nam = "Combo36", "[SSM] = '", _
Nam = "Combo38", "[Size] = '", _
Nam = "Combo40", "[Value] = '", _
Nam = "Combo42", "[Volts] = '", _
Nam = "Combo44", "[Dielectric] = '", _
Nam = "Combo46", "[Tolerance] = '", _
Nam = "Combo48", "[MPNINMKT] = '", _
Nam = "Combo50", "[Num] = '", _
Nam = "Combo52", "[Cap] = '", _
Nam = "Combo54", "[V] = '", _
Nam = "Combo46", "[Size] = '")
If Fltr <> "" Then
Fltr = Fltr & " AND (" & fld & Me(Nam) & "')"
Else
Fltr = "(" & fld & Me(Nam) & "')"
End If
End If
End If
Next

If Fltr <> "" Then
Debug.Print Fltr
Me.Filter = Fltr
Me.FilterOn = True
Else
MsgBox "No Criteria! . . ."
End If
End Sub

All subsequent combo, simply place below code on afterupdate event -

Call Combo34_AfterUpdate

This code is valid for text format only, you will need to adapt to accomodate any number formats

Hope this helps anyone looking to do this, with thanks to ACEMAN for his great work on this

Thanks again aceman

Ali
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
709
Kennelbloke
Kennelbloke
JanaLNelson
  • Locked
  • Question
Filter as you type combo box in Access 2
2 3
Replies
54
Views
2K
JanaLNelson
JanaLNelson
monkeysee
  • Locked
  • Question
Combo Box - Limit results
Replies
1
Views
410
dhookom
dhookom
Bubba002
  • Locked
  • Question
Pass a subform fieldname-ID as a string variable in CF expression
2 3
Replies
50
Views
2K
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Use Crosstab query in subform
Replies
9
Views
502
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top