Select form record set by more than one query

[Keith Nichols]

Hi,

I don't quite know if I am heading down a blind alley here. I want to filter a form to show a subset of the records via a toggle button. Click again and you go back to the full record set.

The form is based on a query and the filter is to be based on a copy of the query with several criteria and sorts added. This will result in a record set of active projects comprising about 20% of all records (65 out of 253). The sorting sets the record order to match the main management report and so the Planner can update the active records by navigating through the record set rather than having to search for each record using the Find button.

If what I am trying to do is not the way to achieve the desired result, I'd be grateful for any pointers elsewhere.

Regards,

Keith.

 

[Rod D]

Very Simple...

in theory...

The following code will set or clear the filter of the form, depending on whether the toggle is pressed or not. You need to paste this into the On Click event for the toggle. I have called this btnToggle1 in this example.

HTH
Regards
Rod


Private Sub btnToggle1_Click()

' Ensure filtering is enabled
Me.FilterOn = True

' Check state of button - TRUE="Pressed" FALSE="Not pressed"
If Me!btnToggle1.Value = False Then
Me!btnToggle1.Caption = "ALL"

' Setting a null filter effectively switches the filter off
Me.Filter = ""

Else
Me!btnToggle1.Caption = "FILTERED"
Me.Filter = "<Filter statement>"
End If

' Make sure the form is updated with the new filter
Me.Refresh
End Sub

 

[Keith Nichols]

Thanks

Looks good - I'll get it running tomorrow.

I learn every day from this site.

Regards,

Keith.

 

[Keith Nichols]

"I get prompted for a parameter when I don't expect it"

Hi Rod,

I have made progress but this isn't working yet.

My form and query are both based on the same table. The form loads ok. The query runs fine with no parameters set and returns the same record set as the form. When I use the toggle button to filter the form, the query throws up a parameter box. None of the situations Access help describes seems to be relevant to this. Any ideas?

Also, when I was pasting the code, I tried to set an error trap by copying one from a different procedure and renaming it. This didn't work and I have had this problem before so I am missing something here. What am I doing wrong?

A pretty ugly version of the database, with all the data and most of the forms etc stripped out for size considerations, is in the the attached zip file. I populated 2 records, test project 1 - not started (inactive) and Test Project 2 - in progress (active).

Here is the code I used to aply the query as the filter for the form.

Private Sub btn_Toggle_1_Click()
'On Error GoTo Err_btn_Toggle_1_Click:

' Ensure filtering is enabled
Me.FilterOn = True

' Check state of button - TRUE="Pressed" FALSE="Not pressed"
If Me!btn_Toggle_1.Value = False Then
Me!btn_Toggle_1.Caption = "All Projects"

' Setting a null filter effectively switches the filter off
Me.Filter = ""

Else
Me!btn_Toggle_1.Caption = "Active Projects"
Me.Filter = "qry_fdlg_Prj_Details_Active_Projects"
End If

' Make sure the form is updated with the new filter
Me.Refresh

'MsgBox Err.Description
' Resume Exit_btn_Toggle_1_Click
End Sub


Regards,

Keith.


From Access help:

I get prompted for a parameter when I don't expect it.

If you get prompted to type parameters when you attempt to run a query, report, or data access page, and you don't expect this prompt, one of the following might apply:

You deleted a parameter from the query design grid but not from the Query Parameters dialog box.
You renamed or misspelled a field in the query, report, or data access page but not in the query's underlying table(s).
You have a field that refers to a calculated field. If a field in the query performs a calculation based on the calculated value, make sure the Show check box is selected for the calculated field in the query design grid.

 

[Rod D]

Don't confuse queries and filters

Hi Keith

Please do not confuse queries and filters...
Queries produce a subset of records that forms ( & reports etc.) use as a recordset.
Filters "hide" (or "display") records within the available recordset.

To get your code to work as you want it, you need to tell Access what records you want to see, replace the line
Me.Filter = "qry_fdlg_Prj_Details_Active_Projects"
with
' Project_status_id = 2 = "In progress"
Me.Filter = "[Project_status_id]=2"

This is because queries and filters are not interchangable - hope this makes sense!
If you want to build more complex filters, the "AND", "OR", "NOT" operators are available, as well.

After you update your code, you will get the error once, as the filter is re-built, so don't panic when you see it. Allow it to fail and re-click the button and all will be fine.

Regards
Rod

 

[Keith Nichols]

Hi Keith

Please do not confuse queries and filters...
Queries produce a subset of records that forms ( & reports etc.) use as a recordset.

Thanks Rod,

I think I will be able to duplicate the results of my query by building a filter with And statements. I don't quite get something though. When I was wrestling with this, I'm sure that I came accross help saying that you can base a filter on a query?

The final results I am trying to achieve will include some sorting so that the record set in the form is displayed in the same order as the "active Projects" report (see the grouping and sorting).

I tried inserting an "orderBy" within the if statement but this failed. Am I on the right track?

Else
Me!btn_Toggle_1.Caption = "Active Projects"
' Project_status_id = 2 = "In progress"
Me.Filter = "[Project_status_id]=2"

' order the form in the same order as the report
Me = fdlg_Prj_Details
OrderBy Section_ID, Project_Status_ID, Project_Stage_ID, Project_Title

End If

 

[Keith Nichols]

Building more complex filters with logic operators

Hi Keith
If you want to build more complex filters, the "AND", "OR", "NOT" operators are available, as well.

Hi Rod,

Funny how things you expect to be easy can be hard and the reverse is also sometimes true. By searching the forum, I managed to find what I needed to get the records sorted in the right order.

The filter you gave me worked perfectly. However, I have been unable to build the more complex filters using AND, OR, and NOT. There is obvioulsy something wrong with the way I put the commands together. Access help is particularly unhelpful if you haven't the background and they don't go in for examples to illuminate things in a big way.

Anyway, to get round this, I listed a number of "not equal to" filters, basically everything I didn't want rather than listing things I did want. These appeared to do something, but I wasn't getting the same record set as the query for the report that I am triyng to match.

The definition of Active, as far as our department goes, is any project that we may have to do work on. For example, once the project is being implemented by others in Contract Execution, it is In Progress but not in our list of active projects because our input has finished. Similarly, projects that are On Hold or Not Started can have a significant input from us to either get them started or remove the hold.

The convoluted nature of this is why I wanted to use a query as a filter in the first place.

The query for the report filters 2 fields using the following expressions:

Project_Staus table
Project_Status
<>"CA" And <>"CO" And <>"RA"


Project_Status_ID Status Description
1 NS Not Started
2 IP In Progress
3 OH On Hold
4 RA Reassigned
5 CA Cancelled
6 CO Complete


How I tried to replicate this:
'Filter for project status
Me.Filter = "[Project_status_id]<>4"
Me.Filter = "[Project_status_id]<>5"
Me.Filter = "[Project_status_id]<>6"



Project Phase
"Pre SOW Prep" Or "Tender Preparation" Or "SOW" Or "PIN/SOR Review" Or "n/a"

Project_Phase table:

Project_Phase_ID Phase Description
1 Pre PIN Project Initiation Note
2 PIN/SOR Review Project Initiation Note / Statement of Requirements
3 SOW Scope Of Work
4 SOW: - ECS Engineering Consultancy Services
5 SOW: - FEED (i) Internal FEED
6 SOW: - FEED(e) External FEED
7 SOW: - Studies / Surveys
8 SOW: - EPIC Engineering, Procurement, Installation & Commissioning
9 SOW: - Construction Construction Scope Of Work
10 Tender Preparation
11 Bid Evaluation
12 Contract Execution
13 n/a Not Applicable - Internal projects etc.
14 Pre SOW Prep Substantial preparation work beyond PIN/SOR Review
15 EE Study


How I tried to replicate the effect:
'Filter for Project Phase
Me.Filter = "[Project_Phase_ID]<>1"
Me.Filter = "[Project_Phase_ID]<>2"
Me.Filter = "[Project_Phase_ID]<>11"
Me.Filter = "[Project_Phase_ID]<>12"


