Results 1 to 15 of 15
  1. #1
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56

    Report error Can't find the field 'Report' referred to in your expression.

    I have form with a tab control on each tab I am using a sub report widget and have embedded a report, there are 5 different tabs each with a report.

    The user can select a ship start and end date for the query.

    in my code behind when I run and select a ship (dates are not set yet). I get the error
    "Can't find the field 'Report' referred to in your expression."

    This is the name of the report in the Objects are "Report.qryTblAuditsubreport.RecordSource"
    The name in the properties Object field appears as "Report.qryTblAuditsubreport"

    I have tested the sql and it runs, so I cannot figure out what this error means. Maybe I am using the record source wrong?




    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim strSQL As String


    strSQL = " Select strShipname, strChangeType, strTableName, NumRecordID, StrFieldName, StrOldValue, StrNewValue, memChangeMemo, strChangedBy, dtChangedDate "
    strSQL = strSQL & " FROM tblAuditTrail INNER JOIN (tblShip INNER JOIN tblEquip ON tblShip.keyShip = tblEquip.keyShip) "
    strSQL = strSQL & " ON tblAuditTrail.numRecordID = tblEquip.keyEquip where tblShip.keyship = " & Me.cmbShip.value

    txtsql.value = strSQL


    ' Reports!Report.qryTblAuditsubreport.RecordSource = strSQL
    Me!Report!qryTblAuditsubreport.RecordSource = strSQL

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have a report named qryTblAuditsubreport?

    First time I've heard a subform/subreport container control referred to as a 'widget'.

    What is name of the container control that holds the report? I always name container different from object it holds, such as ctrAudit. Reference form/report through the container control. Don't actually use the report name. Code:

    Me.ctrAudit.Report.RecordSource = strSQL
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Sorry I placed the report directly on the tab control. The name for Properties Name of this Sub-report is qryTblAuditsubreport the same as the query itself.
    The source object for the Subreport is
    Report.qryTblAuditsubreport
    and the reports record source is qryTblAuditReport.

    I wanted to open the reports on tabs so it's not over whelming for the user.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Still involves a container control. Sounds like you have a query, report, container all with the same name. Suggest you give each a different name.

    Try the RowSource as described.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Ok so I deleted the report from my tab control and draged a new copy over to the tab
    The Source Object shows as : Report.ReportShipChangeAudit
    Name: qryTblEquipAudit subreport
    Record Source: qryTblAuditReport
    Tab Name: tabReports

    Still get the same error. I guess I'm doing something completely wrong

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    A totally different approach (easier, in my opinion) to to create customized named queries, like qtempQuery. Make all the reports have the same recordsource, qtempQuery.
    Now all the code has to do is create the proper qtempQuery for the given report. The below code will create a named query from an SQL string.
    Then your code needs to simply call this function, then open the report.
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : fcnMakeNamedQryFromSQLString
    ' DateTime  : 9/26/2006 20:57
    ' Author    : DaveGri
    ' Purpose   : Create named query from SQL string
    ' sName is the name to be given to the named query. 
    ' sSQL is the query string used to create the named query.
    '---------------------------------------------------------------------------------------
    '
    Public Function fcnMakeNamedQryFromSQLString(qName As String, qSQL As String)
        Dim Qdf As DAO.QueryDef
        Dim Db As DAO.Database
        Dim qExists As Boolean
        Set Db = CurrentDb
        qExists = False
        For Each Qdf In Db.QueryDefs
            If Qdf.Name = qName Then
                qExists = True
                Exit For
            End If
        Next
        If qExists Then
            Qdf.SQL = qSQL
        Else
            Set Qdf = Db.CreateQueryDef(qName)
            Qdf.SQL = qSQL
        End If
        Application.RefreshDatabaseWindow
        Set Db = Nothing
        Set Qdf = Nothing
    End Function
    

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested this in my db and the RecordSource did change.

    You revised code as suggested? Post the current code.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Below are my properties of the controls and my code as well. Maybe a report can only have a record source passed to it before it opens?







    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim strSQL As String


    strSQL = " Select strShipname, strChangeType, strTableName, NumRecordID, StrFieldName, StrOldValue, StrNewValue, memChangeMemo, strChangedBy, dtChangedDate "
    strSQL = strSQL & " FROM tblAuditTrail INNER JOIN (tblShip INNER JOIN tblEquip ON tblShip.keyShip = tblEquip.keyShip) "
    strSQL = strSQL & " ON tblAuditTrail.numRecordID = tblEquip.keyEquip where tblShip.keyship = " & Me.cmbShip.value

    txtsql.value = strSQL


    ' Reports!Report.qryTblAuditsubreport.RecordSource = strSQL
    Report![ReportShipAudit].RecordSource = strSQL

  9. #9
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    I thought I would add what I am trying to do.

    So the user access the reports on the Reports form. which has a tab control there are about 5. At the top of the form the user can choose his selection criteria and a report will open on each tab. Now the user decides to query something else I want the reports to change. The only code I can find opens the reports outside of the tab.

    Maybe you cannot embed reports on tabs and change the record set???/I'm not really sure. This should have taken me 5 minutes, but there doesn't seem to be anyway to make the report requery itself let alone change the recordset.

    I could use the DoCmd.OpenReport stDocName, acViewPreview, but this launches the report outside of the tab control. which is useless for this project.

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    gw-davegri-v01.zip
    Here's an example of what I suggested in post #6. The example has the report showing on the first tab, and the report will dynamically change as you click the option buttons on the main form.
    It's unclear to me what the other reports appearing on other tabs might be, relating to the criteria on the main form.
    Last edited by davegri; 05-03-2019 at 12:12 PM. Reason: additional comment

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe Dave's example will resolve issue.

    However, as I said, your code is not correct syntax for referencing subform/subreport. You did not change as suggested in post 2.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    There appears to be a timing issue in the DB attachment in Post#10. It can be fixed by the red code mod below.

    Code:
    Private Sub Frame4_AfterUpdate()
        Dim sSQL As String
        Select Case Me.Frame4.Value
            Case 1
                sSQL = "SELECT Members_PK, tblMembers.FirstName, tblMembers.LastName " _
                & "FROM tblMembers " _
                & "WHERE (((tblMembers.LastName) Like '*A*'));"
                Call fcnMakeNamedQryFromSQLString("qtempQuery", sSQL)
                DoEvents
                Me.Page1Report.SourceObject = "Report.rptMembers"
            Case 2
                sSQL = "SELECT Members_PK, tblMembers.FirstName, tblMembers.LastName " _
                & "FROM tblMembers " _
                & "WHERE (((tblMembers.LastName) Like '*B*'));"
                Call fcnMakeNamedQryFromSQLString("qtempQuery", sSQL)
                DoEvents
                Me.Page1Report.SourceObject = "Report.rptMembers"
            Case 3
                sSQL = "SELECT Members_PK, tblMembers.FirstName, tblMembers.LastName " _
                & "FROM tblMembers " _
                & "WHERE (((tblMembers.LastName) Like '*S*'));"
                Call fcnMakeNamedQryFromSQLString("qtempQuery", sSQL)
                DoEvents
                Me.Page1Report.SourceObject = "Report.rptMembers"
            Case 4
                sSQL = "SELECT Members_PK, tblMembers.FirstName, tblMembers.LastName " _
                & "FROM tblMembers " _
                & "WHERE (((tblMembers.LastName) Like '*R*'));"
                Call fcnMakeNamedQryFromSQLString("qtempQuery", sSQL)
                DoEvents
                Me.Page1Report.SourceObject = "Report.rptMembers"
        End Select
    End Sub
    

  13. #13
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Thank you very much that is exactly what I wanted to do.

    have a nice weeknd

    Quote Originally Posted by davegri View Post
    gw-davegri-v01.zip
    Here's an example of what I suggested in post #6. The example has the report showing on the first tab, and the report will dynamically change as you click the option buttons on the main form.
    It's unclear to me what the other reports appearing on other tabs might be, relating to the criteria on the main form.

  14. #14
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Thank you very much that is exactly what I wanted to do.
    Glad to help. Hope you can work it out with the additional tabs/reports.

  15. #15
    greatwhite is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2019
    Location
    Ottawa, ON
    Posts
    56
    Everyone thank you for your help. I realize my description of the issue is not always correct. Generally I use layman terms. This helped on the project quite well. Now I just have to figure out how to connect the dots between the Audit table and the actual changes made in the DB. Whoever developed this I would have preferred a different auditing system and the schema itself I cannot actually put on here to show what I am doing I have part of the SQL worked out.

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

Similar Threads

  1. Replies: 5
    Last Post: 05-04-2019, 09:02 PM
  2. Replies: 3
    Last Post: 10-10-2014, 06:29 PM
  3. Replies: 1
    Last Post: 12-01-2012, 12:44 PM
  4. Replies: 5
    Last Post: 06-09-2012, 12:37 PM
  5. Replies: 5
    Last Post: 03-13-2012, 11:53 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