Warning message

J

Johnny Lui

Hi Everyone,

I'll explain what I'm doing first. At the moment, I am
doing a record search via a form. I like to know if it's
possible to display an error message if that record
doesn't exist. Currently, it displays a blank form which
isn't too informative for the user.

If someone could help me with this, it would be greatly
appreciated.

Thanks in advance.


Cheers,

Johnny.
 
E

Eric J. Williams

If you are doing your record search via a recordset you should be able to
use the nomatch property.

For example:
....
If rsClone.nomatch Then MsgBox "No records satisfy the specified search
criteria", vbExclamation, "No Match"
....

I hope this helps.

Eric


| Hi Everyone,
|
| I'll explain what I'm doing first. At the moment, I am
| doing a record search via a form. I like to know if it's
| possible to display an error message if that record
| doesn't exist. Currently, it displays a blank form which
| isn't too informative for the user.
|
| If someone could help me with this, it would be greatly
| appreciated.
|
| Thanks in advance.
|
|
| Cheers,
|
| Johnny.
 
J

Johnny Lui

Hi Eric,

Thanks for your reply.

Unfortunately, I'm using a query to do the search. Can you
do via the query?

Thanks again.


Cheers,

Johnny.
 
K

Ken Snell

Try using the DCount function:

If DCount("*", "QueryName", "[FieldName]="somevalue") = 0 Then
MsgBox "No matches found."
Exit Sub
End If

If the query is the form's recordsource, you could use this:
If DCount("*", Me.RecordSource, "[FieldName]="somevalue") = 0 Then
MsgBox "No matches found."
Exit Sub
End If

Note that the criterion expression might not be needed for the second
example, depending upon how you were filtering your form.

If you can provide more info about your form and the query, we can revise
the code more specifically for you.
 
J

Johnny Lui

Hi Ken,

Thanks for your reply.

Here's the query:

SELECT tblProducts.[Catalog Number], tblProducts.[Catalog
Description], tblProducts.[Company Name], tblProducts.P1,
tblProducts.P2, tblProducts.Description
FROM tblProducts
WHERE (((tblProducts.[Catalog Number])=[Please enter the
Catalog Number (do not include spaces or hyphens):]));

Is there a way to put the Dcount function you mention in
the query?

Thanks again.


Cheers,

Johnny.

-----Original Message-----
Try using the DCount function:

If DCount("*", "QueryName", "[FieldName]="somevalue") = 0 Then
MsgBox "No matches found."
Exit Sub
End If

If the query is the form's recordsource, you could use this:
If DCount("*", Me.RecordSource, "[FieldName] ="somevalue") = 0 Then
MsgBox "No matches found."
Exit Sub
End If

Note that the criterion expression might not be needed for the second
example, depending upon how you were filtering your form.

If you can provide more info about your form and the query, we can revise
the code more specifically for you.
--
Ken Snell
<MS ACCESS MVP>


Johnny Lui said:
Hi Everyone,

I'll explain what I'm doing first. At the moment, I am
doing a record search via a form. I like to know if it's
possible to display an error message if that record
doesn't exist. Currently, it displays a blank form which
isn't too informative for the user.

If someone could help me with this, it would be greatly
appreciated.

Thanks in advance.


Cheers,

Johnny.
Click to expand...


.
Click to expand...
 
J

Johnny Lui

Hi Ken,

Thankyou so much for your reply again.

Yes, the user is typing a value into a textbox on the
form. It has a search button, so when they press it,
another form pops up and displays the record. However, I
want it to display a error message if the record doesn't
exist.

Thanks in advance.


Cheers,

Johnny.
-----Original Message-----
It doesn't go in the query. It goes in VBA code in your form that is
"calling" the query.

Before I get into more details, please provide more details about how your
form is set up: your user is typing a value into a textbox on the form? what
is happening? Then we can best determine how you can use the DCount
function.

--
Ken Snell
<MS ACCESS MVP>

Johnny Lui said:
Hi Ken,

Thanks for your reply.

Here's the query:

SELECT tblProducts.[Catalog Number], tblProducts. [Catalog
Description], tblProducts.[Company Name], tblProducts.P1,
tblProducts.P2, tblProducts.Description
FROM tblProducts
WHERE (((tblProducts.[Catalog Number])=[Please enter the
Catalog Number (do not include spaces or hyphens):]));

Is there a way to put the Dcount function you mention in
the query?

Thanks again.


Cheers,

Johnny.

-----Original Message-----
Try using the DCount function:

If DCount("*", "QueryName", "[FieldName]
Click to expand...
="somevalue")
= 0 Then
MsgBox "No matches found."
Exit Sub
End If

If the query is the form's recordsource, you could use this:
If DCount("*", Me.RecordSource, "[FieldName] ="somevalue") = 0 Then
MsgBox "No matches found."
Exit Sub
End If

Note that the criterion expression might not be needed for the second
example, depending upon how you were filtering your form.

If you can provide more info about your form and the query, we can revise
the code more specifically for you.
--
Ken Snell
<MS ACCESS MVP>


Hi Everyone,

I'll explain what I'm doing first. At the moment, I am
doing a record search via a form. I like to know if it's
possible to display an error message if that record
doesn't exist. Currently, it displays a blank form which
isn't too informative for the user.

If someone could help me with this, it would be greatly
appreciated.

Thanks in advance.


Cheers,

Johnny.


.
Click to expand...
Click to expand...


.
Click to expand...
 
J

Johnny Lui

This is the what the search button does the moment:

Option Compare Database

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProducts"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub
-----Original Message-----
Hi Ken,

Thankyou so much for your reply again.

Yes, the user is typing a value into a textbox on the
form. It has a search button, so when they press it,
another form pops up and displays the record. However, I
want it to display a error message if the record doesn't
exist.

Thanks in advance.


Cheers,

Johnny.
-----Original Message-----
It doesn't go in the query. It goes in VBA code in your form that is
"calling" the query.

Before I get into more details, please provide more details about how your
form is set up: your user is typing a value into a textbox on the form? what
is happening? Then we can best determine how you can use the DCount
function.

--
Ken Snell
<MS ACCESS MVP>

Johnny Lui said:
Hi Ken,

Thanks for your reply.

Here's the query:

SELECT tblProducts.[Catalog Number], tblProducts. [Catalog
Description], tblProducts.[Company Name], tblProducts.P1,
tblProducts.P2, tblProducts.Description
FROM tblProducts
WHERE (((tblProducts.[Catalog Number])=[Please enter the
Catalog Number (do not include spaces or hyphens):]));

Is there a way to put the Dcount function you mention in
the query?

Thanks again.


Cheers,

Johnny.


-----Original Message-----
Try using the DCount function:

If DCount("*", "QueryName", "[FieldName] ="somevalue")
= 0 Then
MsgBox "No matches found."
Exit Sub
End If

If the query is the form's recordsource, you could use
this:
If DCount("*", Me.RecordSource, "[FieldName]
="somevalue") = 0 Then
MsgBox "No matches found."
Exit Sub
End If

Note that the criterion expression might not be needed
for the second
example, depending upon how you were filtering your form.

If you can provide more info about your form and the
query, we can revise
the code more specifically for you.
--
Ken Snell
<MS ACCESS MVP>


Hi Everyone,

I'll explain what I'm doing first. At the moment, I am
doing a record search via a form. I like to know if it's
possible to display an error message if that record
doesn't exist. Currently, it displays a blank form which
isn't too informative for the user.

If someone could help me with this, it would be greatly
appreciated.

Thanks in advance.


Cheers,

Johnny.


.
Click to expand...


.
Click to expand...
.
Click to expand...
 
K

Ken Snell

Here's one way to do what you seek (I have not tested this exact code, so it
may need a bit of tweaking):
---------------------------------------------------
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProducts"

DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
If DCount("*", Forms(stDocName).Form.RecordSource) = 0 Then
MsgBox "There are no records to display."
DoCmd.Close acForm, Forms(stDocName).Form.Name
Else
Forms(stDocName).Form.Visible = True
End If

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub
------------------------------------------------------

--
Ken Snell
<MS ACCESS MVP>

Johnny Lui said:
This is the what the search button does the moment:

Option Compare Database

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProducts"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub
-----Original Message-----
Hi Ken,

Thankyou so much for your reply again.

Yes, the user is typing a value into a textbox on the
form. It has a search button, so when they press it,
another form pops up and displays the record. However, I
want it to display a error message if the record doesn't
exist.

Thanks in advance.


Cheers,

Johnny.
-----Original Message-----
It doesn't go in the query. It goes in VBA code in your form that is
"calling" the query.

Before I get into more details, please provide more details about how your
form is set up: your user is typing a value into a textbox on the form? what
is happening? Then we can best determine how you can use the DCount
function.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for your reply.

Here's the query:

SELECT tblProducts.[Catalog Number], tblProducts. [Catalog
Description], tblProducts.[Company Name], tblProducts.P1,
tblProducts.P2, tblProducts.Description
FROM tblProducts
WHERE (((tblProducts.[Catalog Number])=[Please enter the
Catalog Number (do not include spaces or hyphens):]));

Is there a way to put the Dcount function you mention in
the query?

Thanks again.


Cheers,

Johnny.


-----Original Message-----
Try using the DCount function:

If DCount("*", "QueryName", "[FieldName] ="somevalue")
= 0 Then
MsgBox "No matches found."
Exit Sub
End If

If the query is the form's recordsource, you could use
this:
If DCount("*", Me.RecordSource, "[FieldName]
="somevalue") = 0 Then
MsgBox "No matches found."
Exit Sub
End If

Note that the criterion expression might not be needed
for the second
example, depending upon how you were filtering your form.

If you can provide more info about your form and the
query, we can revise
the code more specifically for you.
--
Ken Snell
<MS ACCESS MVP>


Hi Everyone,

I'll explain what I'm doing first. At the moment, I am
doing a record search via a form. I like to know if it's
possible to display an error message if that record
doesn't exist. Currently, it displays a blank form which
isn't too informative for the user.

If someone could help me with this, it would be greatly
appreciated.

Thanks in advance.


Cheers,

Johnny.


.



.
Click to expand...
.
Click to expand...
Click to expand...
 
K

Ken Snell

Shucks.....my home base is about 1/2 way around the world from you! Guess
you'll have to drink that coffee!

--
Ken Snell
<MS ACCESS MVP>

Johnny Lui said:
Hi Ken,

That code worked perfectly. Your help was greatly
appreicated.

If you are in Australia, Sydney, NSW, Frenchs Forest 2086,
I'll buy you coffee.

Thanks again.


Cheers,

Johnny.
-----Original Message-----
Here's one way to do what you seek (I have not tested this exact code, so it
may need a bit of tweaking):
---------------------------------------------------
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProducts"

DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
If DCount("*", Forms(stDocName).Form.RecordSource) = 0 Then
MsgBox "There are no records to display."
DoCmd.Close acForm, Forms(stDocName).Form.Name
Else
Forms(stDocName).Form.Visible = True
End If

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub
------------------------------------------------------

--
Ken Snell
<MS ACCESS MVP>

Johnny Lui said:
This is the what the search button does the moment:

Option Compare Database

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmProducts"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub
-----Original Message-----
Hi Ken,

Thankyou so much for your reply again.

Yes, the user is typing a value into a textbox on the
form. It has a search button, so when they press it,
another form pops up and displays the record. However, I
want it to display a error message if the record doesn't
exist.

Thanks in advance.


Cheers,

Johnny.

-----Original Message-----
It doesn't go in the query. It goes in VBA code in your
form that is
"calling" the query.

Before I get into more details, please provide more
details about how your
form is set up: your user is typing a value into a
textbox on the form? what
is happening? Then we can best determine how you can use
the DCount
function.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks for your reply.

Here's the query:

SELECT tblProducts.[Catalog Number], tblProducts.
[Catalog
Description], tblProducts.[Company Name],
tblProducts.P1,
tblProducts.P2, tblProducts.Description
FROM tblProducts
WHERE (((tblProducts.[Catalog Number])=[Please enter
the
Catalog Number (do not include spaces or hyphens):]));

Is there a way to put the Dcount function you mention
in
the query?

Thanks again.


Cheers,

Johnny.


-----Original Message-----
Try using the DCount function:

If DCount("*", "QueryName", "[FieldName]
="somevalue")
= 0 Then
MsgBox "No matches found."
Exit Sub
End If

If the query is the form's recordsource, you could use
this:
If DCount("*", Me.RecordSource, "[FieldName]
="somevalue") = 0 Then
MsgBox "No matches found."
Exit Sub
End If

Note that the criterion expression might not be needed
for the second
example, depending upon how you were filtering your
form.

If you can provide more info about your form and the
query, we can revise
the code more specifically for you.
--
Ken Snell
<MS ACCESS MVP>


Hi Everyone,

I'll explain what I'm doing first. At the moment, I
am
doing a record search via a form. I like to know if
it's
possible to display an error message if that record
doesn't exist. Currently, it displays a blank form
which
isn't too informative for the user.

If someone could help me with this, it would be
greatly
appreciated.

Thanks in advance.


Cheers,

Johnny.


.



.

.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

GoSub warning 3
No Data Selected for Form 4
Main form with two sub forms 1
Check for duplicate with multiple criteria 4
Field Display goes Blank 1
How to set Subform Default Focus 1
checking for existing record 3
Replacing 'NotInList' Message for a Combo 2

Top