Message to Popup That Data Not Found

  • Thread starter Lirva S via AccessMonster.com
  • Start date
L

Lirva S via AccessMonster.com

I have the following code on a "Search" command button. The code works fine
except that I'm not sure where the put the part that advises the user that
the data they entered in the txtSearch is not found. I've tried placing it
in many different parts of the code but can't seem to get it to work.

Can someone tell me where to it shoud go. Thanks!
------------------------------------------------------

Private Sub cmdSearch_Click()
'On Error GoTo Err_cmdSearch_Click
Dim strSQL As String
Dim strTextEntry As String

'*** create the string based on the information on the form
strSQL = ""

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Else

strTextEntry = txtSearch

MsgBox strTextEntry & " is not found.", , "Sorry!"
txtSearch.SetFocus
End If

' The fields to search
strSQL = strSQL & "[StudentID] Like '*" & Me![txtSearch] & "*' OR [Student]
Like '*" & Me![txtSearch] & "*' OR [Status] Like '*" & Me![txtSearch] & "*'"

'Data source
strSQL = "SELECT qryStudent.* FROM qryStudent WHERE " & strSQL
 
D

Dirk Goldgar

Lirva S via AccessMonster.com said:
I have the following code on a "Search" command button. The code
works fine except that I'm not sure where the put the part that
advises the user that the data they entered in the txtSearch is not
found. I've tried placing it in many different parts of the code but
can't seem to get it to work.

Can someone tell me where to it shoud go. Thanks!
------------------------------------------------------

Private Sub cmdSearch_Click()
'On Error GoTo Err_cmdSearch_Click
Dim strSQL As String
Dim strTextEntry As String

'*** create the string based on the information on the form
strSQL = ""

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!" Me![txtSearch].SetFocus
Else

strTextEntry = txtSearch

MsgBox strTextEntry & " is not found.", , "Sorry!"
txtSearch.SetFocus
End If

' The fields to search
strSQL = strSQL & "[StudentID] Like '*" & Me![txtSearch] & "*' OR
[Student] Like '*" & Me![txtSearch] & "*' OR [Status] Like '*" &
Me![txtSearch] & "*'"

'Data source
strSQL = "SELECT qryStudent.* FROM qryStudent WHERE " & strSQL
Click to expand...

Where's the rest of it? There's nothing there that actually performs a
search, filter, or query operation of any kind, so it's impossible to
know yet whether the record being sought is going to be found.
 
L

Lirva S via AccessMonster.com

This is the entire code on the command button

------------------ CODE ----------------------------------

Private Sub cmdSearch_Click()
'On Error GoTo Err_cmdSearch_Click
Dim strSQL As String
Dim strTextEntry As String

'*** create the string based on the information on the form
strSQL = ""

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Else

strTextEntry = txtSearch

MsgBox strTextEntry & " is not found.", , "Sorry!"
txtSearch.SetFocus
End If

' The fields to search
strSQL = strSQL & "[StudentID] Like '*" & Me![txtSearch] & "*' OR [Student]
Like '*" & Me![txtSearch] & "*' OR [Status] Like '*" & Me![txtSearch] & "*'"

'Data source
strSQL = "SELECT qryStudent.* FROM qryStudent WHERE " & strSQL

'*** set the record source
Debug.Print strSQL
Me.RecordSource = strSQL
Me.Requery

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click
'End If
End Sub
 
G

Gijs Beukenoot

Lirva S via AccessMonster.com gebruikte zijn klavier om te schrijven :
This is the entire code on the command button

------------------ CODE ----------------------------------

Private Sub cmdSearch_Click()
'On Error GoTo Err_cmdSearch_Click
Dim strSQL As String
Dim strTextEntry As String

'*** create the string based on the information on the form
strSQL = ""

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Else

strTextEntry = txtSearch

MsgBox strTextEntry & " is not found.", , "Sorry!"
txtSearch.SetFocus
End If

' The fields to search
strSQL = strSQL & "[StudentID] Like '*" & Me![txtSearch] & "*' OR [Student]
Like '*" & Me![txtSearch] & "*' OR [Status] Like '*" & Me![txtSearch] & "*'"

'Data source
strSQL = "SELECT qryStudent.* FROM qryStudent WHERE " & strSQL

'*** set the record source
Debug.Print strSQL
Me.RecordSource = strSQL
Me.Requery
Click to expand...

if me.eof then
msgbox "No recods with those criteria...", vbinformation
endif
Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click
'End If
End Sub
Click to expand...

I think you'll need those lines there to check it
 
D

Dirk Goldgar

message [...]
strSQL = "SELECT qryStudent.* FROM qryStudent WHERE " & strSQL

'*** set the record source
Debug.Print strSQL
Me.RecordSource = strSQL
Me.Requery
Click to expand...

Remove this line:
Me.Requery
Click to expand...

Setting the form's RecordSource automatically requeries it, so you don't
need to do it again.

After changing the recordsource, check to see if the form's recordset
has any records:

Me.RecordSource = strSQL

If Me.Recordset.RecordCount = 0 Then
MsgBox "No records"
End If

Note: if you're using Access 97, you'll have to use the form's
RecordsetClone instead:

If Me.RecordsetClone.RecordCount = 0 Then
 
D

