Message box pop up when no reults

  • Thread starter Thread starter Guest
  • Start date Start date
Queries don't have that functionality. If you were using a form, you could
test a query and then use MsgBox() or some other method.
 
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyQueryName")
If rst.RecordCount = 0 Then
MsgBox "No Records Found"
End If
rst.Close
Set rst = Nothing
 
I tried this and received an error 3061. The code is in the On Got Focus of
the combobox. The error is issued as soon as the form is opened.

Could someone tell me why?

TIA
Ron
 
You have a parameterized query and it is not finding the parameters it needs.

Why the Got Focus event? That seems a strange place to put this code. What
is it you are trying to accomplish?
 
The query is a named one and not just written in the control (a combobox).
I was attempting to place the MsgBox on the coontrol that is actually doing
the lookup and I was attempting to beat the actual lookup. How else do you
learn?

Would this cause the problem? Parent!ServiceID

Ron
 
Ronald,

I wasn't criticizing what you are doing, just trying to understand it.
I don't know how you are using Parent!ServiceID, so I can't really give you
an answer. The problem you are having is that if you look in the query
builder at what you have in the Criteria row, you need to be sure it is
something the query can understand.

If Parent!ServiceID is being used in the Criteria row, it can't be resolved
Jet. My quess is ServiceID is a control on the parent form of the subform
your combo is in.
First, it is best to always qualify your references. I would suggest
instead of Parent!ServiceID you use Me.Parent!ServiceID, but that will still
not help with the SQL part. You need to be very specific when passing a
parameter to a query using a form control:
=[Forms]![TheFormName]![TheControlName]

If the control is on a subform:
=[Forms]![TheFormName]![TheSubFormControlName].[Form]![TheControlName]
 
There is nothing wrong with constructive criticism, and I certainly was not
offended.

The query will not display in the graphic window. Here is a cut and paste
of its SQL:
SELECT Members.ContactID, (FirstName+" ") & LastName, HomePhone, MobilePhone
FROM Members INNER JOIN MemberServices ON
MemberServices.ContactID=Members.ContactID
WHERE Active=True And MemberServices.ServiceID=Parent!ServiceID
ORDER BY LastName, FirstName;

The ServiceID is set in a control on the Parent form and this query is
executed from the subform.
The subform only lists members with the specified ServiceID. If there are
none then I wanted to present a message box informing of the situation. It
is not critical, it just would be nice and I would learn something new.

Thanks
Ron

Klatuu said:
Ronald,

I wasn't criticizing what you are doing, just trying to understand it.
I don't know how you are using Parent!ServiceID, so I can't really give
you
an answer. The problem you are having is that if you look in the query
builder at what you have in the Criteria row, you need to be sure it is
something the query can understand.

If Parent!ServiceID is being used in the Criteria row, it can't be
resolved
Jet. My quess is ServiceID is a control on the parent form of the subform
your combo is in.
First, it is best to always qualify your references. I would suggest
instead of Parent!ServiceID you use Me.Parent!ServiceID, but that will
still
not help with the SQL part. You need to be very specific when passing a
parameter to a query using a form control:
=[Forms]![TheFormName]![TheControlName]

If the control is on a subform:
=[Forms]![TheFormName]![TheSubFormControlName].[Form]![TheControlName]


--
Dave Hargis, Microsoft Access MVP


Ronald Marchand said:
The query is a named one and not just written in the control (a
combobox).
I was attempting to place the MsgBox on the coontrol that is actually
doing
the lookup and I was attempting to beat the actual lookup. How else do
you
learn?

Would this cause the problem? Parent!ServiceID

Ron
Click to expand...
Click to expand...
 
Okay, I would suggest this way of doing that. I would use the main form's
Current event. At this point, you would know what ServiceID you are on. You
can now see how many records are in the subform's recordset:

If Not Me.NewRecord Then
If Me!SubFormControlName.Form.Recordset.RecordCount = 0 Then
MsgBox "There are No Records For this Service ID"
End If
End If

There is one important fact to keep in mind. Where I refer to
SubFormControlName, it should be the name of the SubForm control on the main
form - Not the name of the form being used as the subform. This is a point
that many people have trouble with until they understand the difference. A
subform on a form is actually a control on the form. The form object being
used is identified in the subform control's Source Object property. Good luck

--
Dave Hargis, Microsoft Access MVP


Ronald Marchand said:
There is nothing wrong with constructive criticism, and I certainly was not
offended.

The query will not display in the graphic window. Here is a cut and paste
of its SQL:
SELECT Members.ContactID, (FirstName+" ") & LastName, HomePhone, MobilePhone
FROM Members INNER JOIN MemberServices ON
MemberServices.ContactID=Members.ContactID
WHERE Active=True And MemberServices.ServiceID=Parent!ServiceID
ORDER BY LastName, FirstName;

The ServiceID is set in a control on the Parent form and this query is
executed from the subform.
The subform only lists members with the specified ServiceID. If there are
none then I wanted to present a message box informing of the situation. It
is not critical, it just would be nice and I would learn something new.

Thanks
Ron

Klatuu said:
Ronald,

I wasn't criticizing what you are doing, just trying to understand it.
I don't know how you are using Parent!ServiceID, so I can't really give
you
an answer. The problem you are having is that if you look in the query
builder at what you have in the Criteria row, you need to be sure it is
something the query can understand.

If Parent!ServiceID is being used in the Criteria row, it can't be
resolved
Jet. My quess is ServiceID is a control on the parent form of the subform
your combo is in.
First, it is best to always qualify your references. I would suggest
instead of Parent!ServiceID you use Me.Parent!ServiceID, but that will
still
not help with the SQL part. You need to be very specific when passing a
parameter to a query using a form control:
=[Forms]![TheFormName]![TheControlName]

If the control is on a subform:
=[Forms]![TheFormName]![TheSubFormControlName].[Form]![TheControlName]


--
Dave Hargis, Microsoft Access MVP


Ronald Marchand said:
The query is a named one and not just written in the control (a
combobox).
I was attempting to place the MsgBox on the coontrol that is actually
doing
the lookup and I was attempting to beat the actual lookup. How else do
you
learn?

Would this cause the problem? Parent!ServiceID

Ron

You have a parameterized query and it is not finding the parameters it
needs.


Why the Got Focus event? That seems a strange place to put this code.
What
is it you are trying to accomplish?

--
Dave Hargis, Microsoft Access MVP


:

I tried this and received an error 3061. The code is in the On Got
Focus
of
the combobox. The error is issued as soon as the form is opened.

Could someone tell me why?

TIA
Ron


Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyQueryName")
If rst.RecordCount = 0 Then
MsgBox "No Records Found"
End If
rst.Close
Set rst = Nothing

--
Dave Hargis, Microsoft Access MVP


:



How do I create a message box pop up when my query returns no
results?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top