newbie needs help with vba code syntax

[kee2ka4]

Hey Guys,

I am new to vba and I need some help with writing the correct syntax for the code to work. Basically I have the following logic:

strSELECT = "SELECT qryReportBuilderNew.* "
strFROM = "FROM qryReportBuilderNew"
strWHERE = " WHERE qryReportBuilderNew.CompanyID = " & Me.Text10

strSQL = strSELECT & strFROM & strWHERE

CurrentDb.QueryDefs("qryReportBuilderNewTwo").SQL = strSQL

DoCmd.OpenReport "QueryReportOne", acViewPreview

I want to add an IF statement to check - If "qryReportBuilderNewTwo" doesn't return any results then run another query and view it in a report.

How would I code the logic in vba to make it work?

Thanks,
Ket

 

[CBrighton]

You could do it with a DAO recordset.

dim db as database
dim rst as recordset
set db = currentdb
set rst = db.openrecordset("qryReportBuilderNewTwo")
 
if rst.eof then
   '0 records
Else
   '1+ records
End if

 

[DCrake]

Should be

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rCnt As Long

set db = CurrentBb
set rst = db.OpenRecordset("qryReportBuilderNewTwo")

If Not Rst.EOF And Not Rst.BOF Then
   rst.MoveLast
   rCnt = rst.Recordcount
   rst.MoveFirst
End If

 

[CBrighton]

No need to check for BoF. If EoF is true when the recordset is loaded there is a 100% chance of zero records.

I vaguely remember having this discussion on this forum before

However adding a BoF check will not negatively effect it, so it's your call.