UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Query As Filter To Open Form, Access 2016    
 
   
Consonanza
post Aug 17 2019, 10:08 AM
Post#1



Posts: 167
Joined: 1-June 10



I have a button on form A which opens form B (frmDiscs). I want form B to be filtered on a query (qryKPI_T1Discs)
but I can't figure out how to fire this when I launch from form A.

Among things I've tried is this:
CODE
DoCmd.OpenForm "frmDiscs", , "qryKPI_T1Discs", , , , Openargs:=Me.Name

but it just opens frmDiscs with no filter applied.

The SQL for the above query is:
CODE
SELECT DISTINCT tblDiscographyBN.fldDiscID
FROM tblDiscographyBN INNER JOIN tblTrackTitles ON tblDiscographyBN.fldDiscID = tblTrackTitles.fldDiscID
WHERE (((tblTrackTitles.fldRuntime) Is Null));

It makes no difference if I set the "Output all fields" query property to Yes.
Go to the top of the page
 
dale.fye
post Aug 17 2019, 10:17 AM
Post#2



Posts: 160
Joined: 28-March 18
From: Virginia


You might try:

DoCmd.OpenForm "frmDiscs", , "[fldDiscID] IN (SELECT fldDiscID FROM qryKPI_T1Discs)", , , , Openargs:=Me.Name


--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
GroverParkGeorge
post Aug 17 2019, 10:18 AM
Post#3


UA Admin
Posts: 35,653
Joined: 20-June 02
From: Newcastle, WA


What is the recordsource for the form you are opening, frmDiscs ?

If it's already the same query, qryKPI_T1Discs, then it should be filtered as you want, i.e. that query returns only records WHERE tblTrackTitles.fldRuntime Is Null

Is that NOT what you want?

If you want something else, please explain what that would be.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Aug 17 2019, 10:22 AM
Post#4


UtterAccess VIP
Posts: 10,954
Joined: 10-February 04
From: South Charleston, WV


George is right. Open that query and have another look at it. If not, post it.

(I originally though you were using the wrong parameter. To filter forms on opening use the where condtion. But that doesn't seem to be the case here.)

--------------------
Robert Crouser
Go to the top of the page
 
Consonanza
post Aug 17 2019, 10:41 AM
Post#5



Posts: 167
Joined: 1-June 10



@dale.fye

No, that just opens frmDiscs with no filter applied.

@GroverParkGeorge

The source for frmDiscs is tblDiscographyBN.

The query returns only records where tblTrackTitles.fldRuntime is null and it is those records
that I want to see filtered when frmDiscs is opened.

@projecttoday

The SQL for the query is shown in my original post.

Correction to my first post. The "unique values" property is set to true. If I change the "Output all fields" property to "No", the query produces too many records.
In one of my many net searches to find a solution I recall reading that in using a query as a filter in the above way, you must have its "Output all fields" property set to True.

Go to the top of the page
 
GroverParkGeorge
post Aug 17 2019, 10:49 AM
Post#6


UA Admin
Posts: 35,653
Joined: 20-June 02
From: Newcastle, WA


QUOTE
@dale.fye

No, that just opens frmDiscs with no filter applied.

@GroverParkGeorge

The source for frmDiscs is tblDiscographyBN.

The query returns only records where tblTrackTitles.fldRuntime is null and it is those records
that I want to see filtered when frmDiscs is opened.


I would suggest, then, that you use that query as the recordsource for frmDiscs, instead of tblDiscographyBN. That'll do it.

Of course, that's probably too simplistic, but should make the point about filtering.

Actually, what you should try is a query that uses this existing query as a subquery to restrict the records returned from tblDiscographyBN. Join it on the Primary key/Foreign Key.
This post has been edited by GroverParkGeorge: Aug 17 2019, 10:52 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Consonanza
post Aug 18 2019, 04:10 AM
Post#7



Posts: 167
Joined: 1-June 10



I can't change the source of frmDiscs as it is the main form for viewing,adding and editing all the records in the database.
Also the subquery idea did not work either.
But your comments gave me a thought. I decided to move the filtering part of the code to frmDiscs itself. I used a Select Case argument on Openargs to detect which form had launched frmDiscs
and then filtered frmDiscs in the On Open event using the code suggested by dale.fye. And it worked!

For completeness, here is the relevant code

In the launch form (frmKeyprogressIndicators)

CODE
DoCmd.OpenForm "frmDiscs", , , , , , Openargs:=Me.Name

and in frmDiscs

CODE
strSQL = "[fldDiscID] IN (SELECT fldDiscID FROM qryKPI_T1_Discs)"

If Not IsNull(Openargs) Then
Select Case Openargs
Case Is = "frmKeyprogressIndicators"
Me.Filter = strSQL
Me.FilterOn = True
Case Else
End Select
End If


My thanks to all for helping me stumble through this.
This post has been edited by Consonanza: Aug 18 2019, 05:09 AM
Go to the top of the page
 
projecttoday
post Aug 18 2019, 05:25 AM
Post#8


UtterAccess VIP
Posts: 10,954
Joined: 10-February 04
From: South Charleston, WV


It is simpler to use the where condition of the openform statement.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th September 2019 - 09:13 AM