After update event

[Neilster]

Hi Guys

I have a form with a set of combo filters which filter one after the other. now I need a date filter button. I have the following code which works fine, however when I filter the dates it will filter every employee's dates....

So an employee logs on filters their name, status, city and so on until they get the records they want to work that day, I need then to filter the date to & from and still just show that persons work and not everyone's.

I know can be done because Access has it on its standard right-click filters.

Please help and how can I add to this code to make that work.

Dim strDateField As String
Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strDateField = "[NextCallDate]"
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If

If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
Me.Filter = strWhere
Me.FilterOn = True
End sub

 

[JHB]

..
I have a form with a set of combo filters which filter one after the other. now I need a date filter button. I have the following code which works fine, however when I filter the dates it will filter every employee's dates....
..

You need to include filters from the comboxes also.

 

[Neilster]

Hi

Sorry how do you mean?

 

[JHB]

Sorry how do you mean?

I have a form with a set of combo filters which filter one after the other. now I need a date filter button. I have the following code which works fine, however when I filter the dates it will filter every employee's dates....

I read the above as each time you make a choice in the comboxes, the form filters okay.
So you need to include that filter in the filter you make for the dates.
But maybe I misunderstand you.

 

[Neilster]

So you mean something like this??

Dim strDateField As String
Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strDateField = "[NextCallDate]"
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If

If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
Me.Filter = strWhere
Me.FilterOn = True

If Nz(Me.cboOPOwner.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False


ElseIf Me.cboOPOwner.ListIndex <> -1 Then

Me.Form.Filter = "[OPOwner] = '" & _
Replace(Me.cboOPOwner.Text, "'", "''") & "'"
Me.FilterOn = True

Else
Me.Form.Filter = "[OPOwner] Like '*" & _
Replace(Me.cboOPOwner.Text, "'", "''") & "*'"
Me.FilterOn = True
End If


Me.cboOPOwner.SetFocus
Me.cboOPOwner.SelStart = Len(Me.cboOPOwner.Text)

 

[JHB]

So an employee logs on filters their name, status, city and so on until they get the records they want to work that day, ...

How do you filter the form, to get their name, status, city and so on?

 

[Neilster]

So using combox boxes they would either enter their name or drop down to find their name, then the same status, city, and so on using the 'afterupdate' event.

Using the VBA below.

Private Sub cboOPOwner_AfterUpdate()

If Nz(Me.cboOPOwner.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False


ElseIf Me.cboOPOwner.ListIndex <> -1 Then

Me.Form.Filter = "[OPOwner] = '" & _
Replace(Me.cboOPOwner.Text, "'", "''") & "'"
Me.FilterOn = True

Else
Me.Form.Filter = "[OPOwner] Like '*" & _
Replace(Me.cboOPOwner.Text, "'", "''") & "*'"
Me.FilterOn = True
End If


Me.cboOPOwner.SetFocus
Me.cboOPOwner.SelStart = Len(Me.cboOPOwner.Text)

End Sub

On the second filter and so on the code uses the 'And' as below..

Private Sub cboCity_AfterUpdate()

If Nz(Me.cboCity.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False


ElseIf Me.cboCity.ListIndex <> -1 Then
Me.Form.Filter = Me.Form.Filter & " and [City] = '" & _
Replace(Me.cboCity.Text, "'", "''") & "'"
Me.FilterOn = True


Else
Me.Form.Filter = Me.Form.Filter & " and [City] = '" & _
Replace(Me.cboCity.Text, "'", "''") & "'"
Me.FilterOn = True
End If


Me.cboCity.SetFocus
Me.cboCity.SelStart = Len(Me.cboCity.Text)

End Sub