Need help with a combobox query

[Ronald Marchand]

I hope that someone can assist me with a little dilemma
I have a database which as the following tables/fields
Contacts
ContactID
LastName
FirstName
Active (yes/no)
(other info fields)
ContactServices
ContactID
ServiceID
Services
ServiceID
ServiceDescription
Events
EventID
EventDescription
ServiceID
(other info fields)
EventStaff
EventID
ContactID

I have a form/subform to fill in Events/EventStaff. The EventStaff subform
has a combo box to look up the contacts to assign. This lookup must present
the ContactID, LastName, FirstName of any contact who is
A. Active = True
If the Active status goes false, they remain in the table and
displayed on the form.
B. Whose ContactServices.ServiceID = Events.ServiceID

The form source is the Events Table.
The source for the subform from the Query builder is
SELECT EventStaffing.ContactID, Contacts.LastName, Contacts.FirstName,
EventStaffing.EventID
FROM Contacts INNER JOIN (Events INNER JOIN EventStaffing ON (Events.EventID
= EventStaffing.EventID) AND (Events.EventID = EventStaffing.EventID)) ON
Contact.ContactID = EventStaffing.ContactID;


Currently the SQL for the ContactID lookup is:
SELECT Members.ContactID, Members.LastName, Members.FirstName,
Members.Active
FROM Members
WHERE (((Members.Active)=True))
ORDER BY Members.LastName;

What I do not understand is how to apply condition "B" ..
ContactServices.ServiceID = Events.ServiceID. If the Contact is not in the
table, then it is not listed for selection.

Can someone guide me to the solution?

TIA
Ron

 

[Guest]

Ron:

I don't see any reason why you need the query joining the three tables as
the subform's RecordSource. Simply the EventStaffing table will do, unless
you want to sort the subform's rows by staff name, in which case use a query
joining it to Contacts and ORDER BY LastName, Firstname. The subform will be
linked to the parent form on EventID.

The RowSource for the combo box on the subform bound to ContactID needs to
return the ContactID and the concatenated first and last names from the
Contacts table where the contact's Active value is True, and where the
ServiceID value in the row in ContactServices for the contact matches the
current ServiceID value. For this you need to join the Contacts table to the
ContactServices table and restrict the result set by means of a parameter
referencing the form's ServiceID. As you are dealing with a property of a
control on the subform you can use the Parent property to reference the main
form rather than a fully qualified reference. So the RowSource would be like
this:

SELECT Contacts.ContactID, (FirstName + " ") & LastName
FROM Contacts INNER JOIN ContactServices
ON ContactServices.ContactID = Contacts.ContactID
WHERE Active = TRUE
AND ContactServices.ServiceID = Parent!ServiceID
ORDER BY LastName, FirstName;

Set the control's BoundColumn property to 1, its ColumnCount property to 2
and its ColumnWidths to 0cm;8cm (or rough equivalent in inches, but the first
dimension must be zero to hide the first column).

You'll need to requery the contact combo box on the subform both in the
parent form's Current event procedure and in its AfterInsert event procedure.
The control on the subform is referenced via the Form property of the
subform control (that's the control in the main form which houses the
subform), e.g.

Me.YourSubformControl.Form.cboContact.Requery

Ken Sheridan
Stafford, England

 

[Ronald Marchand]

Ken:
Thank you a hundred times.

This worked. I am a novice with Access and I spent hours toiling over this
one. Access will not graphically display this query and I was trying to
develop it graphically.

I do have two questions.

1. Why the need for the requery?

2. Can the list be limited to not present a name if it is already in the
EventStaff table.
I don't want to wind up with
1 John Doe
1 Jane Doe
1 John Doe ---- duplicate

Ron Marchand
Kenner, LA. (USA)

 

[Guest]

Ron:

The control needs to be requeried whenever the EventID of the main parent
form changes, i.e. when you move the parent form to another record, or enter
a new event record. This is because the parameter in the combo box's
RowSource which references the EventID does not reflect the new value until
it is requeried.

You can easily eliminate any duplicates by changing the RowSource to:

SELECT DISTINCT Contacts.ContactID, (FirstName + " ") & LastName
FROM Contacts INNER JOIN ContactServices
ON ContactServices.ContactID = Contacts.ContactID
WHERE Active = TRUE
AND ContactServices.ServiceID = Parent!ServiceID
ORDER BY LastName, FirstName;

