Results 1 to 10 of 10
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    Trying to set sub-report source filter in VBA

    I'm attempting to set the filter of a sub-report in much the same way as one would do for a form's RecordSource. Apparently, it can't be done like this:



    Click image for larger version. 

Name:	FilterSubReport.jpg 
Views:	14 
Size:	126.8 KB 
ID:	23589

    How do I filter the source of a sub-report?

  2. #2
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    This fails as well:

    Code:
     
    strSQL = "SELECT TDate, Description, Credit FROM tblRegister WHERE (((TDate)>=#" & Me.OpenArgs & _
             "#) AND ((Credit)>0)) ORDER BY TDate;"
    
    Me.ChildReceipts.Report.RecordSource = strSQL

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Been a long time since I did anything with OpenArgs.

    Does/is the SQL string correctly formed?
    Maybe add a message box after strSQL or add Debug.Print strSQL and a breakpoint
    Code:
    strSQL = "SELECT TDate, Description, Credit FROM tblRegister WHERE (((TDate)>=#" & Me.OpenArgs & _
             "#) AND ((Credit)>0)) ORDER BY TDate;"
    Debug.Print strSQL
    Me.ChildReceipts.Report.RecordSource = strSQL
    or
    Code:
    strSQL = "SELECT TDate, Description, Credit FROM tblRegister WHERE (((TDate)>=#" & Me.OpenArgs & _
             "#) AND ((Credit)>0)) ORDER BY TDate;"
    Msgbox strSQL
    Me.ChildReceipts.Report.RecordSource = strSQL
    Are the report master fields and sub report child fields linked? Curious if the linking field is in the SQL.
    Maybe this will help? http://access.mvps.org/access/forms/frm0031.htm

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Here's the formed string:
    Click image for larger version. 

Name:	FormedSQL.jpg 
Views:	10 
Size:	21.3 KB 
ID:	23592

    The assignment statement:
    Click image for larger version. 

Name:	CodeStmt.jpg 
Views:	10 
Size:	10.7 KB 
ID:	23593

    And the error produced:

    Click image for larger version. 

Name:	CodeError.jpg 
Views:	10 
Size:	19.1 KB 
ID:	23594

    The use of sub-reports is new to me, so I'm not at all understanding what might be going on. When I created the sub-report control, I specified its SourceObject as QReceipts, the name of a query. What shows as I look at the property sheet for the control is Query.QReceipts. I tried setting the strSQL string to include the qualifier "Query.Select.........." but get the same error. There isn't any RecordSource for the main report, so the Child and Master Links are empty.

    Don't know how to proceed here unless there's a way to actually update the query itself before the report is opened?

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    See this for info on subreport sourceobject - poster had an issue and it has a solution.
    I think the strSQL is being interpreted as the name of the subreport.

    If you put a breakpoint on the line where the error occurs, and in the vbe do View Locals, and run to the breakpoint; then you can query the values of controls and variables.

    You might be able to trace Me.ChildReceipts. ???? and see what Access has thinks you mean..

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Hardly how I expected to resolve the issue, but time is of the essence.

    The popup form that obtains the date of interest modifies the query def before opening the main report. Once the user enters the date into text box "tbBegDate" and clicks "OK" the command event updates the SourceObject queries for the two sub-reports contained in rptTresReport before opening that report.

    The "ModifyQuery" sub just updates the queries to their revised SQL.

    Code:
    Private Sub cmdOK_Click()
    Dim strQReceipts As String
    Dim strQExpences As String
    
    'Trying to Change the SourceObject of the sub-reports in rptTresReport became futile, so the query defs are modified here
    
    strQReceipts = "SELECT TDate, Description, Credit FROM tblRegister WHERE (((TDate) >= #" & Me.tbBegDate & "#)" & _
                    " And ((Credit) > 0)) ORDER BY TDate;"
    
    Call ModifyQuery("QReceipts", strQReceipts)
    
    strQExpences = "SELECT TDate, Description, Debit FROM tblRegister WHERE (((TDate) >= #" & Me.tbBegDate & "#)" & _
                    " And ((Debit) > 0)) ORDER BY TDate;"
    
    Call ModifyQuery("QExpences", strQExpences)
                   
    
    DoCmd.OpenReport "rptTresReport", acViewPreview
    
    End Sub

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Orange, there was never a problem with the forming of the SQL string, only with the attempt to set a new SourceObject to use the modified SELECT.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bill,
    That was my point. I understand your SQL. I think Access thinks your strSQL is the name of your report or SourceObject.
    I think Access is confused, whether because of syntax or whatever, and is not seeing the SQL as you intended.
    My untested approach was to use a breakpoint, stop there and investigate some controls and properties. If you get a look at a few controls and values, you may see something and adjust.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I am interested in gaining more knowledge about what is happening, so I'll investigate further in a day or two. As I said earlier, time is of the essence so I had to move on for the time being.

    Also, it turns out that I need to know in the OnOpen event of the report if there are no records returned in the sub-reports, so a DCount in the updated SourceObject queries has become handy.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Please note that "Description" is a reserved word in Access.Should have brackets around it or change to "Desc" or "EntryDesc"...

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

Similar Threads

  1. Row source value list filter
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 01-27-2014, 10:19 PM
  2. Replies: 1
    Last Post: 11-28-2013, 10:03 PM
  3. Replies: 6
    Last Post: 06-22-2013, 08:10 PM
  4. Replies: 7
    Last Post: 10-28-2012, 02:55 PM
  5. Replies: 6
    Last Post: 05-05-2012, 08:43 AM

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