If Query Returns No Resluts

I have a command button that runs a sql query and load the results in
a form (adapted from Allen Brown). Code work great except when the
query return no results. I get a blank form with no fields. How can I
adapt the code to open a msgbx when the query returns no results or
not found and return me to were I started.


My Code


DoCmd.OpenForm "frmSearchRequestID", , , , , acDialog


If IsLoaded("frmSearchRequestID") Then



GCriteria = Forms![frmGotoRequestID]![cboSearchField] & " = "
& Forms![frmSearchRequestID]![txtSearchString]

'Filter frmCustomers based on search criteria
Form_ frmRequest.RecordSource = "select * from tbl_Request
where " & GCriteria
Form_frmRequest.Caption = "Request Form (" &
Forms![frmSearchRequestID]![cboSearchField].value & " contains '*" &
Forms![frmSearchRequestID]![txtSearchString] & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearchRequestID"

MsgBox "Results have been filtered."


End If

End Sub


Thanks



Little Penny

hi Little Penny,
you can do a test to see if there any records matching the criteria.
Below is untested air code--

Dim lngCount as Long
GCriteria = Forms![frmGotoRequestID]![cboSearchField] & " = "
& Forms![frmSearchRequestID]![txtSearchString]

lngCount = DCount("*", "tblRequest", GCriteria)
If lngCount =0 Then
MsgBox "No results"
Else
'the code that opens the search form goes here
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"Little Penny" wrote in message
...
I have a command button that runs a sql query and load the results in
a form (adapted from Allen Brown). Code work great except when the
query return no results. I get a blank form with no fields. How can I
adapt the code to open a msgbx when the query returns no results or
not found and return me to were I started.


My Code


DoCmd.OpenForm "frmSearchRequestID", , , , , acDialog


If IsLoaded("frmSearchRequestID") Then



GCriteria = Forms![frmGotoRequestID]![cboSearchField] & " = "
& Forms![frmSearchRequestID]![txtSearchString]

'Filter frmCustomers based on search criteria
Form_ frmRequest.RecordSource = "select * from tbl_Request
where " & GCriteria
Form_frmRequest.Caption = "Request Form (" &
Forms![frmSearchRequestID]![cboSearchField].value & " contains '*" &
Forms![frmSearchRequestID]![txtSearchString] & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearchRequestID"

MsgBox "Results have been filtered."


End If

End Sub


Thanks



Little Penny

That did it

Thank you Jeanette





On Wed, 29 Apr 2009 08:28:44 +1000, "Jeanette Cunningham"
wrote:

Jeanette