Custom error message??

[Cheyenne]

Is there a way to create a custom error message or pop up window in a form
when a value is not found?

 

[Dirk Goldgar]

Is there a way to create a custom error message or pop up window in
a form when a value is not found?

Yes, but you have to give more information than that. What are the
tables, fields, and controls involved, and what would the user do that
should cause the error message to be displayed?

 

[Cheyenne]

I have created a 'search' form based on table which has 3 columns: Account
Name, Telephone Number, and Account Number. The form is set up so that when
an account number, telephone number, or account name is entered into one of
the blank text boxes, and the search command button is clicked, a query opens
showing the corresponding information. My problems is that when an entry is
not found, the query still opens but shows a blank row where the appropriate
information should be. What I would like to do is fix this so that instead of
even opening the query when the record can't be found, an error message would
pop up with something like "RECORD NOT FOUND". I know it might seem a little
confusing but hope i've made myself clear. I can post VB or SQL codes if
needed.
Thanks.
-Cheyenne

 

[Dirk Goldgar]

I have created a 'search' form based on table which has 3 columns:
Account Name, Telephone Number, and Account Number. The form is set
up so that when an account number, telephone number, or account name
is entered into one of the blank text boxes, and the search command
button is clicked, a query opens showing the corresponding
information. My problems is that when an entry is not found, the
query still opens but shows a blank row where the appropriate
information should be. What I would like to do is fix this so that
instead of even opening the query when the record can't be found, an
error message would pop up with something like "RECORD NOT FOUND". I
know it might seem a little confusing but hope i've made myself
clear. I can post VB or SQL codes if needed.
Thanks.
-Cheyenne

Seeing the VBA code might help, but I'm going to plow on with some
suggestions regardless. <g>

First, I'll outline an approach that assumes you don't want to change
anything in your current setup aside from catching and reporting the
"not found" condition. I guess from your description that you have
something like this:

Private Sub cmdSearch_Click()

DoCmd.OpenQuery "qrySearchResults"

End Sub

I'm not sure whether you meant that you have a different search button
for each of the three text boxes, or whether you examine the text boxes
to see which one was filled in and open a different query for each, or
whether you have a single query that is set up so that it doesn't matter
which text box is filled in. Probably the most flexible way to find out
if there will be any results is to open a recordset on that same query
and check whether the recordset has any records. This is a bit
redundant, processing-wise, since it involves executing the query twice,
bit it's simple -- for the most part. The only complication is that the
query presumably has parameters -- references to the form controls used
as criteria -- so you have to resolve the parameters before opening the
recordset. Here's how you do it with DAO (requires a reference to the
Microsoft DAO (3.6, probably) Object library:

Private Sub cmdSearch_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim prm As DAO.Parameter

Const conSearchQuery As String = "qrySearchResults"

Set db = CurrentDb
Set qdf = db.QueryDefs(conSearchQuery)
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset
if rs.EOF Then
MsgBox "No matching records were found."
Else
DoCmd.OpenQuery "qrySearchResults"
End If

rs.Close
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

End Sub

That should take care of the first approach.

There are a number of other possible approaches to this problem. If the
purpose is to find only one record that matches whatever was entered,
you could use a form bound to the table and place unbound combo boxes in
the form's header section. These combo boxes could be used to find the
record that matches whatever is selected in the combo box. It would
then be impossible for the user to enter a value that doesn't exist in
the table, because the combo boxes would only offer choices that exist.

Or you could do something similar with the combo boxes, but bind the
form to your search query. Then no records would appear on the form
until the user selects something in one of the combo boxes. In the
AfterUpdate event of each combo box you would clear the *other* combo
boxes, and requery the form -- voilá, the matching record(s) would
appear on the form.

 

[Cheyenne]

