Creating a filter using VBA

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hello

I'm trying to filter data in a form (It does not have a Subform) based on
what what user types in two unbound text boxs. I am able to do this based of
one but I want to filter for 2, so that where my problem lies.

Here is what I been playing around with, but it does not work. It will
filter for the first textbox then clear the filter and filter for the next
textbox.

Private Sub Command27_Click()
If IsNull(Me.FilterLName) Then
MsgBox "Please Enter Student's Last Name"
FilterLName.SetFocus

Else
Me.Filter = "Lname = """ & Me.FilterLName & """"
Me.FilterOn = True
Me.Filter = "Fname = """ & Me.FilterLName & """"
Me.FilterOn = True
End If

End Sub

Any help would be great, thank you
 
David wrote:


(...)
| Else
| Me.Filter = "Lname = """ & Me.FilterLName & """ AND Fname = """
& Me.FilterLName & """"
| Me.FilterOn = True
| End If
 
With the amount of data(16000 records) I think it will be easier to filter
using textboxes. Is there way to use an & to combine these two lines of
code. I am really bad at figuring out how to use the &.

Thanks
 
David said:
I'm trying to filter data in a form (It does not have a Subform) based on
what what user types in two unbound text boxs. I am able to do this based of
one but I want to filter for 2, so that where my problem lies.

Here is what I been playing around with, but it does not work. It will
filter for the first textbox then clear the filter and filter for the next
textbox.

Private Sub Command27_Click()
If IsNull(Me.FilterLName) Then
MsgBox "Please Enter Student's Last Name"
FilterLName.SetFocus

Else
Me.Filter = "Lname = """ & Me.FilterLName & """"
Me.FilterOn = True
Me.Filter = "Fname = """ & Me.FilterLName & """"
Me.FilterOn = True
End If
Click to expand...


Not sure what you are doing with FName, but try something
like:

Private Sub Command27_Click()
Dim strFltr As String
If IsNull(Me.FilterLName) Then
MsgBox "Please Enter Student's Last Name"
FilterLName.SetFocus
Exit Sub
Else
strFltr = strFltr & " AND Lname = """ & Me.FilterLName &
""""
End If

If IsNull(Me.FilterFName) Then
MsgBox "Please Enter Student's First Name"
FilterFName.SetFocus
Exit Sub
Else
strFltr = strFltr & " AND Fname = """ & Me.FilterFName &
""""
End If

Me.Filter = Mid(strFltr, 6)
Me.FilterOn = True

End Sub
 
Back
Top