Filter subform with multiple combo boxes and date ranges.

[Stephenson]

Hello all,

I'm new to the access game and loving learning it. I have however hit a bit of a wall in my DB. I've got a form named TimeCard that holds a subform named TimeCardSub. I'm trying to use this sub for two purposes. One for the data entry person to see what they're entering and the second to be able to search for Records.

My issue is with the ability to create a search for the records. I have the combo boxes; cboSearchEmployee, cboSearchJob, and cboSearchService that I would like to use to filter down the subform, then be able to click on a certain row, bring it up to the form and edit it if needed. So far here is the code I'm using.

Private Sub cboSearchEmployee_AfterUpdate()
     If IsNull(Me.cboSearchEmployee) Then
   Me.cldTimeCard.Form.Filter = ""
   Me.cldTimeCard.Form.FilterOn = False
Else
  Me.cldTimeCard.Form.Filter = "[EmployeeID]=" & Me.cboSearchEmployee
  Me.cldTimeCard.Form.FilterOn = True
End If
   Exit Sub
End Sub


Private Sub cboSearchJob_AfterUpdate()
    If IsNull(Me.cboSearchJob) Then
   Me.cldTimeCard.Form.Filter = ""
Me.cldTimeCard.Form.FilterOn = False
Else
  Me.cldTimeCard.Form.Filter = "[JobID]=" & Me.cboSearchJob
  Me.cldTimeCard.Form.FilterOn = True
End If
   Exit Sub
End Sub


Private Sub cboSearchService_AfterUpdate()
    If IsNull(Me.cboSearchService) Then
   Me.cldTimeCard.Form.Filter = ""
   Me.cldTimeCard.Form.FilterOn = False
Else
  Me.cldTimeCard.Form.Filter = "[ServiceID]=" & Me.cboSearchService
  Me.cldTimeCard.Form.FilterOn = True
End If
   Exit Sub
End Sub

The problem is that it will not filter down off of all the combo boxes together. I need a way to code this so that all the filters work together. Is there a way to change my code or a better way all together to get a search such as this to work? FYI I'm dumb when it comes to queries.

On top of this I would like to add the ability to search for a date range. Say between two dates or just on one date. I have two text boxes; tboStartDate and tboEndDate. So far I'm completely unclear on how to add this functionality. I would love suggestions on how to incorporate this.

As far as being able to double click on the row like you can on a split form I've only come up with placing an on double click command for each column. like...

Private Sub cboJob_DblClick(Cancel As Integer)
DoCmd.OpenForm "Try", , , "[JobID] = " & Me!JobID
End Sub

Aside form having to enter this for each column, when this is used it will only bring the uppermost record into the form. I assume there is a better way, I'm just unclear what it may be. Please let me know if there is a better way to get my for to behave this way.

Thank you for looking.



Attached is my work thus far. Please be kind.

[June7]

What you are trying requires form and subform to be bound to same dataset. This usually doesn't work well. This is what the Split form was designed to provide. Select record in 'datasheet' section and it displays in the 'single' section. Can also have unbound controls in form header for input of filter criteria. All the pieces of code you have need to be behind a command button. Review http://www.allenbrowne.com/ser-62.html

[Stephenson]

June7,

Thank you for the link. This looks great. I'm going to see if I can get it to work for me.

Thanks for the help.

[Stephenson]

June7,

This worked great for me. I've been trying to get this to work for way too long. Well, I finally got it thanks to you. I appreciate the help.