Query Continous form in Form View from Drop Down List

[sross81]

Is there a way to query a continous form while in form view from a drop down list?

For example I have a field on the form called encounter status. The form includes a list of encounters each with their status. I want the user to be able to select a status from a drop down list at the top of the form to only see the New, or Follow up encounters? It would be helpful if they could turn it off to view the original data set as well.

The form's record source is a query.

Can anyone point me in the right direction?

Thank you!

 

[bob fitz]

Perhaps you could create a combo box in the Header Section of the form and use code in its After Update event to Filter the form. The attached db is a simplistic example that uses an Option Group control instead of a combo box.

 

[sross81]

Thank you I will try this out.

 

[sross81]

This is what I try using a combo box. I do not get any errors, but I do not get any results. I chose the option from the combo box that is #2 and it goes through the second IF statement, but it just refreshes the form to be blank when there should be rows. Any ideas?

If Me.cboFilterEncounterStatus = 1 Then
Me.Filter = Me.EncounterStatusID = 1
Me.FilterOn = True
Me.Requery
End If

If Me.cboFilterEncounterStatus = 2 Then
Me.Filter = Me.EncounterStatusID = 2
Me.FilterOn = True
Me.Requery
End If

 

[boblarson]

The filter part has to be in quotes and without the ME portion. And, you can be more efficient with a Select Case statement:

Dim strFilter As String
Select Case Me.cboFilterEncounterStatus 
    Case 1
         strFilter ="[EncounterStatusID]=1"
    Case 2
         strFilter = "[EncounterStatusID]=2"
    Case Else
         strFilter = vbNullString
End Select
Me.Filter = strFilter
Me.FilterOn = (strFilter <> vbNullString)

No requery necessary.

 

[sross81]

Thank you this works perfect!