Filter through VBA question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a drop down box that lets a user choose from a list of classes. Once
the user selects from that list I want the Details section that lists all of
the test taken in that class to filter by whatever class was chosen from the
drop down.

When I use the following code the user is prompted for the value of
Me.Combo22 instead of it reading what the value stored in the Combo22 combo
drop down box is.

Private Sub Combo22_AfterUpdate()

Me.Filter = "ClassID = Me.Combo22"

Me.FilterOn = True

End Sub

i've tried entering in Combo22 a number of different ways but it never
works. i even tried using a dim statment and setting a seperate variable
equal to the value of combo22 and then using that variable as part of the
filter statment.

Any help or suggestions would be appreciated.
 
If ClassID is numeric, use:

Me.Filter = "ClassID = " & Me.Combo22

If it's text, use:

Me.Filter = "ClassID = " & Chr$(34) & Me.Combo22 & Chr$(34)

or

Me.Filter = "ClassID = '" & Me.Combo22 & "'"

(exagerated for clarity, that last one is Me.Filter = "ClassID = ' " &
Me.Combo22 & " ' ")

Note that the last suggestion won't work if there are apostrophes in what's
coming from Combo22.
 
It worked perfect using the code:

Me.Filter = "ClassID = " & Chr$(34) & Me.Combo22 & Chr$(34)

Thanks for the help Douglas.
 
ok Now I have a new question. How can I perform multiple filters at once. I
set up this code but it isn't working. can you tell me if you see any glaring
errors with the code?

Private Sub Combo24_AfterUpdate()

If Me.ClassID <> "" Then
Me.Filter = "ClassID = " & Chr$(34) & Me.Combo22 & Chr$(34) And
"TestName = " & Chr$(34) & Me.Combo24 & Chr$(34)
Else
Me.Filter = "TestName = " & Chr$(34) & Me.Combo24 & Chr$(34)
End If
Me.FilterOn = True

End Sub

Thanks for you help
 
If you want to test for a zero-length string and for null change your If
statement. One way to do that is shown below.

Second, you need to include the AND conjunction inside the quote marks instead
of outside.


IF Len(Me.ClassID & "") > 0 Then
Me.Filter = "ClassID = " & Chr$(34) & Me.Combo22 & Chr$(34) & _
" And TestName = " & Chr$(34) & Me.Combo24 & Chr$(34)
Else
Me.Filter = "TestName = " & Chr$(34) & Me.Combo24 & Chr$(34)
End If
Me.FilterOn = True
 
Back
Top