By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Filter on Form

P: 24
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_Patients]. 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.
Nov 15 '06 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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_Patients]. 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:

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.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdReset_Click()
  3. Dim strFilter As String
  4.  
  5.     strFilter = "select * from  [Qry_All_Patients];"
  6.  
  7.     Me.RecordSource = strFilter
  8.     Me.Requery
  9.  
  10. End Sub
  11.  
  12.  
Nov 15 '06 #2

P: 24
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!
Nov 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdReset_Click()
  3. Dim strFilter As String
  4.  
  5.   strFilter = "select * from [Qry_All_Patients];"
  6.  
  7.   Me.RecordSource = strFilter
  8.   Me.Requery
  9.   Me.cboStatus = Null
  10.  
  11. End Sub
  12.  
  13.  
Nov 16 '06 #4

P: 24
It works, Thank you!
Nov 16 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
It works, Thank you!
You're welcome.
Nov 16 '06 #6

P: 24
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?
Nov 16 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 16 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
BTW, this assumes PTFNCLS is a text field, if it is a number remove the single quotes enclosing the cboFncls.



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
Nov 16 '06 #9

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 16 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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)
I'll have to check that one out.

Thanks Nic;o
Nov 16 '06 #11

P: 24
That actually made sense to me, Thank you very much.
Nov 16 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.