The DISTINCT option restricts the result set to just one instance of each
row where two or more rows return the same data.

Incidentally you might have wondered why the expression (FirstName + " ") &
LastName is used to build the full name rather than simply FirstName & " " &
LastName. It allows for a missing first name. If you have any British upper
crust individuals amongst your contacts for instance, e.g. our local aristo
Lord Stafford, he would be just called Stafford (they do have real names but
you have to move in the right social circles to use them!). When a space is
tacked onto a Null first name using the + operator the space is suppressed
because Nulls 'propagate', i.e. Null + anything = Null. If the &
concatenation character is used, however, then the space is not suppressed so
you'd end up with an unwanted space at the start of the name. Making use of
the propagation of Nulls is a useful little trick here (more so if you have
names with or without middle names or initials), but when dealing with data
such as financial amounts its important as if you try and add values in
currency fields for instance and one is Null the result will be Null.
Currency fields in a table should normally be given a default value of zero
and 'required' so you never have a Null. This also reduces any ambiguity,
e.g. a zero credit rating means exactly that, but a Null credit rating has no
intrinsic meaning, so it could mean no credit, unlimited credit or anything
else you can think of, because a Null is an 'unknown' not an equivalent of
zero.

Ken Sheridan
Stafford, England

 

[Ronald Marchand]

Ken:

(they do have real names but you have to move in the right social circles
to use them!).

Fortunately, here in the colonies we don't have that problem, although some
local politicians think they are royalty.

I am confused on one point.

You'll need to requery the contact combo box on the subform both in the
parent form's Current event procedure and in its AfterInsert event
procedure

Rephrased:
"ComboBox on the subform in the ParentForm"

The parent form is Events and the subform is "EventStaffing Subform" and its
control is named the same thing. There are only two events here are "On
Enter" and "On Exit". I know I am not understanding what you wrote and I am
not sure where to put it.

Thanks
Ron Marchand

 

[Guest]

Ron:

Which particular colony would that be?

I may have caused your confusion by referring to EventID in my last post
when I meant ServiceID. It’s the latter which is referenced as a parameter
by the combo box's RowSource of course. Mea culpa!

It’s the Events form's event procedures you use here, not the subform
control's. The combo box on the EventStaffing Subform, I'll call it
cboContact for this example, needs to be requeried when the Events form's
ServiceID changes so that its list will reflect the changed ServiceID. The
ServiceID could change when you move to a different record in the parent form
or when you add a new event, so the two event procedures of the Events form
in which you need to requery the combo box are its Current and AfterInsert
event procedures, though it might be better to use the AfterUpdate rather
than AfterInsert event procedure as its theoretically possible that the
ServiceID for an existing event record could be changed (I'd assumed it would
be static, but that's perhaps a rash assumption). The AfterUpdate event
fires for both new and changed records so would cover both possibilities.
The combo box is referenced via the subform control's Form property, so the
syntax would be:

Me.[EventStaffing Subform].Form.[cboContact].Requery

Ken Sheridan
Stafford, England

 

[Ronald Marchand]

In Line response ...

Ron:

Which particular colony would that be?

The big one, just sligjtly to your west. )

I may have caused your confusion by referring to EventID in my last post
when I meant ServiceID. It's the latter which is referenced as a
parameter
by the combo box's RowSource of course. Mea culpa!

You are correct. I now have it working OK. If you don';t mind, I would
like to impose on you one last time on this issue. I have attempted a
slight variation on this approach.

I have a form (FormA) which only exists to set a combobox field (ServiceID)
with a value from the services table. I have placed a subform on this form
(FormASubform). This subform has a query as its record source. There is a
WHERE clause to set the match ServiceID = Parent!ServiceID.

This subform has no combo boxes and there is no possible selection by the
user. How do I reference the 'requery'? If I run the query and fill in the
ServiceID, it works as desired. If I run the Subform. give it the service
ID when prompted, I get the proper results.

Again, Thanks
Ron Marchand