I'm hoping you can see what I am trying to achieve and give me a pointer to the right way to do it.

Regards,

Keith.

 

[Rod D]

Your filter statement

Hi Keith

If I understand you correctly - You want to display the projects that are Not started, On hold or In progress, BUT ONLY if they are not pre-initiation or being delivered.

My only problem with the list that you gave is ... you included "PIN/SOR Review" then removed it Me.Filter = "[Project_Phase_ID]<>2"

Anyway, that aside, yor filter is...
Me.Filter = "([Project_status_id]<4) and ([Project_Phase_ID]<>1 and [Project_Phase_ID]<>2 and [Project_Phase_ID]<>11 and [Project_Phase_ID]<>12)"

Please note the bracketing - the first filters the Status to items 1 to 3 (NS, OH, IP) i.e. The status less than 4. The second filters the list you gave for Phase (NOT 1, 2 ,11 or 12). Only if BOTH these statements are true will the record be displayed. If Phase 2 should be shown, I'm sure you can change it.

Let me know how you get on.
Regards
Rod

 

[Keith Nichols]

If I understand you correctly - You want to display the projects that are Not started, On hold or In progress, BUT ONLY if they are not pre-initiation or being delivered.

Me.Filter = "([Project_status_id]<4) and ([Project_Phase_ID]<>1 and [Project_Phase_ID]<>2 and [Project_Phase_ID]<>11 and [Project_Phase_ID]<>12)"

Hi Rod,

I think you have understood it right. I struggled with this today and eventually ended up getting the "And" and "Or" functions working. However my filtered form still had a different total of records to my report.

On further investigation I realised that the query for the report was very complicated with loads of tables shown.

To clarify matters, I created a new query based on a single table (the project_details table that the form is based on) to select just the project stage and then a query based on that query to select for the phase. This returned the same record set as the original more complicated query.

I was then able to make filters that would match each of these queries. Trouble was, they wouldn't return the right record set when they were both enabled in the code.

I realised that the problem was the filters were separate and so the second filter didn't run for some reason. Today's question was to have been how to join the two filters. Your code has already answered that.

BTW: I very much appreciate the explanations as well as the code. I'm still near the beginning of all this and so many things are inadequately explained (fro virgins at least) in the help files with few or meaningless examples. It seems to me that the help could be tailored to give a little more "hand-holding" the newbies a bit more.

Here's te code I managed to crank out. I'm very hopeful that the code you gave me will generate the correct record set.

'Filter for Project status
Me.Filter = "[Project_status_id]=1 Or [Project_status_id]= 2 Or [Project_status_id]=3"

'Filter for Project Phase
Me.Filter = "[Project_Phase_ID]=2 Or [Project_Phase_ID]=3 or [Project_Phase_ID]=10 Or [Project_Phase_ID]=13 Or [Project_Phase_ID]=14"

Kind regards,

 

[Rod D]

Thanks for the kind words

Hi Keith
Thanks for the kind words - I sometimes worry that I "ramble on".

I am a little concerned with what you said in the last post.
Firstly:
The selection criteria for the Phases has changed dramatically - you will know if this is correct or not when you apply the filter.

Secondly:
If you intend to apply a different filter to each of the button states (Pressed & Not Pressed), you will see the following:
1) Records will be displayed when the Status is 1, 2 or 3 and ignoring the Phase value
2) Records will be displayed when the Phase is 2, 3, 10, 13 or 14 and ignoring the Status value
3) You will never get access to the whole recordset returned by the query as one of the filters will always be active.

I don't believe this is what you want, so you need to join the two filter condition sets together with an "AND". It would look like...

Me.Filter = "([Project_status_id]=1 Or [Project_status_id]= 2 Or [Project_status_id]=3) AND ([Project_Phase_ID]=2 Or [Project_Phase_ID]=3 or [Project_Phase_ID]=10 Or [Project_Phase_ID]=13 Or [Project_Phase_ID]=14)"
or
Me.Filter = "([Project_status_id]<4) AND ([Project_Phase_ID]=2 Or [Project_Phase_ID]=3 or [Project_Phase_ID]=10 Or [Project_Phase_ID]=13 Or [Project_Phase_ID]=14)"

