Filter form using checkbox and combo

[Sketchin]

I have a reservation form and I want to tick a checkbox that will filter the form based on what is in the "Reservation Status" combo box.
When the checkbox is ticked, the code would remove all records that have "Complete" as a status in the "Reservation Status" combo box. The non-working code that I currently have is:

Private Sub chkHideComplete_AfterUpdate()
On Error Resume Next
    If Me.chkHideComplete = True Then
        Me.filter = "[ReservationStatus] = 1"
        Me.FilterOn = True
        
    Else
       Me.filter = "[ReservationStatus] = 1"
       Me.FilterOn = False
       
    End If
    
     DoCmd.GoToRecord , "", acLast
     Me.cboContactID.Requery
End Sub

Thanks for the help

 

[Mihail]

Try this:

Private Sub chkHideComplete_AfterUpdate()
  Me.filter = "[ReservationStatus] = 0"
  Me.FilterOn = chkHideComplete 

     DoCmd.GoToRecord , "", acLast
     Me.cboContactID.Requery
End Sub

If don't work then show us the combo row sources (SQL)

 

[Sketchin]

It didn't work. Combo row source is:
SELECT [tblLookup_Reservation_Status].[ReservationStatusID], [tblLookup_Reservation_Status].[ReservationStatus] FROM tblLookup_Reservation_Status ORDER BY [ReservationStatusID];

 

[Sketchin]

I must have got distracted when i made the first post. I forgot to mention the code works, but it ONLY shows reservations where the status is 1. What i need is multiple criteria that says :
Me.filter = "[ReservationStatus] = 1" AND "[Reservationstatus] = 2" AND etc...

 

[Mihail]

I must have got distracted when i made the first post. I forgot to mention the code works, but it ONLY shows reservations where the status is 1.

These is good a new. Try this:

Private Sub chkHideComplete_AfterUpdate()
  If Me.chkHideComplete Then
    Me.filter = "[ReservationStatus] = " & Me.[COLOR=Blue][B]cmbReservationStatus '[I][COLOR=Black]Replace with the correct name[/COLOR][/I][/B]
[/COLOR]     Me.FilterOn = True
  Else
    Me.FilterOn = False
  End If

  DoCmd.GoToRecord , "", acLast
  Me.cboContactID.Requery End Sub

This should work and display records for a certain (single) status.

I don't understand how you have multiple reservation status

What i need is multiple criteria that says :
Me.filter = "[ReservationStatus] = 1" AND "[Reservationstatus] = 2" AND etc...

Maybe you wish to say that you need to select those records that have reservation status 1 OR 2 OR ...... So, OR, not AND.

 

[Sketchin]

The reservation status's are as follows:

In
Out
Complete
RMA
Do Not Count
Information Request

You are right, it should be 1 OR 2 OR 3 etc...

 

[Sketchin]

Here is another way to put it. If I use this code:

Private Sub chkHideComplete_AfterUpdate()
On Error Resume Next
    If Me.chkHideComplete = True Then
        Me.filter = "[ReservationStatus] = 3"
        Me.FilterOn = True

    Else
'       Me.filter = "[ReservationStatus] = 1"
       Me.FilterOn = False

    End If

     DoCmd.GoToRecord , "", acLast
     Me.cboContactID.Requery
End Sub

The only records that are shown are "complete" records but what I really want to do is NOT show complete records.

 

[Sketchin]

Figured it out:

Private Sub chkHideComplete_AfterUpdate()
On Error Resume Next
    If Me.chkHideComplete = True Then
        'Show every reservation with a status other than "complete"
        Me.filter = "[ReservationStatus] = 1 Or [ReservationStatus] = 2 OR " & _
        "[ReservationStatus] = 4 OR [ReservationStatus] = 5 OR [ReservationStatus] " & _
        "= 6 OR [ReservationStatus] = 7 OR [ReservationStatus] = 8"
        Me.FilterOn = True

    Else
       Me.FilterOn = False
    End If
     'Go to last record
     'DoCmd.GoToRecord , "", acLast
     Me.cboContactID.Requery
End Sub