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
> Change The Recordsource Of A Report While Executing, Access 2016    
 
   
JackLamberton
post Jun 14 2017, 06:00 PM
Post#1



Posts: 85
Joined: 4-December 07
From: Fremont, CA


The Record Source on the report is initially set to "qryEveryone" but I want to be able to change the recordsource to any of the 3 different queries when I launch the report...

What I have tried so far, it is not working for me as the report seems to use the default recordsource ("qryEveryone"). MsgBox following the DoCMD.OpenReport statement accurately shows what I chose but the report output still shows the results of default recordsource ("qryEveryone"). See code below.

I can easily make a separate copy of the report for each recordsource but I know there has to be a better way. iconfused.gif What do you suggest me to do?

Thanks,
Jack

CODE
Private Sub cmdPrintPreview_Click()
    Dim callquery As String
    Me.Refresh
    Select Case txtWho
        Case "Individual"
            callquery = "qrySelectStudent"
        Case "Department"
            callquery = "qrySelectDept"
        Case "Everyone"
            callquery = "qryEveryone"
        Case Else
            MsgBox "Unknown Error Encountered in Private Sub cmdPrintPreview_Click"
    End Select
    DoCmd.OpenReport "rptBehaviorCaseLoadManagement", acViewPreview, callquery
    MsgBox "txtwho=" & txtWho & "   callquery=" & callquery
  End Sub
Go to the top of the page
 
Doug Steele
post Jun 14 2017, 06:32 PM
Post#2


UtterAccess VIP
Posts: 21,313
Joined: 8-January 07
From: St. Catharines, ON (Canada)


The 3rd parameter of the OpenReport method is FilterName.

Try passing the name of the query you want to use as the OpenArgs parameter (the 6th parameter), and putting logic in the report's Open event to change its RecordSource to the name of the query being passed as the OpenArgs value.

--------------------
Go to the top of the page
 
projecttoday
post Jun 14 2017, 08:19 PM
Post#3


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


I suggest you not use variable queries but base the report on just one unfiltered query and filter the report with the where condition parameter of the openreport statement.

--------------------
Robert Crouser
Go to the top of the page
 
LPurvis
post Jun 15 2017, 04:46 AM
Post#4


UtterAccess Editor
Posts: 16,060
Joined: 27-June 06
From: England (North East / South Yorks)


Or base the report on one query, and alter the definition of that query prior to launching the report.

CurrentDb.QueryDefs("ReportQuery").SQL = "SELECT * FROM qrySelectStudent"
etc.

Just another alternative.

Cheers

--------------------
Go to the top of the page
 
JackLamberton
post Jun 15 2017, 03:58 PM
Post#5



Posts: 85
Joined: 4-December 07
From: Fremont, CA


Thank you (Doug, ProjectToday and LPurvis)! I think I finally got it to work but I must admit being "Ich bin dummkopf" because I mispelled "qrySelectStudentDept" as "qrySelectDept" which was non-existent but my OpenReport code ran qryEveryone apparently by default because it couldnt find qrySelectDept --- I wish OpenReport would complain about not finding qrySelectDept. confused.gif

Once again thank you for trying to help me.
notworthy.gif
Go to the top of the page
 
projecttoday
post Jun 15 2017, 06:58 PM
Post#6


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


It would still be simpler to vary the where condition instead of the varying the whole query. Remember, simpler means less work for you and others when it comes to maintaining this database in the future.

--------------------
Robert Crouser
Go to the top of the page
 
JackLamberton
post Jun 15 2017, 07:45 PM
Post#7



Posts: 85
Joined: 4-December 07
From: Fremont, CA


Hi ProjectToday,

Can you give me an example how to do that? My VBA programming skills are limited.

Many thanks,
Jack
Go to the top of the page
 
projecttoday
post Jun 15 2017, 08:35 PM
Post#8


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


It's just an openreport statement. But first can you post the 3 queries you're using now?

--------------------
Robert Crouser
Go to the top of the page
 
LPurvis
post Jun 16 2017, 07:01 AM
Post#9


UtterAccess Editor
Posts: 16,060
Joined: 27-June 06
From: England (North East / South Yorks)


>> It would still be simpler to vary the where condition instead of the varying the whole query

I'd certainly agree, assuming that the varying sources of the report are essentially from the same core data that can just be filtered differently for different results.

--------------------
Go to the top of the page
 
projecttoday
post Jun 16 2017, 08:34 AM
Post#10


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


That's what I was driving at. But now that I look at my last post, let me clarify it a little: Jack, can you post the SQL code of those 3 queries.

--------------------
Robert Crouser
Go to the top of the page
 
JackLamberton
post Jun 16 2017, 10:37 AM
Post#11



Posts: 85
Joined: 4-December 07
From: Fremont, CA


Basically all 3 queries below are similiar except in StudentSelected field. Thank you very much.