Actually, I have 3 text boxes (one for each search field: bill name,
acct#,tel#)
I have only 1 search command button. when text is entered into either of the
text boxes and the search button is clicked, the query opens shows the
corresponding record. Here is the VB code that i'm currently using:

Private Sub Command10_Click()
On Error Resume Next

If Not IsNull(Me.ACCOUNT_NUMBER) Then
DoCmd.OpenQuery "queryaccountnumber", acViewNormal
Else
If Not IsNull(Me.BILL_NAME) Then
DoCmd.OpenQuery "querybillName", acViewNormal
Else
If Not IsNull(Me.TELEPHONE_NUMBER) Then
DoCmd.OpenQuery "querytelephonenumber", acViewNormal


End If
End If
End If
End Sub

Since my code is pretty detailed , i'm not sure how to incorporate the code
you suggested. Please advise. Thanks.
-Cheyenne

 

[Dirk Goldgar]

Actually, I have 3 text boxes (one for each search field: bill name,
acct#,tel#)
I have only 1 search command button. when text is entered into either
of the text boxes and the search button is clicked, the query opens
shows the corresponding record. Here is the VB code that i'm
currently using:

Private Sub Command10_Click()
On Error Resume Next

If Not IsNull(Me.ACCOUNT_NUMBER) Then
DoCmd.OpenQuery "queryaccountnumber", acViewNormal
Else
If Not IsNull(Me.BILL_NAME) Then
DoCmd.OpenQuery "querybillName", acViewNormal
Else
If Not IsNull(Me.TELEPHONE_NUMBER) Then
DoCmd.OpenQuery "querytelephonenumber", acViewNormal


End If
End If
End If
End Sub

Since my code is pretty detailed , i'm not sure how to incorporate
the code you suggested. Please advise. Thanks.

Okay, that's one of the possibilities I speculated about, to which the
code I posted would have to be adapted. Let's make our lives easier by
creating a helper function, Put this function into a standard module, so
you can use it any time you like:

'----- start of code for helper function -----
Function fncQueryReturnsRecords(pstrQuery As String) As Boolean

' Return True if the query that was passed in pstrQuery
' returns records; False if not. The argument pstrQuery
' must be a stored query, not a SQL string. The query may
' contain references to controls on open forms.

' This function requires that a reference be set to the
' Microsoft DAO Object Library.

On Error GoTo Err_Handler


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim prm As DAO.Parameter

Set db = CurrentDb

Set qdf = db.QueryDefs(pstrQuery)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

If qdf.Type = dbQSelect _
Or (qdf.Type = dbQSQLPassThrough And qdf.ReturnsRecords) _
Then
Set rs = qdf.OpenRecordset
fncQueryReturnsRecords = Not rs.EOF
rs.Close
Set rs = Nothing
Else
MsgBox _
"The query you passed to fncQueryReturnsRecords, '" & _
pstrQuery & "', is not a SELECT query.", _
vbExclamation, _
"Invalid Query"
End If

Set qdf = Nothing
Set db = Nothing

Exit_Point:
Exit Function

Err_Handler:
If Err.Number = 3265 Then
MsgBox _
"The argument passed to fncQueryReturnsRecords " & _
"is not the name of a stored query.", _
vbExclamation, _
"Invalid Query Name"
Else
MsgBox _
Err.Description, _
vbExclamation, _
"Error " & Err.Number
End If
Resume Exit_Point

End Function
'----- end of code for helper function -----

Now modify your code like this:

'----- start of revised command-button code -----
Private Sub Command10_Click()

On Error Resume Next

Dim strQuery As String

If Not IsNull(Me.ACCOUNT_NUMBER) Then
strQuery = "queryaccountnumber"
ElseIf Not IsNull(Me.BILL_NAME) Then
strQuery = "querybillName"
ElseIf Not IsNull(Me.TELEPHONE_NUMBER) Then
strQuery = "querytelephonenumber"
End If

If Len(strQuery) > 0 Then
If fnQueryReturnsRecords(strQuery) Then
DoCmd.OpenQuery strQuery, acViewNormal
Else
MsgBox "No matching records were found."
End If

End Sub
'----- end of revised command-button code -----

 

[Cheyenne]

Thanks for the codes. It worked just perfectly. Way to go!
-Cheyenne