Counting records from a docmd.runsql string

[VegaLA]

Hi all.
I have a routine that loops through a table using a specific field and pulls the Daat from another table that will use the value of that field to export the related Data as a Excel sheet. It does this by writing to a temp table before exporting to a specified path as an excel file. So far so good but if there are no nmatches I want it to abort the operation and move onto the next source table value rather then running the RunSQL command to write 0 records to the temp table.
I have tried using dcount of the SQL string but as a SELECT statement (The Insert code is added after this point) but it does not like SELECTs for RunSQL, so I tried DoCmd.OpenQuery(strSQL) but still no joy.
My code is below, can anyone help me find a way of countign the records from the SQL string before it attempts to write the data to the table?

Thanks in advance,
Mitch...

Set db = CurrentDb
Set rs = db.OpenRecordset("QryCustomerName")

rs.MoveFirst
Do While Not rs.EOF
strFldrName = ""
strFldrName = rs!CNLocationname
strSQLIns = ""
strsqlfolder = "SELECT tblCustomers.FolderName, tblCutomers.CoName, tblCutomers.Conumber FROM tblCustomers WHERE (((tblCustomers.FolderName)='" & strFldrName & "'));"
Set rsFldr = db.OpenRecordset(strsqlfolder)
rsFldr.MoveFirst
Do While Not rsFldr.EOF
strInvref = ""
strInvref = rsFldr!CoNumber
MsgBox strInvref
strSQLIns = "INSERT INTO tblAATemp ( Field01, Field02,Field03 )"
strSQL = "SELECT tblSrcLive.CoNumber, tblSrcLive.field01, tblSrcLive.field02, tblSrcLive.field03, " & _
"tblSrcLive.field04, tblSrcLive.field05 " & _
"FROM tblSrcLive"
strSQL = strSQL & "WHERE (((tblSrcLive.CoNumber)=" & strInvref & "));"

strCount = dcount("CoNumber",strSQL)

strSQL = strSQLIns & strSQL
DoCmd.RunSQL strSQL
rsFldr.MoveNext
Loop
rsFldr.Close
rs.MoveNext
Loop
rs.Close

 

[MarkK]

I would refactor your code as follows...

[SIZE="1"][FONT="Verdana"]Private Sub FirstLoop()
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset

   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("QryCustomerName")
   With rst
      Do While Not .EOF
         SecondOperation dbs, !cnLocationName
         .MoveNext
      Loop
      .Close
   End With

End Sub

Private Sub SecondLoop(dbs As DAO.Database, FolderName As String)
   Dim rst As DAO.Recordset
   
   Set rst = dbs.OpenRecordset( _
      "SELECT FolderName, CoName, CoNumber " & _
      "FROM tblCustomers " & _
      "WHERE FolderName = '" & FolderName & "';")
   With rst
      Do While Not .EOF
         DoInsert dbs, !CoNumber
         .MoveNext
      Loop
      .Close
   End With

End Sub

Private Sub DoInsert(dbs As DAO.Database, CoNumber As Long)
   dbs.Execute _
      "INSERT INTO tblAATemp ( Field01, Field02, Field03 ) " & _
      "SELECT CoNumber, field01, field02, field03, field04, field05 " & _
      "FROM tblSrcLive " & _
      "WHERE CoNumber = " & CoNumber & ";"
      
End Sub[/FONT][/SIZE]

For me this makes each logical step in your process more distinct and therefore simpler. Do you see more clearly now where you might preform the logic to skip the insert if no records exist?

 

[VegaLA]

Thanks Lagbolt, yeah, that looks so much cleaner.

Cheers for that!!