Using VBA to filter a field by first letter?
I'm working on a small inventory database that lists all of the software at my company. I am attempting to make a form that allows a user to search for a software package by first letter of the application name.
I've tried to write some VBA code to do this, but I am new to A2k and VBA so I am sure I am missing something since I cannot get this to work. I always get an error stating that I cannot assign a value to the Me.Filter action. The following is the code I was trying to use:
Begin Code
--------------------------
Private Sub Sort_By_First_Letter_Of_Pr
On Error GoTo Err_Sort_By_First_Letter_O
MsgBox "Hello World"
Dim strLetter As String, strInput As String
strInput = "Please type in the first letter of the program name: "
strLetter = InputBox(Prompt:=strInput)
Me.Filter = "ProgramName = LIKE M*"
Me.FilterOn = True
Exit_Sort_By_First_Letter_
Exit Sub
Err_Sort_By_First_Letter_O
MsgBox Err.Description
Resume Exit_Sort_By_First_Letter_
End Sub
--------------------------
End Code
I am beginning to think I may need to use ADO or DAO to do this, but I am unfamiliar with how to use either one of them. Any pointers or hints would be greatly appreciated.
I've tried to write some VBA code to do this, but I am new to A2k and VBA so I am sure I am missing something since I cannot get this to work. I always get an error stating that I cannot assign a value to the Me.Filter action. The following is the code I was trying to use:
Begin Code
--------------------------
Private Sub Sort_By_First_Letter_Of_Pr
On Error GoTo Err_Sort_By_First_Letter_O
MsgBox "Hello World"
Dim strLetter As String, strInput As String
strInput = "Please type in the first letter of the program name: "
strLetter = InputBox(Prompt:=strInput)
Me.Filter = "ProgramName = LIKE M*"
Me.FilterOn = True
Exit_Sort_By_First_Letter_
Exit Sub
Err_Sort_By_First_Letter_O
MsgBox Err.Description
Resume Exit_Sort_By_First_Letter_
End Sub
--------------------------
End Code
I am beginning to think I may need to use ADO or DAO to do this, but I am unfamiliar with how to use either one of them. Any pointers or hints would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried the code you posted nexusnation, and now I am getting a Type Mismatch error.
At first I thought it might just be a missing quotation mark or the use of strInput in the Me.Filter line instead of strLetter, but I get the same error regardless of which variable I use.
At first I thought it might just be a missing quotation mark or the use of strInput in the Me.Filter line instead of strLetter, but I get the same error regardless of which variable I use.
ASKER
I tried the code you posted nexusnation, and now I am getting a Type Mismatch error.
At first I thought it might just be a missing quotation mark or the use of strInput in the Me.Filter line instead of strLetter, but I get the same error regardless of which variable I use.
At first I thought it might just be a missing quotation mark or the use of strInput in the Me.Filter line instead of strLetter, but I get the same error regardless of which variable I use.
ASKER
Arthur Wood,
Your line does work, however I had to use strLetter instead of strInput as the variable.
Thanks for the fast response nexusnation and Arthur. I'll award points here shortly.
Your line does work, however I had to use strLetter instead of strInput as the variable.
Thanks for the fast response nexusnation and Arthur. I'll award points here shortly.
ASKER
Fast answer, minor issues with line format and incorrect variable use but other than that right on.
Me.Filter = "[ProgramName] = "LIKE" & strinput & "*"
will generate an error.
Change it to:
Me.Filter = "[ProgramName] LIKE '" & strinput & "*'"
1) notice the space after the work LIKE, and
2) the strInput needs to be enclosed in '....' s
AW