Full Version: Filter a form using SQL ??
UtterAccess Forums > Microsoft® Access > Access Forms
ronsue
Can a form be filtered by applying the “results” of a SQL to a String Variable ie- strFilterList and then
use strFilterList as criteria for the filter? ------- as attempted below.
need the form to be filtered to match the items in the list box [lstContactName]
Following is the code used to fill the list box on the form from the combo box “cboOptionLists”:
Private Sub cboOptionLists_Click()
On Error GoTo HandleErr
Dim strFilterList As String
strFilterList = ""
'Fill List "lstContactName" with contact names belonging to the list selected

Forms![frmContacts]![lstContactName].RowSource = _
"SELECT [tblContacts].[ContactID], [tblContacts].[FirstName] & " " & [tblContacts].[LastName] AS [Contact Name] FROM qryListandNames" _
& " WHERE (tblLists.ListSubTypeID)= " & Me.cboOptionLists.Column(1) _
& " ORDER BY tblContacts.[LastName], tblContacts.[FirstName];"
‘Filter the form to match the names in the list “lstContactName”
strFilterList = _
"Select [tblContacts].[ContactID] FROM qryListandNames" _
& " WHERE (tblLists.ListSubTypeID)= " & Me.cboOptionLists.Column(1)

DoCmd.ApplyFilter "", "[ContactID] Like ""[strFilterList]"""

Me.Requery
ExitHere:
Exit Sub
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, , _
"Form_frmContacts.CmdOK_Click"
End Select
Resume ExitHere

End Sub
NorthNone
I think you need to add a FilterOn statement. Here's a sample from an application I'm working on right now:
Me.Filter = strFilterList
Me.FilterOn = True
Me.Form.Requery
Jack Cowley
An easier way would be to add a combo box to the form using the Wizard. On the Wizards first screen select the 3rd item, "Find a record...". Finish the Wizard. Now the user can start to type in the name of the Contact that they want and when they exit the combo box that record will be displayed on the form. The form must be bound to the query "qryListandNames".
th,
Jack
ronsue
Jack
Thank you for the help.
Using your first suggestion seems the most appropriate way for me to go but now I get the error message:
2448: You can't assign a value to this object.
Just have to kept working on it I guess
Thanks again
Ron
Jack Cowley
Ron -
don't understand why you are getting that error as the code created by the Wizard does not assign a value to an object. If you just create the combo box, using the Wizard, that is all that you should need to do. Be sure that you haven't added any additional code as it is not needed.
hth,
Jack
PS. If you want me to know that you have responded then do not use the Quick Reply, but click on one of my posts in this thread and click on the Reply to the right of my avatar. Put your post in that reply box, at the bottom of the thread, and I will be notified of your post.
kapeller
Hi!!!!!!!!!
Have a look at his Link This sample has a SQL statment thats of the type you may be looking for.
Cheers!!!!!!
Happy New year thumbup.gif o!
Lou
ronsue
Thank you for your reply Lou
Helped but was not a solution to my problem.
got my form to filter the first item in the filter string but if there is more than one item in the string it still filters only the first. MUST BE A SYNTAX ERROR NOW ??
I had to loop throught the list box “lstContactName” an apply results to string mFilterList – then trim the beginning and ending of the string to create a string that I could apply to the filter.
Below is updated code that almost works.
Private Sub cboOptionLists_Click()
On Error GoTo HandleErr
Dim mFilterList
Dim I As Integer
mFilterList = ""
'Fill List "lstContactName" with contact names belonging to the list selected

'Populate lstContactName list box

Forms![frmContacts]![lstContactName].RowSource = _
"SELECT [tblContacts].[ContactID], [tblContacts].[FirstName] & ' ' & [tblContacts].[LastName] AS [Contact Name] FROM qryListandNames" _
& " WHERE (tblLists.ListSubTypeID)= " & Me.cboOptionLists.Column(1) _
& " ORDER BY tblContacts.[LastName], tblContacts.[FirstName];"

'Create the filter string by looping thru the listbox
For I = 0 To Me.lstContactName.ListCount - 1
mFilterList = mFilterList & Me.lstContactName.Column(0, I) & " AND "

Next I

'Remove from the end
mFilterList = Left(mFilterList, Len(mFilterList) - 5)

'Remove from beginning
mFilterList = Right(mFilterList, Len(mFilterList) - 14)

mFilterList = "ContactID= " & mFilterList
‘Apply the filter
Me.Filter = mFilterList
Me.FilterOn = True
Me.Form.Requery

Me.Requery
ExitHere:
Exit Sub
HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, , _
"Form_frmContacts.CmdOK_Click"
End Select
Resume ExitHere

End Sub
Thank you
Ron
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.