Executing a Query on a form and checking for data

[mdweezer]

The problem I am having is when I execute a report with no data, I get a blank report. So i implemented on the "no data event" on the report a message box and then a process to kill the action and return to the form. This works flawlessly on my developer edition but when it is actually run on a users machine using the access runtimes it gets an error and exits the database.

Therefore on the form that calls the report, I would like to run the qry first, check for data and if there is no data use a msgbox to say no data and that would be it, there would be no report calling involved unless the qry returned data.

qry = "SELECT Issues.MILESTONE, TBL_MILESTONES.MILESTONES, Issues.[Status Code] AS "
qry = qry & "[Status Code1], Count(Issues.IssueNumber) AS [Total Of Status Code] "
qry = qry & "FROM Issues INNER JOIN TBL_MILESTONES ON Issues.MILESTONE = "
qry = qry & "TBL_MILESTONES.MILESTONES_ID "
qry = qry & "GROUP BY Issues.MILESTONE, TBL_MILESTONES.MILESTONES, Issues.[Status Code] "
qry = qry & "HAVING (((Issues.MILESTONE)= " & MilestoneValue & " ));"

There is the qry I run for the report. How do I run that qry on a form, check for data, if there is no data return a msgbox else if there is data call the report as I would and display it normally.

Thanks!

 

[Crowley16]

either have a query with:
SELECT COUNT(*) FROM yourQryName;
and if that = 0 then quit...

OR

you could do it by opening a recordset based on the query, and then quit if recordset.countrecords = 0

 

[mdweezer]

I understand what you're saying, i'm just not sure how to do that, or execute it in code...

All of this needs to happen on a command button click.

Could you elaborate a tad to help a newbie out?

Thanks.

 

[mik18]

Use an ADODB recordset.

    Dim qry As String
    Dim rst As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    
    qry = Your query    
    rst.Open qry, cnn, adOpenStatic, adLockReadOnly
    
    If rst.RecordCount = 0 Then
        Msgbox "No records to display.",vbInformation,"NO RECORDS"
        rst.Close
        cnn.Close
        Exit Sub
    Else
        docmd.OpenReport ....
    End If
    rst.Close
    cnn.Close

Good luck

-mike

 

[mdweezer]

Looks like it makes sense however I dont' think I have the correct reference libraries...

    Dim stDocName As String
    Dim qry As String
    Dim rst As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    
    MilestoneValue = cboMilestone.Value
    
    Set cnn = CurrentProject.Connection
    
    qry = "SELECT Issues.MILESTONE, TBL_MILESTONES.MILESTONES, Issues.[Status Code] AS "
    qry = qry & "[Status Code1], Count(Issues.IssueNumber) AS [Total Of Status Code] "
    qry = qry & "FROM Issues INNER JOIN TBL_MILESTONES ON Issues.MILESTONE = "
    qry = qry & "TBL_MILESTONES.MILESTONES_ID "
    qry = qry & "GROUP BY Issues.MILESTONE, TBL_MILESTONES.MILESTONES, Issues.[Status Code] "
    qry = qry & "HAVING (((Issues.MILESTONE)= " & MilestoneValue & " ));"
    
    rst.Open qry, cnn, adOpenStatic, adLockReadOnly
    
    If rst.RecordCount = 0 Then
        MsgBox "No status exist under this milestone.", vbInformation, "NO RECORDS"
        rst.Close
        cnn.Close
        Exit Sub
    Else
        stDocName = "RPT_Milestone_Status_Count"
        DoCmd.OpenReport stDocName, acPreview
    End If
    rst.Close
    cnn.Close

It errors out on:
Dim rst As New ADODB.Recordset saying "User defined type not defined"

Is this because i'm not using the ADODB references? I'm not too familiar with that sort of stuff, but if I add it to my reference list, how will that effect the user who will be running this app off of Access runtimes only?

 

[mdweezer]

Oh yeah, i'm using Access 2000.

I'm not too sure what version of the access runtimes the user is running.

 

[mik18]

You need to have Microsoft ActiveX Data Objects 2.1 Library checked as a reference. If the users with just Access runtime installed are getting that error, just copy that dll to their machines.
The file is located:
C:\Program Files\Common Files\System\ADO\msado21.dll

Good luck

mike