I am wanting my program to look at a name in the combo box and determine if
the name is also a participant.

I am receiving a compile error : variant not defined.
when it does this it highlights Particvolu of "particvolu =" Participant"


Dim ParticipantName As Integer

ParticpantName = DCount("ID", "Combobox query", "volID", "Particvolu" = " &
cboName & " And Particvolu = "Participant")

"nancy" wrote:

> I am wanting my program to look at a name in the combo box and determine if
> the name is also a participant.
>
> I am receiving a compile error :wrong number of arguments or invalid property assignment.


> Dim ParticipantName As Integer
>
>ParticpantName = DCount("ID", "Combobox query", "volID", "Particvolu", "Relationship" = " & cboName & " And [particvolu] = "Participant")
>

On Thu, 20 May 2010 11:20:05 -0700, nancy <(E-Mail Removed)>
wrote:

>I am wanting my program to look at a name in the combo box and determine if
>the name is also a participant.
>
>I am receiving a compile error : variant not defined.
>when it does this it highlights Particvolu of "particvolu =" Participant"
>
>
>Dim ParticipantName As Integer
>
>ParticpantName = DCount("ID", "Combobox query", "volID", "Particvolu" = " &
>cboName & " And Particvolu = "Participant")

Your quotes are messed up. The third argument to DCount() needs to be a
String, which can be constructed by tacking together string constants
delimited by ", such as "[Particvolu] =", and variable values such as the
names of controls on the form.

This is made more complicated by the fact that a Text criterion must be
delimited by quotemarks, and putting quotemarks inside a string constant
delimited by quotemarks can be confusing (it's possible, just confusing!)

The other thing that's confusing about your example is that you're apparently
asking for records where the field named Particvolu is - at one and the same
time - equal to the value found in the combo box, and ALSO equal to the text
string "Participant". That's probably never going to happen! What is in the
field [Particvolu]? Where is the information that a person is a Participant to
be found in your table? What field contains the participant's name? What is
the value of the combo box: a name (which might not be unique) or a numeric
ParticipantID?
--

John W. Vinson [MVP]

Maybe I am going about this the wrong way. ..

The names from the combo box come from a query called “Combobox query�,
where it is joining two tables. The combobox name on the form is “cboName�

The SQL from the Combobox Query is: SELECT table1.volID, volName & " " &
VolLastName AS Expr1, Address1.Inactive, Address1.IDFROM Address1 RIGHT JOIN
table1 ON Address1.ID = table1.ADDRESS1_ID WHERE (((Address1.Inactive)=No))
ORDER BY table1.VolName;

I have also used another query that selects the name if it was a participant
and active:
The SQL for it is “SELECT table1.volID, volName & " " & VolLastName AS
Expr1, Address1.Inactive, Address1.ID, Address1.Particvolu FROM Address1
RIGHT JOIN table1 ON Address1.ID=table1.ADDRESS1_ID WHERE
(((Address1.Inactive)=No) AND ((Address1.Particvolu)="Participant")) ORDER
BY table1.VolName;�

I would like to use the combo box that comes from “Combobox query�, because
I am using it for another macro on the same form.

To answer your question What is in the field [Particvolu] It is a drop
down box that contains either participant or volunteer.

Maybe I could do something like.
If (cboName) <> 0 and [address1.particvolu] = “participant� then…
Suggestions?

On Thu, 20 May 2010 15:11:01 -0700, nancy <(E-Mail Removed)>
wrote:

>Maybe I am going about this the wrong way. ..
>
>The names from the combo box come from a query called “Combobox query”,
>where it is joining two tables. The combobox name on the form is “cboName”
>
>The SQL from the Combobox Query is: SELECT table1.volID, volName & " " &
>VolLastName AS Expr1, Address1.Inactive, Address1.IDFROM Address1 RIGHT JOIN
>table1 ON Address1.ID = table1.ADDRESS1_ID WHERE (((Address1.Inactive)=No))
> ORDER BY table1.VolName;
>
>I have also used another query that selects the name if it was a participant
>and active:
>The SQL for it is “SELECT table1.volID, volName & " " & VolLastName AS
>Expr1, Address1.Inactive, Address1.ID, Address1.Particvolu FROM Address1
>RIGHT JOIN table1 ON Address1.ID=table1.ADDRESS1_ID WHERE
>(((Address1.Inactive)=No) AND ((Address1.Particvolu)="Participant")) ORDER
>BY table1.VolName;”
>
>I would like to use the combo box that comes from “Combobox query”, because
>I am using it for another macro on the same form.
>
>To answer your question What is in the field [Particvolu] It is a drop
>down box that contains either participant or volunteer.
>
>Maybe I could do something like.
>If (cboName) <> 0 and [address1.particvolu] = “participant” then…
>Suggestions?

A field *is not a dropdown box*.

A field is *DATA* - text, or a number, or a date.

A dropdown box is not data; it's a *tool for displaying data*.

I'm still not at all clear what you're trying to do. What does it mean that
"cboName <> 0"? Is the user selecting a name from the combo box? If so does it
matter which name they select? If there are 8124 records in Address1 and you
ask if [address1].[particvolu] is equal to "Participant", and it is for 3489
of them and is not for the rest, what result do you want?
--

John W. Vinson [MVP]