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.
11 5298
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: -
-
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
-
-
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!
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
- Me.cboStatus = Null
-
-
End Sub
-
-
It works, Thank you!
You're welcome.
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?
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
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
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)
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
That actually made sense to me, Thank you very much.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
| |