• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Access 2007 - Searching with a form

Hi experts,

I'm trying to create a search form that has several drop-down boxes which I'd like to serve as the criteria for a query. Once the user clicks "search" on the form, the query opens up with the records that match those criteria. My problem is that if the criteria section of the query says something like Forms![My search form].cbo1, if cbo1 isn't filled in on the form, the query will return nothing. Your thoughts?

Thanks...
0
JC_Lives
Asked:
JC_Lives
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:

create a sub like this in the form's module, something like this

Sub ApplyFilter()
  Dim strFilter As String

    If Len(Me.[ComboBoard] & vbNullString) > 0 Then
        strFilter = "SumBoard=" & Me.[ComboBoard] & ""
    End If

    If Len(Me.[ComboFinance] & vbNullString) > 0 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "SumFinance=" & Me.[ComboFinance] & ""
    End If
   
    If Len(Me.[ComboHR] & vbNullString) > 0 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "SumHR=" & Me.[ComboHR] & ""
    End If

    If Len(Me.[ComboRandD] & vbNullString) > 0 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "SumRD=" & Me.[ComboRandD] & ""
    End If

    If Len(Me.[ComboRegulatory] & vbNullString) > 0 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "SumReg=" & Me.[ComboRegulatory] & ""
    End If

    If Len(Me.[ComboSales] & vbNullString) > 0 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "SumSales=" & Me.[ComboSales] & ""
    End If


Me.filter= strFilter
me.filteron=true
End sub

Open in new window



call the sub in the afterUpdate event of the combo boxes, like this

private sub ComboBoard_afterupdate()
applyFilter

end sub


0
 
JC_LivesAuthor Commented:
THanks!!!
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Sure, Try this criteria:

Like Forms![My search form].cbo1 & "*"

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Sorry about the late post.  When I viewed the post I did not see an replies.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now