Filter on Form

[Umoja]

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.

[MMcCarthy]

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.

Firstly, thank you for clearly explaining the problem it makes it easier to help you.

Yes you can do this. Create a combo box called for example cboStatus based on your table TblStatus.

Then create an After Update event on the combo box as follows:

 
Private Sub cboStatus_AfterUpdate()
Dim strFilter As String
	
	strFilter = "select * from  [Qry_All_Patients] WHERE PTstatus='" & cboStatus & "';"
	
	Me.RecordSource = strFilter
	Me.Requery
	
End Sub

To reset the form to show all ot the records.

The easiest way to do it is to create a reset command button. Put a command button on the form. Call it cmdReset and put in the following code:

 
Private Sub cmdReset_Click()
Dim strFilter As String
	
	strFilter = "select * from  [Qry_All_Patients];"
	
	Me.RecordSource = strFilter
	Me.Requery
	
End Sub

[Umoja]

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!

[MMcCarthy]

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!

Add a line to the reset button code as follows:

 
Private Sub cmdReset_Click()
Dim strFilter As String
 
  strFilter = "select * from [Qry_All_Patients];"
  
  Me.RecordSource = strFilter
  Me.Requery
[b]  Me.cboStatus = Null[/b]
 
End Sub

[Umoja]

It works, Thank you!

[MMcCarthy]

It works, Thank you!

You're welcome.

[Umoja]

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?

[MMcCarthy]

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?

 
Private Sub cboStatus_AfterUpdate()
Dim strFilter As String
	
	strFilter = "select * from  [Qry_All_Patients] WHERE PTstatus='" & cboStatus & "';"
	
	Me.RecordSource = strFilter
	Me.Requery
	
End Sub

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:

 
Private Sub cboFncls_AfterUpdate()
Dim strFilter As String

strFilter = "select * from  [Qry_All_Patients] WHERE PTFNCLS='" & cboFncls & "';"

Me.RecordSource = strFilter
Me.Requery

End Sub

The reset button is fine as it is.

Mary

[MMcCarthy]

BTW, this assumes PTFNCLS is a text field, if it is a number remove the single quotes enclosing the cboFncls.

 
Private Sub cboStatus_AfterUpdate()
Dim strFilter As String
 
	strFilter = "select * from [Qry_All_Patients] WHERE PTstatus='" & cboStatus & "';"
 
	Me.RecordSource = strFilter
	Me.Requery
 
End Sub

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:

 
Private Sub cboFncls_AfterUpdate()
Dim strFilter As String
 
strFilter = "select * from [Qry_All_Patients] WHERE PTFNCLS='" & cboFncls & "';"
 
Me.RecordSource = strFilter
Me.Requery
 
End Sub

The reset button is fine as it is.

Mary

[nico5038]

Personally I prefer to use the right-click popup menu to do the filtering of tables, queries and/or forms.
Check my Right-Click instruction at www.geocities.com/nico5038 for the info I give my users and thus save myself a lot of coding...

Nic;o)

[MMcCarthy]

Personally I prefer to use the right-click popup menu to do the filtering of tables, queries and/or forms.
Check my Right-Click instruction at www.geocities.c om/nico5038 for the info I give my users and thus save myself a lot of coding...

Nic;o)

I'll have to check that one out.

Thanks Nic;o

[Umoja]

That actually made sense to me, Thank you very much.