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!?

Expand|Select|Wrap|Line Numbers

  
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.

Expand|Select|Wrap|Line Numbers

 
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.

Expand|Select|Wrap|Line Numbers

 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

Use SQL in VBA to pass a number into a MsgBox

re: Use SQL in VBA to pass a number into a MsgBox

re: Use SQL in VBA to pass a number into a MsgBox

re: Use SQL in VBA to pass a number into a MsgBox

re: Use SQL in VBA to pass a number into a MsgBox

Similar topics

More channels