Use SQL in VBA to pass a number into a MsgBox

[Jamie1977]

Sorry, if covered before but I'm struggling with something I think should be very easy.

I have a form which exports data from a query. I just want to run an SQLstr that counts record in the query so as to present a Msgbox saying "x number of records exported"

The code below just returns a zero records even though it is 250!?

Private Sub Command261_Click()

    Dim SQLStr As String
    Dim NoOfDataRows As Integer
        
    SQLStr = "SELECT Count(Qry_Dist1_Z_Desp_Total_Cost_2.Sub_Cat) AS CountOfSub_Cat " & _
             "FROM Qry_Dist1_Z_Desp_Total_Cost_2;"
    
    Export_No.Value = SQLStr
    Export_No.Requery
        
    NoOfDataRows = Export_No.Value
    MsgBox "Export Complete...." & vbCrLf & NoOfDataRows & " Rows Exported", vbOKOnly, "Computer Says...."

End Sub

[MMcCarthy]

This sql string doesn't do anything. If you need a count of rows in a query something like the following should work.

Private Sub Command261_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQLStr As String
Dim NoOfDataRows As Integer
 
    SQLStr = "SELECT Count(Qry_Dist1_Z_Desp_Total_Cost_2.Sub_Cat) AS CountOfSub_Cat " & _
             "FROM Qry_Dist1_Z_Desp_Total_Cost_2;"
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SQLStr)
    
    rs.MoveLast
    rs.MoveFirst
    
    NoOfDataRows = rs.RecordCount
 
    Export_No.Value = NoOfDataRows
    
    MsgBox "Export Complete...." & vbCrLf & NoOfDataRows & " Rows Exported", vbOKOnly, "Computer Says...."
 
End Sub

By the way to enclose your code in a code box just put [CODE] before the start of your code and [ /CODE] (remove space) after your code.

[MikeTheBike]

Hi

Without knowing how you exported the records, based on your code posted, I would suggerst this as the simplest solution without using a recordset.

Dim NoOfDataRows As Integer

NoOfDataRows = DCount("Sub_Cat","Qry_Dist1_Z_Desp_Total_Cost_2")

MsgBox "Export Complete...." & vbCrLf & NoOfDataRows & " Rows Exported", vbOKOnly, "Computer Says...."

??

MTB

[MMcCarthy]

Even simpler Mike :)

[Jamie1977]

Mike, thanks a lot, that's amazingly simple......... ..when you know how !

MSquared, thanks for posting tip.

Will no doubt be on again soon