message if no matching data

[Mike Green]

Hi all
Below is the code that I use to carry out a search on my database. However
I am stuck on trying to pop up a simple message box if there are no records
found from the submitted search.
I have tried :-
If Frm_Search_Results.NoMatch Then
MsgBox "No record found."
Else
End If
At the end of the code below.

Could some one please point me in the right direction?

Thanks

Mike
*******************************************code******************************
Private Function SEARCH_DATABASE()

Dim StrSQL As String
'first iterate the sql string with the initial settings

StrSQL = "1 = 1 "
'find out what information is there
DoCmd.OpenForm "Frm_Search_Results"

If IsNull(Me.SCaravanMake) = False Then
StrSQL = StrSQL & "AND CaravanMake Like '" & Me.SCaravanMake & "*' "
End If

If IsNull(Me.SCustomer) = False Then
StrSQL = StrSQL & "AND CustomerSName Like '" & Me.SCustomer & "*' "
End If

If IsNull(Me.SCaravanRegNo) = False Then
StrSQL = StrSQL & "AND CaravanRegNo LIKE '" & Me.SCaravanRegNo & "*' "
End If


StrSQL = "select * from Qry_Caravan_Customer_plot where " & StrSQL
Forms![Frm_Search_Results].RecordSource = StrSQL

**************************************************************************************

 

[Jeff L]

Private Function SEARCH_DATABASE()

Dim StrSQL As String, rst as object
..
..
..
StrSQL = "select * from Qry_Caravan_Customer_plot where " & StrSQL
Set rst = CurrentDB.OpenRecordSet(StrSQL)

If rst.eof = False Then
'Records found
Forms![Frm_Search_Results].RecordSource = StrSQL
Else
MsgBox "No records found", vbOKOnly
End if

Hope that helps!

 

[Mike Green]

Thanks
That works a treat!


Mike

Private Function SEARCH_DATABASE()

Dim StrSQL As String, rst as object
.
.
.
StrSQL = "select * from Qry_Caravan_Customer_plot where " & StrSQL
Set rst = CurrentDB.OpenRecordSet(StrSQL)

If rst.eof = False Then
'Records found
Forms![Frm_Search_Results].RecordSource = StrSQL
Else
MsgBox "No records found", vbOKOnly
End if

Hope that helps!

Hi all
Below is the code that I use to carry out a search on my database.
However
I am stuck on trying to pop up a simple message box if there are no
records
found from the submitted search.
I have tried :-
If Frm_Search_Results.NoMatch Then
MsgBox "No record found."
Else
End If
At the end of the code below.

Could some one please point me in the right direction?

Thanks

Mike
*******************************************code******************************
Private Function SEARCH_DATABASE()

Dim StrSQL As String
'first iterate the sql string with the initial settings

StrSQL = "1 = 1 "
'find out what information is there
DoCmd.OpenForm "Frm_Search_Results"

If IsNull(Me.SCaravanMake) = False Then
StrSQL = StrSQL & "AND CaravanMake Like '" & Me.SCaravanMake & "*'
"
End If

If IsNull(Me.SCustomer) = False Then
StrSQL = StrSQL & "AND CustomerSName Like '" & Me.SCustomer & "*' "
End If

If IsNull(Me.SCaravanRegNo) = False Then
StrSQL = StrSQL & "AND CaravanRegNo LIKE '" & Me.SCaravanRegNo &
"*' "
End If


StrSQL = "select * from Qry_Caravan_Customer_plot where " & StrSQL
Forms![Frm_Search_Results].RecordSource = StrSQL

**************************************************************************************