Access.2007.VBA.Bibl..

.MoveFirst

.MoveLast

CreateAndTestQuery = .RecordCount

End With

ErrorHandlerExit:

Exit Function

ErrorHandler:

If Err.Number = 3021 Then

CreateAndTestQuery = 0

Resume ErrorHandlerExit

Else

MsgBox “Error No: “ & Err.Number _

& “; Description: “ & Err.Description

Resume ErrorHandlerExit

End If

End Function

Here is a typical code segment using the CreateAndTestQuery function:

strInventoryCode = Me![InventoryCode]

strQuery = “qryTemp”

Set dbs = CurrentDb

strSQL = “SELECT * FROM tblInventoryItemsComponents _

WHERE [InventoryCode] = “ & Chr$(39) _

& strInventoryCode & Chr$(39) & “;”

Debug.Print “SQL for “ & strQuery & “: “ & strSQL

lngCount = CreateAndTestQuery(strQuery, strSQL)

Debug.Print “No. of items found: “ & lngCount

If lngCount = 0 Then

strPrompt = “No records found; canceling”

strTitle = “Canceling”

MsgBox strPrompt, vbOKOnly + vbCritical, strTitle

GoTo ErrorHandlerExit

Else

‘Further code here to work with the newly created query

End If

The code creates a SQL string filtered by a value picked up from a form, and uses that string and a
query name as arguments for the CreateAndTestQueryfunction. That function returns the num-
ber of records; if there are no records (the function returns zero), the code exits; otherwise, it can
continue to perform some action on the query created by the CreateAndTestQueryfunction.

You can also create a QueryDef and use it directly to create a recordset, as in the following line
of code:

Set rst = qdf.OpenRecordset

Working with Access Data 5