It's the Events form's event procedures you use here, not the subform
control's. The combo box on the EventStaffing Subform, I'll call it
cboContact for this example, needs to be requeried when the Events form's
ServiceID changes so that its list will reflect the changed ServiceID.
The
ServiceID could change when you move to a different record in the parent
form
or when you add a new event, so the two event procedures of the Events
form
in which you need to requery the combo box are its Current and AfterInsert
event procedures, though it might be better to use the AfterUpdate rather
than AfterInsert event procedure as its theoretically possible that the
ServiceID for an existing event record could be changed (I'd assumed it
would
be static, but that's perhaps a rash assumption). The AfterUpdate event
fires for both new and changed records so would cover both possibilities.
The combo box is referenced via the subform control's Form property, so
the
syntax would be:

Me.[EventStaffing Subform].Form.[cboContact].Requery

Ken Sheridan
Stafford, England

Ken:


Fortunately, here in the colonies we don't have that problem, although
some
local politicians think they are royalty.

I am confused on one point.
Rephrased:
"ComboBox on the subform in the ParentForm"

The parent form is Events and the subform is "EventStaffing Subform" and
its
control is named the same thing. There are only two events here are "On
Enter" and "On Exit". I know I am not understanding what you wrote and I
am
not sure where to put it.

Thanks
Ron Marchand

 

[Guest]

Ron:

That would be Ireland, where I originate from! You must mean that little
place just off the west coast of Co Mayo, where everyone wears ten gallon
hats and spurs. My grandmother was brought up there; some little town called
Boston, Mass<G>.

With the approach you've adopted the subform control itself would be
requeried in the combo box's AfterUpdate event procedure. However, you don't
need to do so as you can simply remove the WHERE clause from the subform's
query and set the subform control's LinkMasterFields property to SewrviceID,
in this case the name of the combo box on the parent form and the
LinkChildFields property to ServiceID, in this case the name of the column in
the subdorm's underlying recordset..

Ken Sheridan
Stafford, England

In Line response ...

Ron:

Which particular colony would that be?

The big one, just sligjtly to your west. )

I may have caused your confusion by referring to EventID in my last post
when I meant ServiceID. It's the latter which is referenced as a
parameter
by the combo box's RowSource of course. Mea culpa!

You are correct. I now have it working OK. If you don';t mind, I would
like to impose on you one last time on this issue. I have attempted a
slight variation on this approach.

I have a form (FormA) which only exists to set a combobox field (ServiceID)
with a value from the services table. I have placed a subform on this form
(FormASubform). This subform has a query as its record source. There is a
WHERE clause to set the match ServiceID = Parent!ServiceID.

This subform has no combo boxes and there is no possible selection by the
user. How do I reference the 'requery'? If I run the query and fill in the
ServiceID, it works as desired. If I run the Subform. give it the service
ID when prompted, I get the proper results.

Again, Thanks
Ron Marchand

