Multilpe filters

Experts,
in my form frmAdjustmentsToBonus from my table tblAdjustmentsToBonus I have the following fields that I would like to select using a combo box to serve as a filter:
EmployeeID
Month
Year

Now that I've identifed my criteria, I need a command button Locate Adjustment.
My issue here is the code needed in the Locate Adjustment command button.

I recognize that there could be multiple adjustments, however, I would prefer to open another question explaining what is needed.
Frank FreeseAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:

test this revised db

* revised the rowsource of cboEmployee
* revised the rowsource of cboMonth, applied filter based on selection from cboEmployee
StoreBonusRev.mdb
0
 
Rey Obrero (Capricorn1)Commented:

something like this

private sub cmdLocateAdjustment_click()

    Dim sFilter As String
    sFilter = ""
   
    If Me.cboEmployeeID <> "" And Not IsNull(Me.cboEmployeeID) Then
              If sFilter = "" Then
                        sFilter = "[EmployeeID]  ="  & Me!cboEmployeeID & ""
              Else
                        sFilter = sFilter & " and [EmployeeID] = " & Me!cboEmployeeID & ""
              End If
    End If
   
    If Me.cboMonth <> "" And Not IsNull(Me.cboMonth) Then
              If sFilter = "" Then
                        sFilter = "[Month] ='" & Me!cboMonth & "'"
              Else
                        sFilter = sFilter & " And [Month] ='" & Me!cboMonth & "'"
               End If
    End If

    If Me.cboYear <> "" And Not IsNull(Me.cboYear) Then
              If sFilter = "" Then
                        sFilter = "[Year] =" & Me!cboYear & ""
              Else
                        sFilter = sFilter & " And [Year] =" & Me!cboYear & ""
              End If
    End If
   
 
    If Len(sFilter) > 0 Then    
          Me.Filter = sFilter
          Me.FilterOn = True  
    Else
           Me.Filter = ""
           Me.FilterOn = False
    End If
End Sub
0
 
Frank FreeseAuthor Commented:
I've copied the code and need to make some changes in my form and study what you did. Guess I got homework. Will let you know. Thanks
0
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.

 
Frank FreeseAuthor Commented:
Cap,
That did not work at all. I've attached the db and was wondering if it would be better to simple use another form for my filtering? The table I'm referencing is tblAdjustmentToBonus and the form is frmAdjustmentToBonus. My iniital objective was to use this form to enter bonus adjustments as well as locate previous adjustments if editing was needed.
After looking at this over the weekend I questioned the need for a new for to Locate Adjustments To Bonuses and from that form do my filtering leaving the frmAdjustmentToBonus serving only as inital data entry.
I know you've spent time on this and I would feel bad in not awarding you the 500 points. Please advise.

StoreBonus.mdb
0
 
Frank FreeseAuthor Commented:
Fantastic - you nailed it, as always.
0
 
Frank FreeseAuthor Commented:
thank you
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.

All Courses

From novice to tech pro — start learning today.