QUERY: qrySelectStudent (SQL code)
CODE
SELECT tblBEHAVIORdb.StudentID, IIf(DLookUp("[StudentSelected]","LOCAL_tblDefaults")=[Student],"Yes","  ") AS SelectStudent, tblBEHAVIORdb.Student, tblBEHAVIORdb.Status, tblBEHAVIORdb.Dept, tblBEHAVIORdb.BehaviorLevel, tblBEHAVIORdb.DOB, tblBEHAVIORdb.GradeLevel, tblBEHAVIORdb.Gender, tblBEHAVIORdb.DayRes, tblBEHAVIORdb.DateReferred, tblBEHAVIORdb.Counseling, tblBEHAVIORdb.AssessSentToParents, tblBEHAVIORdb.AssessSigned, tblBEHAVIORdb.AssessRFCreceived, tblBEHAVIORdb.[AssessSigned+60days], tblBEHAVIORdb.WeeklyReports, tblBEHAVIORdb.DateClosed, tblBEHAVIORdb.Reason, tblBEHAVIORdb.SixWeeksMtg1, tblBEHAVIORdb.SixWeeksMtg2, tblBEHAVIORdb.SixWeeksMtg3, tblBEHAVIORdb.SixWeeksMtg4, tblBEHAVIORdb.SixWeeksMtg5, tblBEHAVIORdb.[DateLastMtg+60days], tblBEHAVIORdb.Comments, tblBEHAVIORdb.IEPbehaviorGoal, tblBEHAVIORdb.BIP, tblBEHAVIORdb.MinutesPerMonth, tblBEHAVIORdb.StillHere, tblBEHAVIORdb.VersionBEHAVIORapp
FROM tblBEHAVIORdb
WHERE (((IIf(DLookUp("[StudentSelected]","LOCAL_tblDefaults")=[Student],"Yes","  "))="Yes"));


QUERY: qrySelectStudentDept (SQL code)
CODE
SELECT tblBEHAVIORdb.StudentID, IIf(DLookUp("[DeptSelected]","LOCAL_tblDefaults")=[dept],"Yes","  ") AS SelectStudent, tblBEHAVIORdb.Student, tblBEHAVIORdb.Status, tblBEHAVIORdb.Dept, tblBEHAVIORdb.BehaviorLevel, tblBEHAVIORdb.DOB, tblBEHAVIORdb.GradeLevel, tblBEHAVIORdb.Gender, tblBEHAVIORdb.DayRes, tblBEHAVIORdb.DateReferred, tblBEHAVIORdb.Counseling, tblBEHAVIORdb.AssessSentToParents, tblBEHAVIORdb.AssessSigned, tblBEHAVIORdb.AssessRFCreceived, tblBEHAVIORdb.[AssessSigned+60days], tblBEHAVIORdb.WeeklyReports, tblBEHAVIORdb.DateClosed, tblBEHAVIORdb.Reason, tblBEHAVIORdb.SixWeeksMtg1, tblBEHAVIORdb.SixWeeksMtg2, tblBEHAVIORdb.SixWeeksMtg3, tblBEHAVIORdb.SixWeeksMtg4, tblBEHAVIORdb.SixWeeksMtg5, tblBEHAVIORdb.[DateLastMtg+60days], tblBEHAVIORdb.Comments, tblBEHAVIORdb.IEPbehaviorGoal, tblBEHAVIORdb.StillHere, tblBEHAVIORdb.BIP, tblBEHAVIORdb.MinutesPerMonth, tblBEHAVIORdb.VersionBEHAVIORapp
FROM tblBEHAVIORdb
WHERE (((IIf(DLookUp("[DeptSelected]","LOCAL_tblDefaults")=[dept],"Yes","  "))="Yes"));


QUERY: qryEveryone (SQL code)
CODE
SELECT tblBEHAVIORdb.StudentID, "Yes" AS SelectStudent, tblBEHAVIORdb.Student, tblBEHAVIORdb.Status, tblBEHAVIORdb.Dept, tblBEHAVIORdb.BehaviorLevel, tblBEHAVIORdb.DOB, tblBEHAVIORdb.GradeLevel, tblBEHAVIORdb.Gender, tblBEHAVIORdb.DayRes, tblBEHAVIORdb.DateReferred, tblBEHAVIORdb.Counseling, tblBEHAVIORdb.AssessSentToParents, tblBEHAVIORdb.AssessSigned, tblBEHAVIORdb.AssessRFCreceived, tblBEHAVIORdb.[AssessSigned+60days], tblBEHAVIORdb.WeeklyReports, tblBEHAVIORdb.DateClosed, tblBEHAVIORdb.Reason, tblBEHAVIORdb.SixWeeksMtg1, tblBEHAVIORdb.SixWeeksMtg2, tblBEHAVIORdb.SixWeeksMtg3, tblBEHAVIORdb.SixWeeksMtg4, tblBEHAVIORdb.SixWeeksMtg5, tblBEHAVIORdb.[DateLastMtg+60days], tblBEHAVIORdb.Comments, tblBEHAVIORdb.IEPbehaviorGoal, tblBEHAVIORdb.StillHere, tblBEHAVIORdb.BIP, tblBEHAVIORdb.MinutesPerMonth, tblBEHAVIORdb.VersionBEHAVIORapp
FROM tblBEHAVIORdb;
Go to the top of the page
 
projecttoday
post Jun 16 2017, 10:59 AM
Post#12


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


As expected. Just use qryEveryone for the report and vary the where condition of the openreport statement.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Jun 16 2017, 11:36 AM
Post#13


UtterAccess VIP
Posts: 9,092
Joined: 6-December 03
From: Telegraph Hill


Hmmm,

I don't think it would be so simple - you have to deal with the SelectStudent field.

It could be done if you changed the SQL to use a join with LOCAL_tblDefaults.

But that would require LOCAL_tblDefaults to have a StudentID field on which it could join - so that both LOCAL_tblDefaults.StudentSelected and LOCAL_tblDefaults.DeptSelected would both be available for the criteria.

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Jun 16 2017, 02:52 PM
Post#14


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


Looks like that table appears in the WHERE clause and as a Dlookup. Hmmm. What is the layout of this table. Also, tblBEHAVIORdb.SixWeeksMtg1, tblBEHAVIORdb.SixWeeksMtg2, tblBEHAVIORdb.SixWeeksMtg3, not good.

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


Custom Search
RSSSearch   Top   Lo-Fi    23rd September 2017 - 06:38 PM