Search and filter a form

[erroldou]

Hi,


I have a combobox on form (frmSearchRecord) to select a name. I select a name and click the 'search' button which opens a new form (frmMembers) which is filtered to the record(s) with that name. Using the access wizard, one obtains the following code:

----------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMembers"

stLinkCriteria = "[Surname]=" & "'" & Me![Combo3] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSearchRecord"
----------------

I've now added a second combo box to my search form, so that I can choose from another list (cities). When I click 'search', I'd like the (frmMembers) form which opens to be filtered by both the surname and city that I selected. How can I change the above code to make this work. I've tried various tactics, but nothing works as yet.

Thanks, Errol.

 

[Ian Mac]

You could filter the second part:

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stFilter As String
    
    
    stDocName = "Form1"
    stLinkCriteria = "[Surname]= '" & Me![Combo3] & "'"
    stFilter = "[City] = '" & Me![Combo4] & "'"
    
    DoCmd.OpenForm stDocName, , stFilter, stLinkCriteria

What may be even easier is designing a Query with Criteria built in

i.e.

Forms![frm_MainForm]![Combo3] for the Surname
Forms![frm_MainForm]![Combo4] for the City

Then base you second form on this Query.

Hope this helps,

 

[Newman]

The best way is by using this procedure in a module:

Public Sub AttacherTexte(ByRef TexteInitial As String, ByVal NouveauTexte As String, ByVal Separateur As String)
    If TexteInitial = "" Then
        TexteInitial = NouveauTexte 
    Else
        TexteInitial = TexteInitial & Separateur & NouveauTexte 
    End If
End Sub

And calling it from your Search button procedure using these lines:

If not isnull(Combo3) then AttacherTexte stLinkCriteria, "[Surname]=" & "'" & Me![Combo3] & "'", " and "
If not isnull(Combo4) then AttacherTexte stLinkCriteria, "[City]=" & "'" & Me![Combo4] & "'", " and "

This way, you will only have to add a line for every other combobox you may want to add in the future.

 

[erroldou]

Thanks

Thanks for the help - It looks like I got it working ok now.

Cheers,
Errol