Count records in an SQL string

[Ian Chappel]

I have code-generated SQL string, which is to be the RecordSource of a
form.

How do I do a record count before I open the form (I want to avoid opening
if there are no records)?

 

[Allen Browne]

Cancel the Open event of the form if there are no records.

Something like this:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No records"
End If
End Sub

 

[Ian Chappel]

Thanks Allen,

Sorry, I wasn't accurate with my question.

The form's already open. I want to prevent the form's RecordSource being
changed when I click a cmdButton if the new RecordSource would have no
records. So I think I need to count the records as a result of my strSQL
_before_ it becomes the form's RecordSource?

 

[Allen Browne]

If you are building the SQL string dynamically, then you can build the WHERE
clause separately. Then use it in as the Criteria of a DLookup() to see if
any record is returned.

It is possible to respond to the Filter event after the fact, but a
DLookup() sounds like what you are after.

As a worst case, you could assign the SQL string to the SQL property of a
QueryDef you saved for this purpose, and then see if this query generates
any results.

 

[Ian Chappel]

Thanks Allen, All sorted now.

If you are building the SQL string dynamically, then you can build the
WHERE clause separately. Then use it in as the Criteria of a DLookup() to
see if any record is returned.

It is possible to respond to the Filter event after the fact, but a
DLookup() sounds like what you are after.

As a worst case, you could assign the SQL string to the SQL property of a
QueryDef you saved for this purpose, and then see if this query generates
any results.