Dirk Goldgar

Gijs Beukenoot said:
if me.eof then
msgbox "No recods with those criteria...", vbinformation
endif
Click to expand...

That's the right idea, but the Form object has no EOF property. You
could conceivably check the EOF property of the form's Recordset.
 
L

Lirva S via AccessMonster.com

I'm getting a compile error, method or data member not found.
Can you clarify as to where in the code I should put this line.

Thanks again.

Gijs said:
Lirva S via AccessMonster.com gebruikte zijn klavier om te schrijven :
This is the entire code on the command button
Click to expand...
[quoted text clipped - 31 lines]
Me.RecordSource = strSQL
Me.Requery
Click to expand...

if me.eof then
msgbox "No recods with those criteria...", vbinformation
endif
Exit_cmdSearch_Click:
Exit Sub
Click to expand...
[quoted text clipped - 4 lines]
'End If
End Sub
Click to expand...

I think you'll need those lines there to check it
Click to expand...
 
D

Dirk Goldgar

Gijs Beukenoot said:
Ah, yes, sorry. I need a newsgroup-editor with (preferably
configurable, but al least VB and Access) intellisense :)
Click to expand...

Wouldn't that be great!
 
L

Lirva S via AccessMonster.com

Thanks Dirk,

I'm getting closer. I'm getting the message "Invalid use of Null" on this
line of code:
strTextEntry = txtSearch

This happens if nothing is entered in the txtSearch and the user clicks on
the find command button. How can I handle this message?

Here's the edited code:

-------------- CODE -----------------------
Private Sub cmdSearch_Click()
'On Error GoTo Err_cmdSearch_Click
Dim strSQL As String
Dim strTextEntry As String

'*** create the string based on the information on the form
strSQL = ""

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
End If

strTextEntry = txtSearch

' The fields to search
strSQL = strSQL & "[StudentID] Like '*" & Me![txtSearch] & "*' OR [Student]
Like '*" & Me![txtSearch] & "*' OR [Status] Like '*" & Me![txtSearch] & "*'"

'Data source
strSQL = "SELECT qryStudent.* FROM qryStudent WHERE " & strSQL

'*** set the record source
Debug.Print strSQL
Me.RecordSource = strSQL

If Me.Recordset.RecordCount = 0 Then
MsgBox strTextEntry & " is not found.", , "Sorry!"
End If

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click
End Sub
------------------- CODE ----------------

Dirk said:
[...]
strSQL = "SELECT qryStudent.* FROM qryStudent WHERE " & strSQL

'*** set the record source
Debug.Print strSQL
Me.RecordSource = strSQL
Me.Requery
Click to expand...

Remove this line:
Me.Requery
Click to expand...

Setting the form's RecordSource automatically requeries it, so you don't
need to do it again.

After changing the recordsource, check to see if the form's recordset
has any records:

Me.RecordSource = strSQL

If Me.Recordset.RecordCount = 0 Then
MsgBox "No records"
End If

Note: if you're using Access 97, you'll have to use the form's
RecordsetClone instead:

If Me.RecordsetClone.RecordCount = 0 Then
Click to expand...
 
D

Dirk Goldgar

Lirva S via AccessMonster.com said:
Thanks Dirk,

I'm getting closer. I'm getting the message "Invalid use of Null" on
this line of code:
strTextEntry = txtSearch

This happens if nothing is entered in the txtSearch and the user
clicks on the find command button. How can I handle this message?

Here's the edited code:

-------------- CODE -----------------------
Private Sub cmdSearch_Click()
'On Error GoTo Err_cmdSearch_Click
Dim strSQL As String
Dim strTextEntry As String

'*** create the string based on the information on the form
strSQL = ""

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
Criterion!" Me![txtSearch].SetFocus
End If

strTextEntry = txtSearch

' The fields to search
strSQL = strSQL & "[StudentID] Like '*" & Me![txtSearch] & "*' OR
[Student] Like '*" & Me![txtSearch] & "*' OR [Status] Like '*" &
Me![txtSearch] & "*'"

'Data source
strSQL = "SELECT qryStudent.* FROM qryStudent WHERE " & strSQL

'*** set the record source
Debug.Print strSQL
Me.RecordSource = strSQL

If Me.Recordset.RecordCount = 0 Then
MsgBox strTextEntry & " is not found.", , "Sorry!"
End If

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click
End Sub
------------------- CODE ----------------
Click to expand...

I see that you already have code in there to check for that, but after
displaying its error message, it goes on with the rest of the procedure.
Insert a line as shown below:
'Check txtSearch for Null value or Null Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, _
"Invalid Search Criterion!"
Me![txtSearch].SetFocus Exit Sub
End If
Click to expand...
 
L

Lirva S via AccessMonster.com

Thank you so much Dirk - it's working great!!

Dirk said:
Thanks Dirk,
Click to expand...
[quoted text clipped - 48 lines]
End Sub
------------------- CODE ----------------
Click to expand...

I see that you already have code in there to check for that, but after
displaying its error message, it goes on with the rest of the procedure.
Insert a line as shown below:
'Check txtSearch for Null value or Null Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, _
"Invalid Search Criterion!"
Me![txtSearch].SetFocus Exit Sub
End If
Click to expand...
Click to expand...
 
Top