"No Hits" Message for null recordset 1

[swaybright]

I was wondering if there is a good way to have a message box pop up when a search generates no hits.

I have a form with unbound controls that are used to populate the where clause of the recordsource of the data display form. a portion of that code is below. Any Ideas?

Thanks,
Shane

Dim sqlF As String
Dim sqlsel, sqlfrom, sqlwhere, sqlstate As String

If IsNull(Me!Formula) = True Then
sqlF = "((Formula) Like '*')"
Else
sqlF = StringLogic(Me!Formula, "Formula&quot
End If

sqlsel = "SELECT Formula"
sqlfrom = " FROM AddSearch"
sqlwhere = " WHERE (" & sqlF & &quot"

sqlstate = sqlsel & sqlfrom & sqlwhere

DoCmd.OpenForm "AdditiveView"
Forms!AdditiveView.RecordSource = sqlstate

 

[Hasu]

Hi

You can get message when searches hits no records. Just changes/add couples of places and it will works.
----------------------------------
Dim sqlF As String

If IsNull(Me!Formula) = True Then
sqlF = "((Formula) Like '*')"
Else
sqlF = StringLogic(Me!Formula, "Formula&quot
End If

DoCmd.OpenForm "AdditiveView",,,sqlF


*** in the "AdditiveView" Form_Open event,

If Me.Recordset.RecordCount = 0 Then MsgBox "No hits": DoCmd.Close acForm, "AdditiveView"

----------------------------------
and you set...




Hope this helps...
Hasu
(Trust on someone, someone will trust you!)

 

[swaybright]

Thanks Hasu!
The recordset.recordcount is the key!
I settled on replacing the last two lines of my code with

-----------
DoCmd.OpenForm "AdditiveView"
Forms!AdditiveView.RecordSource = sqlstate
If Forms!AdditiveView.Recordset.RecordCount = 0 Then
MsgBox "Your search resulted in no hits.", _
vbOKOnly + vbInformation, "No Hits"
DoCmd.Close acForm, "AdditiveView"
End If
-------------