It's the Events form's event procedures you use here, not the subform
control's. The combo box on the EventStaffing Subform, I'll call it
cboContact for this example, needs to be requeried when the Events form's
ServiceID changes so that its list will reflect the changed ServiceID.
The
ServiceID could change when you move to a different record in the parent
form
or when you add a new event, so the two event procedures of the Events
form
in which you need to requery the combo box are its Current and AfterInsert
event procedures, though it might be better to use the AfterUpdate rather
than AfterInsert event procedure as its theoretically possible that the
ServiceID for an existing event record could be changed (I'd assumed it
would
be static, but that's perhaps a rash assumption). The AfterUpdate event
fires for both new and changed records so would cover both possibilities.
The combo box is referenced via the subform control's Form property, so
the
syntax would be:

Me.[EventStaffing Subform].Form.[cboContact].Requery

Ken Sheridan
Stafford, England

Ken:
You wrote:
(they do have real names but you have to move in the right social
circles
to use them!).

Fortunately, here in the colonies we don't have that problem, although
some
local politicians think they are royalty.

I am confused on one point.
You'll need to requery the contact combo box on the subform both in the
parent form's Current event procedure and in its AfterInsert event
procedure
Rephrased:
"ComboBox on the subform in the ParentForm"

The parent form is Events and the subform is "EventStaffing Subform" and
its
control is named the same thing. There are only two events here are "On
Enter" and "On Exit". I know I am not understanding what you wrote and I
am
not sure where to put it.

Thanks
Ron Marchand

Ron:

The control needs to be requeried whenever the EventID of the main
parent
form changes, i.e. when you move the parent form to another record, or
enter
a new event record. This is because the parameter in the combo box's
RowSource which references the EventID does not reflect the new value
until
it is requeried.

You can easily eliminate any duplicates by changing the RowSource to:

SELECT DISTINCT Contacts.ContactID, (FirstName + " ") & LastName
FROM Contacts INNER JOIN ContactServices
ON ContactServices.ContactID = Contacts.ContactID
WHERE Active = TRUE
AND ContactServices.ServiceID = Parent!ServiceID
ORDER BY LastName, FirstName;

The DISTINCT option restricts the result set to just one instance of
each
row where two or more rows return the same data.

Incidentally you might have wondered why the expression (FirstName + "
")
&
LastName is used to build the full name rather than simply FirstName &
" "
&
LastName. It allows for a missing first name. If you have any British
upper
crust individuals amongst your contacts for instance, e.g. our local
aristo
Lord Stafford, he would be just called Stafford (they do have real
names
but
you have to move in the right social circles to use them!). When a
space
is
tacked onto a Null first name using the + operator the space is
suppressed
because Nulls 'propagate', i.e. Null + anything = Null. If the &
concatenation character is used, however, then the space is not
suppressed
so
you'd end up with an unwanted space at the start of the name. Making
use
of
the propagation of Nulls is a useful little trick here (more so if you
have
names with or without middle names or initials), but when dealing with
data
such as financial amounts its important as if you try and add values in
currency fields for instance and one is Null the result will be Null.
Currency fields in a table should normally be given a default value of
zero
and 'required' so you never have a Null. This also reduces any
ambiguity,
e.g. a zero credit rating means exactly that, but a Null credit rating
has
no
intrinsic meaning, so it could mean no credit, unlimited credit or
anything
else you can think of, because a Null is an 'unknown' not an equivalent
of
zero.

Ken Sheridan
Stafford, England

:

Ken:
Thank you a hundred times.

This worked. I am a novice with Access and I spent hours toiling over
this
one. Access will not graphically display this query and I was trying
to
develop it graphically.

I do have two questions.

1. Why the need for the requery?

2. Can the list be limited to not present a name if it is already in
the
EventStaff table.
I don't want to wind up with
1 John Doe
1 Jane Doe
1 John Doe ---- duplicate

Ron Marchand
Kenner, LA. (USA)

message
Ron:

I don't see any reason why you need the query joining the three
tables
as
the subform's RecordSource. Simply the EventStaffing table will do,
unless
you want to sort the subform's rows by staff name, in which case use
a
query
joining it to Contacts and ORDER BY LastName, Firstname. The
subform
will
be
linked to the parent form on EventID.

The RowSource for the combo box on the subform bound to ContactID
needs
to
return the ContactID and the concatenated first and last names from
the
Contacts table where the contact's Active value is True, and where
the
ServiceID value in the row in ContactServices for the contact
matches
the
current ServiceID value. For this you need to join the Contacts
table
to
the
ContactServices table and restrict the result set by means of a
parameter
referencing the form's ServiceID. As you are dealing with a
property
of a
control on the subform you can use the Parent property to reference
the
main
form rather than a fully qualified reference. So the RowSource
would
be
like
this:

SELECT Contacts.ContactID, (FirstName + " ") & LastName
FROM Contacts INNER JOIN ContactServices
ON ContactServices.ContactID = Contacts.ContactID
WHERE Active = TRUE
AND ContactServices.ServiceID = Parent!ServiceID
ORDER BY LastName, FirstName;

Set the control's BoundColumn property to 1, its ColumnCount
property
to 2
and its ColumnWidths to 0cm;8cm (or rough equivalent in inches, but
the
first
dimension must be zero to hide the first column).

You'll need to requery the contact combo box on the subform both in
the
parent form's Current event procedure and in its AfterInsert event
procedure.
The control on the subform is referenced via the Form property of
the
subform control (that's the control in the main form which houses
the
subform), e.g.

Me.YourSubformControl.Form.cboContact.Requery

Ken Sheridan
Stafford, England

:

I hope that someone can assist me with a little dilemma
I have a database which as the following tables/fields
Contacts
ContactID
LastName
FirstName
Active (yes/no)
(other info fields)
ContactServices
ContactID
ServiceID
Services
ServiceID
ServiceDescription
Events
EventID
EventDescription
ServiceID
(other info fields)
EventStaff
EventID
ContactID

I have a form/subform to fill in Events/EventStaff. The EventStaff
subform
has a combo box to look up the contacts to assign. This lookup must
present
the ContactID, LastName, FirstName of any contact who is
A. Active = True
If the Active status goes false, they remain in the table and
displayed on the form.
B. Whose ContactServices.ServiceID = Events.ServiceID

 

[Ronald Marchand]

Ron:

That would be Ireland, where I originate from! You must mean that little
place just off the west coast of Co Mayo, where everyone wears ten gallon
hats and spurs. My grandmother was brought up there; some little town
called
Boston, Mass<G>.

AH! New England clam chowder. There are many people here from somewhere
else. I rarely hear of someone leaving here to homestead in another
country. Actually, the ten gallon hats are slightly to my west. I am in
the Mississippi River delta region (New Orleans), the land of crawfish,
shrimp, blue crab, oysters ... and the list goes on. I would love to tour
your plot of the earth.

Thanks again for your help.

Ron Marchand

 

[Ronald Marchand]

Ron:

The control needs to be requeried whenever the EventID of the main parent
form changes, i.e. when you move the parent form to another record, or
enter
a new event record. This is because the parameter in the combo box's
RowSource which references the EventID does not reflect the new value
until
it is requeried.

You can easily eliminate any duplicates by changing the RowSource to:

SELECT DISTINCT Contacts.ContactID, (FirstName + " ") & LastName
FROM Contacts INNER JOIN ContactServices
ON ContactServices.ContactID = Contacts.ContactID
WHERE Active = TRUE
AND ContactServices.ServiceID = Parent!ServiceID
ORDER BY LastName, FirstName;

The DISTINCT option restricts the result set to just one instance of each
row where two or more rows return the same data.

Why does this not work? Take out "DISTINCT" and it works correctly. Leave
it in and no record choices are presented in the combo box.

Also this code
If Me![EventStaffing Subform].Form.[cboContact].Recordset.RecordCount = 0
Then
MsgBox "There are no Volunteers for this Service ID"
End If
produces an error box: Run time error 91; Object variable or With block
variable not set

Any guidance?

Ron Marchand

 

[John Spencer]

First, if you are going to use DISTINCT and want to sort by last name and
firstName fields you will need to include them in the SELECT clause. You
can hide them in the combobox by setting the column width to zero. IF you
don't include the fields, you will get an error, but you won't get an error
message since in this case things fail silently. If you open the source in
query design view and try to run it you will see the error message.

Second, I think you might want the following expression. Combo boxes don't
have a recordset.
If Me![EventStaffing Subform].Form.[cboContact].ListCount= 0 Then ...


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ron:

The control needs to be requeried whenever the EventID of the main parent
form changes, i.e. when you move the parent form to another record, or
enter
a new event record. This is because the parameter in the combo box's
RowSource which references the EventID does not reflect the new value
until
it is requeried.

You can easily eliminate any duplicates by changing the RowSource to:

SELECT DISTINCT Contacts.ContactID, (FirstName + " ") & LastName
FROM Contacts INNER JOIN ContactServices
ON ContactServices.ContactID = Contacts.ContactID
WHERE Active = TRUE
AND ContactServices.ServiceID = Parent!ServiceID
ORDER BY LastName, FirstName;

The DISTINCT option restricts the result set to just one instance of each
row where two or more rows return the same data.

Why does this not work? Take out "DISTINCT" and it works correctly. Leave
it in and no record choices are presented in the combo box.

Also this code
If Me![EventStaffing Subform].Form.[cboContact].Recordset.RecordCount = 0
Then
MsgBox "There are no Volunteers for this Service ID"
End If
produces an error box: Run time error 91; Object variable or With block
variable not set

Any guidance?

Ron Marchand

 

[Ronald Marchand]

First, if you are going to use DISTINCT and want to sort by last name and
firstName fields you will need to include them in the SELECT clause. You
can hide them in the combobox by setting the column width to zero. IF you
don't include the fields, you will get an error, but you won't get an
error message since in this case things fail silently. If you open the
source in query design view and try to run it you will see the error
message.

Thanks for the reply.
I had never run the query as a query (if that makes sense). Doing so gave a
more descriptive error.

Second, I think you might want the following expression. Combo boxes
don't have a recordset.
If Me![EventStaffing Subform].Form.[cboContact].ListCount= 0 Then ...

Then how do you reference the items (records) returned by the query?
Currently, if a record is in the table and not in the results of this query,
the data is not displayed but remains there. I have been trying to figure
out how to compare the table reocrds to the query records in an effort to
delete those table records that do not exist in the query result.

Ron Marchand

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Ron:

The control needs to be requeried whenever the EventID of the main
parent
form changes, i.e. when you move the parent form to another record, or
enter
a new event record. This is because the parameter in the combo box's
RowSource which references the EventID does not reflect the new value
until
it is requeried.

You can easily eliminate any duplicates by changing the RowSource to:

SELECT DISTINCT Contacts.ContactID, (FirstName + " ") & LastName
FROM Contacts INNER JOIN ContactServices
ON ContactServices.ContactID = Contacts.ContactID
WHERE Active = TRUE
AND ContactServices.ServiceID = Parent!ServiceID
ORDER BY LastName, FirstName;

The DISTINCT option restricts the result set to just one instance of
each
row where two or more rows return the same data.

Why does this not work? Take out "DISTINCT" and it works correctly.
Leave it in and no record choices are presented in the combo box.

Also this code
If Me![EventStaffing Subform].Form.[cboContact].Recordset.RecordCount = 0
Then
MsgBox "There are no Volunteers for this Service ID"
End If
produces an error box: Run time error 91; Object variable or With block
variable not set

Any guidance?

Ron Marchand

 

[John Spencer]

Not quite sure what you are asking here.

If you want to refer to items in the list box or combobox you can use

V = Forms!NameOfControl.ItemData(#) to get the value of the bound column for
the row specified by the number

or you can use
V=Forms!NameOfControl.Column(Col#,Row#) to get the value of any column, row
combination (zero-based)

If you want to delete records that are not in a query then one method is

DELETE DistinctRow SomeField
FROM SomeTable
WHERE SomeField Not IN
(SELECT MatchingField
FROM Somewhere
WHERE Fieldx = Y and MatchingField is not null)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

First, if you are going to use DISTINCT and want to sort by last name and
firstName fields you will need to include them in the SELECT clause. You
can hide them in the combobox by setting the column width to zero. IF
you don't include the fields, you will get an error, but you won't get an
error message since in this case things fail silently. If you open the
source in query design view and try to run it you will see the error
message.

Thanks for the reply.
I had never run the query as a query (if that makes sense). Doing so gave
a more descriptive error.

Second, I think you might want the following expression. Combo boxes
don't have a recordset.
If Me![EventStaffing Subform].Form.[cboContact].ListCount= 0 Then ...

Then how do you reference the items (records) returned by the query?
Currently, if a record is in the table and not in the results of this
query, the data is not displayed but remains there. I have been trying to
figure out how to compare the table reocrds to the query records in an
effort to delete those table records that do not exist in the query
result.

Ron Marchand

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Ron:

The control needs to be requeried whenever the EventID of the main
parent
form changes, i.e. when you move the parent form to another record, or
enter
a new event record. This is because the parameter in the combo box's
RowSource which references the EventID does not reflect the new value
until
it is requeried.

You can easily eliminate any duplicates by changing the RowSource to:

SELECT DISTINCT Contacts.ContactID, (FirstName + " ") & LastName
FROM Contacts INNER JOIN ContactServices
ON ContactServices.ContactID = Contacts.ContactID
WHERE Active = TRUE
AND ContactServices.ServiceID = Parent!ServiceID
ORDER BY LastName, FirstName;

The DISTINCT option restricts the result set to just one instance of
each
row where two or more rows return the same data.

Why does this not work? Take out "DISTINCT" and it works correctly.
Leave it in and no record choices are presented in the combo box.

Also this code
If Me![EventStaffing Subform].Form.[cboContact].Recordset.RecordCount =
0 Then
MsgBox "There are no Volunteers for this Service ID"
End If
produces an error box: Run time error 91; Object variable or With block
variable not set

Any guidance?

Ron Marchand