473,320 Members | 1,799 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Filter on Form

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
11 5298
MMcCarthy
14,534 Expert Mod 8TB
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
Umoja
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
14,534 Expert Mod 8TB
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
Umoja
24
It works, Thank you!
Nov 16 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
It works, Thank you!
You're welcome.
Nov 16 '06 #6
Umoja
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
14,534 Expert Mod 8TB
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
14,534 Expert Mod 8TB
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
3,080 Expert 2GB
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
14,534 Expert Mod 8TB
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
Umoja
24
That actually made sense to me, Thank you very much.
Nov 16 '06 #12

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

Similar topics

1
by: Robert Neville | last post by:
I would like to add filter functionality to my database whether through the Main form or the subform. This question may be rudimentary, yet I have not less experience with filtering data outside...
0
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the...
2
by: Andante.in.Blue | last post by:
Hi everyone! I was wondering if there is a away to use Access 97's build in filter-by-form function but restrict its effect to just the subform. I have a parent form that shows the major...
3
by: Richard | last post by:
Hi, I have a form based on a table. When I filter the form I want to run a report based on the same table with the same filter as the form. No problem until I want to filter a combo box where...
1
by: jeffgeorge | last post by:
Hi all. Trying to move Filter By Selection, Filter By Form, and Apply/Remove Filter directly into my form. No luck. Is there anyway to move them into the header of the form or, as another...
7
by: damjanu | last post by:
Hi All; I need little help. I have a datasheet form. I allow user to do 'filter by selection'. My form contains a column with values. As user changes selections, I want to calculate totals....
8
by: dick | last post by:
I am just trying to print/report the results of a "filter by selection" which is done by right-clicking a form, filling in values, and "applying the filter." I have searched the newsgroups, and...
2
by: Salad | last post by:
I have a log file with a list of records. The log file can be unfiltered or filtered. I have a command button to call a data entry form from the log. At first I was only going to present the...
2
by: cefrancke | last post by:
I have a form (no underlying record set) that has two separate sub-forms on it. Each sub-form has data from two different tables. Above each sub-form there is one unbound combo box with a SQL...
1
by: woodey2002 | last post by:
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on. My databse mostly includes...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.