This would give the filtered list on one button state and the unfiltered list in the other. If I have got this wrong and you do actually want to filter Status and Phase seperately - let me know as there is a solution in using Triple state, but I don't want to confuse things unnecessarily right now.

Regards
Rod

 

[Keith Nichols]

How do I sort the record set of the form?

Hi Keith

Firstly:
The selection criteria for the Phases has changed dramatically - you will know if this is correct or not when you apply the filter.

Secondly:
If you intend to apply a different filter to each of the button states (Pressed & Not Pressed), you will see the following:
1) Records will be displayed when the Status is 1, 2 or 3 and ignoring the Phase value
2) Records will be displayed when the Phase is 2, 3, 10, 13 or 14 and ignoring the Status value
3) You will never get access to the whole record set returned by the query as one of the filters will always be active.

Hi Rod,

Your second point first:
I had realized that having 2 separate filters was not working for me. Either one returned the right results for itself, but in conjunction they did not.

They weren't set to run on both states of the button (an either or filter state), rather they were sequential lines in the vb that run on the button press. Actually, I inverted things to make the null filter run on button press and the filter run on not pressed. The filter was copied into the form open code and so the default for the form is now the filtered record set. Anyway, I knew I had to join them into one line, but couldn't deduce how to do it. Parentheses were the answer to that bit of the problem.

Your first point second:
I actually messed around with this for quite a while. I'm at home now so don't have the actual code I ended up with but in essence I created a filter like this:

Me.Filter = "([Project_status_id]<4) AND (([Project_Details.Project_Phase_ID]>1 and [Project_Details.Project_Phase_ID]<11) or [Project_Details.Project_Phase_ID]=13 Or [Project_Details.Project_Phase_ID]=14)"

I have to say, between the various phases and filters I got confused (again). I ended up stripping out all the records from my database (working copy!) leaving just 30, and setting various combinations of status and phase, drawing a little matrix and running the filter permutations until I got the right record set. I couldn’t get it to work without the “Project_Details” reference for some reason.

I then revisited the queries that feed the matching report and cranked it until it matched.

You will be pleased to know that I now have very straightforward queries, although it takes 2 of them to get the right record set (one to filter phase and one to filter status).

The final part of this problem is something I thought I had sorted, but now that the filter works as planned, I find that it isn't sorted at all:
How do I sort the record set of the form?

Ok. The report I'm trying to match groups the results firstly by section, status, then phase and finally the projects are listed alphabetically. I have something like:

Orderby: Section_ID, Status_ID, Phase_ID, Project_Title

Any ideas?

Regards,

Keith.

 

[Rod D]

Hi Keith - pleased to see that you are making progress.

Your sorting probem can be resolved by adding two lines of code.
Me.OrderbyOn = True
Me.OrderBy = "Section_ID, Project_Status_ID, Project_Phase_ID, Project_Title"

This will allow the sorting to take place and then applies the sort order based on the fields included in the Me.OrderBy statement. Sorting will take place in the order that the fields are listed. I have assumed that you wanted the form and report sorted the same way.

I guess the reason why you have had to include the "Project_Details." in the filter statement is because the fields are not included in the query, so a link back to the table is required.

Any chance that you could post the DB with the 30 records at some point?
I'm curious about your two queries...? Obviously strip out everything that you would rather the world did not see....

Hope this helps
Regards
Rod

 

[Keith Nichols]

Any chance that you could post the DB with the 30 records at some point?
I'm curious about your two queries...? Obviously strip out everything that you would rather the world did not see....

Hope this helps
Regards
Rod

Rod,

I haven't checked yet, but I'm sure the orderby = on is the step I was missing. I should be able to get that working so many thanks yet again.

When I first wanted to post on this this subject, I tried to strip out everything that wasn't relevant so the file size would be small enough. Unfortunately, it kind of crumbled in a heap. I will try again. I am on leave from my work for the next few weeks so it wil be after that that I have time to do it.

Kind regards,