Results 1 to 14 of 14
  1. #1
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    VBA for if report has no data that is not a On No Data event.

    I have a continuous form displaying a list of orders that when you click on a record, the corresponding report comes up with all order details.


    This report is based on an embedded query that pulls from multiple tables, so the report will only populate with data if each table has a related record.
    It happens often that users will try to open a report to find it completely blank.
    If the report is blank, I would like the report to close and a different form for editing to open, but I don't know how to use VBA to check if there is no data. If I try using IsNull() is says there is a type mismatch. I'm assuming that is because there is no record for a field to be null.

    The reason I don't want this to be an On No Data event is because there are different ways to open the report and I don't want the same thing to happen every time.

    This is what I've got so far: (I do know that I shouldn't have special characters in my form/report names, but I only learned that recently and haven't had time to change them all yet)
    Code:
    Private Sub RetailerID_Click()
        If [IsSample] = True Then
            DoCmd.OpenReport "rptSampleOrderConf", acViewNormal, , "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acWindowNormal
            
            If ( Report has no data ) Then
                DoCmd.OpenForm "F-OrderConf", acNormal, , "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acFormEdit, acWindowNormal
                DoCmd.Close acReport, "rptSampleOrderConf", acSavePrompt
            Else
            End If
        Else
            DoCmd.OpenReport "rptOrderConfPS1", acViewNormal, , "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acFormEdit, acWindowNormal
            
            If ( Report has no data ) Then
                DoCmd.OpenForm "F-OrderConf", acNormal, , "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acFormEdit, acWindowNormal
                DoCmd.Close acReport, "rptOrderConfPS1", acSavePrompt
            Else
            End If
        End If
        
        DoCmd.Close acForm, "F-OrderList", acSavePrompt
    End Sub
    Also, does this code tell it to print?! I can't stop it from printing all of a sudden. It just stopped opening the report at all and just prints a blank form. What is going on?

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can use this function (or just the Dcount) to check your query for recordcount.

    Code:
    Function fcnCountQueryRecs(qQuery as string) as long
        fcnCountQueryRecs = DCount("*", qQuery)
    end function

  3. #3
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    I came across that, but I'm not sure how to use it with an embedded query that doesn't have a name. Unless they do have names and I just don't know what it is.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think the "On No Data event" is the way to go, but there are alternatives.

    I prefer to use saved queries for form/report record sources because it is easier to check for errors and correct record selections.

    You could create two saved queries for the reports: "qrySampleOrderConf" and "qryOrderConfPS1".
    Then change your code to something like:
    Code:
    Private Sub RetailerID_Click()
        Dim bHasRecs As Boolean
    
        'set default value
        bHasRecs = False
    
        If [IsSample] = True Then
            bHasRecs = fcnRptHasRecs("qrySampleOrderConf")
    
            If bHasRecs Then
                DoCmd.OpenForm "F-OrderConf", acNormal, , "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acFormEdit, acWindowNormal
                ' DoCmd.Close acReport, "rptSampleOrderConf", acSavePrompt    '<<<---this line is not needed now
            Else
                DoCmd.OpenReport "rptSampleOrderConf", acViewNormal, , "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acWindowNormal
            End If
        Else
            bHasRecs = fcnRptHasRecs("qryOrderConfPS1")
    
            If bHasRecs Then
                DoCmd.OpenForm "F-OrderConf", acNormal, , "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acFormEdit, acWindowNormal
                '  DoCmd.Close acReport, "rptOrderConfPS1", acSavePrompt  '<<<---this line is not needed now
            Else
                DoCmd.OpenReport "rptOrderConfPS1", acViewNormal, , "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acFormEdit, acWindowNormal
            End If
        End If
    
        DoCmd.Close acForm, "F-OrderList"       ', acSavePrompt      '<<<---parameter not needed
    
    End Sub
    
    '------------------------------------------------------
    
    Function fcnRptHasRecs(qQuery As String) As Boolean
        Dim r As DAO.Recordset
    
        fcnRptHasRecs = False
        
        Set r = CurrentDb.OpenRecordset(qQuery)
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
            fcnRptHasRecs = (r.RecordCount > 0)
        End If
        r.Close
    
        'clean up
        Set r = Nothing
    End Function
    Since the query is opened to check for records and the opening the report depends on there being records, it kind of works like the "On No Data event".


    BTW, you had (have) two lines in your code that don't do what you might think they do.
    Code:
    DoCmd.Close acReport, "rptOrderConfPS1", acSavePrompt
    DoCmd.Close acForm, "F-OrderList", acSavePrompt
    The syntax of this command is
    expression.Close(ObjectType, ObjectName, Save)

    The "Save" parameter does not save the data entered, it is for changes to the DESIGN of the form/report.

    From Help:
    Save Parameter (Optional) = A AcCloseSave constant that specifies whether or not to save changes to the object. The default value is acSavePrompt.

    Using VBA, you can open a form/report in design view (hidden or visible) and change the design of the object; change size, BG color, add controls, move controls, etc. When you close the object, you would have the option to save or discard the changes to the object.




    My $0.02........

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This function will create a named query from an SQL string (the recordsource query for your report). It will be used only once as below.
    Put this code in a module. In the immediate window, type:
    Call fcnCustomizeSQL("qtempQuery","SELECT.......copy the report's recordsource query string here")
    A new query should appear named qtempQuery. Run it to be sure it works.
    Change the recordsource for your report from the SQL string to qtempQuery, your new named query.

    Code:
    Function fcnCustomizeSQL(qName As String, strPassedSQL As String)
        Dim qthisQuery As DAO.QueryDef
        'if the query has been deleted, create it
        If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
            Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
            Set qthisQuery = Nothing
            Exit Function
        End If
        'else modify it
        Set qthisQuery = CurrentDb.QueryDefs(qName)
        qthisQuery.SQL = strPassedSQL
        Set qthisQuery = Nothing
    end function
    Last edited by davegri; 06-13-2017 at 04:30 PM. Reason: report

  6. #6
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Okay, a couple more bugs.

    I did create the two queries separately from the reports.
    When I click on a record where there is no data, it works correctly, bringing up the editing form.
    When I click on a record with data where [IsSample]=True , I get an error 3061 "Too few parameters. Expected 1". The debugger highlights this line:
    Code:
    Set r = CurrentDb.OpenRecordset(qQuery)
    When I click on a record with data where [IsSample]=False, it also brings up the editing form.

    I'm not sure if this makes a difference, but the report filters the query, so if I were to just open the query there would be records, even if the report doesn't show them. Would this mean that the record count would not ever be zero?

  7. #7
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Also, weird thing just happened. I thought that the "If bHasRecs Then" line might need to be "If bHasRecs = 0 Then" but when I did that, and click the button, it prints the report and doesn't open anything. Any idea what that's about?

  8. #8
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Dang it! Now it won't do anything but print! What is happening?!

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Oops, my bad... Computer doesn't know how to type what I want...
    Try
    Code:
    Set r = CurrentDb.OpenRecordset("qQuery")
    Add double quotes around qQuery....


    "bHasRecs" is declared as A Boolean type variable,
    "If bHasRecs Then" is short hand for "If bHasRecs = TRUE Then"



    I sure messed this up. I had the logic backwards (copied the wrong version of the procedure!)
    Logic should now be correct.....
    Code:
    Private Sub RetailerID_Click()
        Dim bHasRecs As Boolean
    
        'set default value
        bHasRecs = False
    
        If [IsSample] = True Then
            bHasRecs = fcnRptHasRecs("qrySampleOrderConf")
    
            If bHasRecs = TRUE Then  'open report
                '   DoCmd.OpenReport "rptSampleOrderConf", acViewNormal, ,  "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acWindowNormal
                DoCmd.OpenReport "rptSampleOrderConf", acViewPreview, ,   "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum]
            Else   'open form
                DoCmd.OpenForm "F-OrderConf", acNormal, , "[ConfNum] ="  & [Forms]![F-OrderList]![ConfNum], acFormEdit, acWindowNormal
            End If
        Else
            bHasRecs = fcnRptHasRecs("qryOrderConfPS1")
    
            If bHasRecs = TRUE Then   'open report
               '     DoCmd.OpenReport "rptOrderConfPS1", acViewNormal, ,  "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum], acFormEdit,  acWindowNormal
                DoCmd.OpenReport "rptOrderConfPS1", acViewPreview, ,   "[ConfNum] =" & [Forms]![F-OrderList]![ConfNum]
            Else 'open form
                DoCmd.OpenForm "F-OrderConf", acNormal, , "[ConfNum] ="  & [Forms]![F-OrderList]![ConfNum], acFormEdit, acWindowNormal
            End If
        End If
    
        DoCmd.Close acForm, "F-OrderList" 
    End Sub
    
    '------------------------------------------------------
    
    Function fcnRptHasRecs(qQuery As String) As Boolean
        Dim r As DAO.Recordset
    
        fcnRptHasRecs = False
        
        Set r = CurrentDb.OpenRecordset("qQuery")   '<<<--
        If Not r.BOF And Not r.EOF Then
            r.MoveLast
            r.MoveFirst
            fcnRptHasRecs = (r.RecordCount > 0)
        End If
        r.Close
    
        'clean up
        Set r = Nothing
    End Function
    Edit: I would use "acViewPreview" instead of acViewNormal. See changes in the code above
    Last edited by ssanfu; 06-13-2017 at 05:42 PM. Reason: edited parameters

  10. #10
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Great
    I'll have to wait for morning to give it a try.

  11. #11
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    The debugger still highlights the same line: Set r = CurrentDb.OpenRecordset("qQuery"). I get error 3078 saying that it can't find a table or query named 'qQuery'... .

    Also, apparently acViewNormal means "Don't view the report normally or at all, just print."
    I have buttons on the report so I wanted to change it from acViewPreview, but now I've got it at acViewReport. That works much better. No printing.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    qQuery is an argument string in function fcnRptHasRecs. It holds the value of the query name, as called by: bHasRecs = fcnRptHasRecs("qryOrderConfPS1")
    So it should not be in quotes as below.
    Code:
    Set r = CurrentDb.OpenRecordset(qQuery)
    .
    Do you actually have the named query
    qryOrderConfPS1? Does it run on its own without errors?
    Last edited by davegri; 06-14-2017 at 08:45 AM. Reason: clarif

  13. #13
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    So if I take it out of quotations, the same thing happens as before.
    If I click a record where [IsSample] is false and there are records, it works great and opens the report.
    If I click a record where [IsSample] is false and there are no records, it brings up the blank report (what I wanted to avoid).
    If I click a record where [IsSample] is true, regardless of if there are records or not, I get error 3061 "Too few parameters. Expected 1." Then if I click to debug, it highlights that same line of code.

    Earlier in the conversation I created the query, which I actually called qryOrderConf since the "PS1" part was to differentiate it from another table/form that I since deleted.
    It does run without errors, however I think it may have a hitch in it since the query always runs with many records, and it is the report itself that filters them. The report always opens with a criteria/where clause that is [ConfNum]=[Forms]![name of the one that's open]![ConfNum]. So if the query itself always has records the record count would never be zero. Unless I put the criteria in the query itself. I think I may go ahead and try to do that.

  14. #14
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    The query having records was definitely the problem!
    I expanded on a function I already had to check if a form or report was open and get the ConfNum from there.

    Code:
    Public Function GetValue()
        If CurrentProject.AllForms("F-OrderConf").IsLoaded = True Then
            GetValue = Forms![F-OrderConf]![ConfNum]
        ElseIf CurrentProject.AllForms("F-OrderList").IsLoaded = True Then
                GetValue = Forms![F-OrderList]![ConfNum]
        ElseIf CurrentProject.AllReports("rptSampleOrderConf").IsLoaded = True Then
                GetValue = Reports![rptSampleOrderConf]![ConfNum]
        ElseIf CurrentProject.AllReports("rptOrderConfPS1").IsLoaded = True Then
                GetValue = Reports![rptOrderConfPS1]![ConfNum]
        Else
            GetValue = InputBox("Enter Confirmation Number")
        End If
    
    
    End Function
    Then I put the function directly in each query's criteria for ConfNum.
    Now everything works like a charm! Without the quotation marks around qQuery.

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

Similar Threads

  1. Replies: 6
    Last Post: 06-24-2016, 06:59 AM
  2. Replies: 4
    Last Post: 04-02-2014, 09:03 AM
  3. Replies: 6
    Last Post: 09-24-2013, 06:02 PM
  4. Replies: 27
    Last Post: 08-14-2012, 09:05 AM
  5. On No Data event for sub-reports
    By Ashe in forum Forms
    Replies: 2
    Last Post: 12-09-2011, 12:11 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