Results 1 to 11 of 11
  1. #1
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82

    How to use saved queries to set a report recordsource?

    Hi All,

    I've got a series of saved queries that populate a report for me. One of the queries requires parameters that the user changes on a form, but if they don't set dates to limit the data returned, all the data should return. I've setup a QueryDef in my Report_Load event that gets all the data properly for me.



    I now need to use that data set as the record source for my report. I've having a heck of a time figuring out how to do that. Me.Recordsource=qdf (QueryDef) and Me.Recourdsource=rs (DAO Recordset) give me type mismatches.

    What am I missing here? I can't use a SQL statement as my recordsource here because the saved query I am using initially doesn't have the parameters; one of it's subqueries has the parameters in it. I can't reference them in the main query because I'm counting records produced by that first subquery.

    Thanks for any help.

    Scott

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Might help to see the code, but if you're creating a query you could have:

    Me.Recordsource="QueryName"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Or construct the query to return all dates and either open it based on that query only, or add a filter expression to the WHERE parameter of the report opening line.
    Or switch between 2 queries - one with all dates and one with dates (from a form, I presume). My preference is to have fewer objects rather than duplicates or close dupes.
    Don't understand "I can't use a SQL statement as my recordsource here".
    Why not build the sql in code and either concatenate using dates or don't? This is a very common approach.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Me.Recordsource="QueryName"
    That won't pass parameters to the query though. I need to do that through VBA. The user has the option of entering or not entering dates. If no dates are entered, i.e., the controls txtStartDate and txtEndDate are left empty, the results will not be limited by date. If the user enters txtStartDate, but no end date, the SQL will be Beween #2017-01-01# AND Now(). If the user enters both, then SQL will be Between #2017-01-01# AND #2018-12-31#. The user is also selecting a course to search by from a combobox.

    Code:
    Dim qdf as DAO.QueryDef
    
    Set qdf = CurrentDb.QueryDefs("qryStatsByCourseGraph3")
    qdf![CourseID] = Me.OpenArgs
    qdf![StartDate] = "2018-01-01"
    qdf![EndDate] = Now()
    Set qdf = Nothing
    This works, but qryStatsByCourseGraph3 looks like this:

    Code:
    SELECT qryOfficesInDistrict.OfficeName, Nz([CountComplete],0) AS TotalActiveDone, qryEmployeeCount04.Total, qryEmployeeCount04.TotalActive, Sum([Total]-[TotalActive]) AS TotalInactive, Sum([TotalActive]-[TotalActiveDone]) AS TotalActiveNotDone
    FROM (qryOfficesInDistrict LEFT JOIN qryCount02 ON qryOfficesInDistrict.OfficeName = qryCount02.OfficeName) INNER JOIN qryEmployeeCount04 ON qryOfficesInDistrict.OfficeName = qryEmployeeCount04.OfficeName
    GROUP BY qryOfficesInDistrict.OfficeName, Nz([CountComplete],0), qryEmployeeCount04.Total, qryEmployeeCount04.TotalActive;
    To make things more complicated, the parameters go into another query not even referenced in the one above:

    Code:
    SELECT qryUnionInDistrictLocation.OfficeName, tblIndividualLearning.CatalogueID, Max(tblIndividualLearning.DateCompleted) AS MaxOfDateCompleted, tblIndividualLearning.EmpID, tblEmployee.LastName, tblEmployee.SubstantivePosition, Max(tblIndividualLearning.DateCompleted) AS MaxOfDateCompleted1
    FROM (qryUnionInDistrictLocation INNER JOIN tblEmployee ON qryUnionInDistrictLocation.EmpID = tblEmployee.EmpID) INNER JOIN tblIndividualLearning ON (tblEmployee.EmpID = tblIndividualLearning.EmpID) AND (tblEmployee.EmpID = tblIndividualLearning.EmpID)
    GROUP BY qryUnionInDistrictLocation.OfficeName, tblIndividualLearning.CatalogueID, tblIndividualLearning.EmpID, tblEmployee.LastName, tblEmployee.SubstantivePosition, qryUnionInDistrictLocation.District
    HAVING (((tblIndividualLearning.CatalogueID)=[CourseID]) AND ((Max(tblIndividualLearning.DateCompleted)) Between [StartDate] And [EndDate]) AND ((qryUnionInDistrictLocation.District)=FindDistrict()));
    So basically, I need to get Course ID, Start Date and End Date to this last query through VBA. Using qdf does that, but now I need to use those results as the report record source.

    If you made it here, thanks for sticking with me!!!

  5. #5
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Quote Originally Posted by Micron View Post
    Don't understand "I can't use a SQL statement as my recordsource here".
    Sorry, I misspoke here. I meant that using SQL here would be very complicated. I'm an amateur when it comes to VBA and I say S-Q-L when I describe the language instead of "Sequel"! I had to really dumb down those queries to make it produce what I wanted. I am not good with joins and subqueries. The whole date thing was just dumped on me this morning. Now I'm trying to add it in.

    Quote Originally Posted by Micron View Post
    Why not build the sql in code and either concatenate using dates or don't? This is a very common approach.
    That's what I would normally do, but with the amount of saved queries being used in conjunction with each other, it would be... Challenging.

    !!!

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Have the report's recordsource refer to a NAMED query such as qTempQuery. Then just use vba to modify the properties of the querydef of qTempQuery before opening the report.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Without studying all the posted code, here is a VERY rudimentary example of how you go about stringing sql together, which I still see as the solution. This is by no means accurate or all inclusive and was just thrown together in NotePad and I didn't worry about text or date delimiters and such.
    Code:
    Dim sqlStart as string, sqlEnd as string
    dim sqlBdate as string, sqlEdate as string
    Dim sql as string
    
    sqlStart = "SELECT * FROM MyTable "
    sqlEnd = " ... SORT BY ...."
    
    If Not IsNull(me.txtBeginDate) then sqlBdate = " WHERE MyTable.BeginDate >= " & me.txtBeginDate
    If Not IsNull((me.txtEndDate) then sqlEdate = " AND MyTable.EndDate <= " & me.txtEndDate
    
    sql = sqlStart & sqlBdate & sqlEDate & sqlEnd
    It's not intended to provide for every situation you need to cover; only to show the gist of the approach. Maybe you need to validate for one date only, or must have two. Or have or not have text criteria, or... All the situational tests would be decided by you. Maybe you'd even need Select Case blocks for variations on a particular field. Too many ifs here for me to keep blabbing, so I'll let you chew this point over. You can research the concept, or there may be enough code in Allen Browne's search form app to get you started.
    http://allenbrowne.com/ser-62.html

    EDIT: saw your last post after I posted the above. davegri's notion is worth considering if building the sql is too daunting. Although, you can use the query design to do most of it for you.

  8. #8
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Quote Originally Posted by davegri View Post
    Have the report's recordsource refer to a NAMED query such as qTempQuery. Then just use vba to modify the properties of the querydef of qTempQuery before opening the report.
    I've not worked with the QueryDef object much, so I apologize for not following.

    In the report properties, I'm going to set the record source = qTempQuery, but it doesn't exist anywhere? How is it defined in VBA after the fact and modified to set the parameters and point to the other saved query?

    I see where it's going, I just don't know how to get there!

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Set up the sql in MakeTheQuery, then pass that sql string and the desired query name to fcnCustomizeSQL as below:
    Code:
    Public Sub MakeTheQuery()
        Dim ssql As String
        ssql = "Select * from tblShoe"
        Call fcnCustomizeSQL("qHello", ssql)
    End Sub
    
    ' Procedure : fcnCustomizeSQL
    ' DateTime  : 9/26/2006 20:57
    ' Author    : DaveGri
    ' Purpose   : Create named query from SQL string
    '---------------------------------------------------------------------------------------
    '
    Public Function fcnCustomizeSQL(sQryName As String, ssql As String) As Boolean
        Dim db   As DAO.Database
        Dim qdf  As DAO.QueryDef
        Set db = CurrentDb
        On Error Resume Next
        With db
            .QueryDefs.Delete (sQryName)                'Delete the query if it exists
            On Error GoTo 0
            Set qdf = .CreateQueryDef(sQryName, ssql)   'Create the query
        End With
        On Error GoTo 0
        Application.RefreshDatabaseWindow
    fcnCustomizeSQL_EXIT:
        Set qdf = Nothing
        Set db = Nothing
        Exit Function
    End Function

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    To each their own preference, but if I had a query I'd change its sql property rather than continually create/delete/recreate. Maybe that's no less problematic than continually replacing a stored query - I don't know. That's why I advocate the temp query more so.

    I'm curious about the example though. Why is the function designed to return a Boolean but nothing is assigned to the function?

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Why is the function designed to return a Boolean but nothing is assigned to the function?




    Adapted from code I wrote in 2006. Can be modified to either return a true/false for success or just removed.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-30-2018, 02:56 PM
  2. Replies: 4
    Last Post: 06-05-2014, 02:24 PM
  3. Replies: 0
    Last Post: 05-09-2011, 01:51 PM
  4. Replies: 1
    Last Post: 04-19-2011, 04:46 PM
  5. Recordsource with Multiple queries
    By darshita in forum Programming
    Replies: 1
    Last Post: 08-10-2009, 03:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums