Filter on Form

Hi Experts,

I have searched all over the internet to find a solution but couldn’t find anything that made it easy for me.

I am a newbie to creating access DB (I see a lot of newbie’s come here) and I am trying to teach myself VB, but I am not there yet.

I have a DB in access 2003, on my main form [Patients] its source is from a
qry [Qry_All_Patiens], which is created from table[Tbl_All_Patient s]. One of the fields available on the form is PTstatus. The patient can have several statuses

EX:

PT Status: A, B, C, D, E, F, 4, 8, 1

I created a table with all the different patient status [Tbl_Status]. I would like to create a combo box on the form with this table (Tbl_Status) that will allow me to filter by status. So if I wanted to look at just patients in status 4 I can just filter it and then remove the filter so that I can go back to all the records.

Is there a way I can code the combo box to do that. If I don't need to use VB, what else can I do to get this done. If it can’t be done please let me know. I don’t know what can be done or cannot be done in VB. Thanks for any feedback.

Wow! it worked perfectly. Thank you very much.

I was wondering is there a way to reset the combo box after removing the filter. This is not a big deal, I'm really happy it is working.
Thank you!

It works, Thank you!

I have a quick question, could I use the same code abovr to create a filter for another field on the form. I want to be able to filter by [PTstatus] or [PTFNCLS]. From the code above I would just change the field/table name correct?

Expand|Select|Wrap|Line Numbers

1.  
2. Private Sub cboStatus_AfterUpdate()
3. Dim strFilter As String
4.  
5.     strFilter = "select * from [Qry_All_Patients] WHERE PTstatus='" & cboStatus & "';"
6.  
7.     Me.RecordSource = strFilter
8.     Me.Requery
9.  
10. End Sub
11.  
12.  

This works for the PTstatus because the cboStatus is based on PTStatus so you would need a new combo box based on PTFNCLS (e.g. cboFncls) and a new afterupdate event on this combo box as follows:

Expand|Select|Wrap|Line Numbers

1.  
2. Private Sub cboFncls_AfterUpdate()
3. Dim strFilter As String
4.  
5. strFilter = "select * from [Qry_All_Patients] WHERE PTFNCLS='" & cboFncls & "';"
6.  
7. Me.RecordSource = strFilter
8. Me.Requery
9.  
10. End Sub
11.  
12.  

The reset button is fine as it is.

Mary