DCount with two tables

[Guest]

I have two tables:
Student table with fields of: StudentID and Status (values of Current, New,
Past) plus other fields.
Activity table with fields of: StudentID and Activity plus other fields.

The tables have a one (student table) to many (Activity table) relationship

I have a form where the user selects (using check boxes) a status
(ckCurrent, ckNew or ckPast) and then clicks on a command button which will
produce a report. In the on-click event of the command button I want to use
Dcount to insure that there are entries in the activity table for students
with the selected status? Can this be done, where two tables are involved?

Example. If the user selects “ckPast†and there are no entries in the
activity table for students with a status of Past, I want to display a
message that there are no students for the selected status.

Any help would be appreciated.
Thanks

 

[Guest]

You can't DCount multiple tables at once, but you can DCount a query that
combines multiple tables.
Write a query that returns StudentId, Status and Activity, then use the
query in your DCount function

 

[Guest]

Thanks Chris,

I created the query and have the following code in my on-click event:
-------------
strcriteria = "([Status] = 'Current' And [ckStatusCurrent] = -1) Or " & _
"([Status] = 'New' And [ckStatusNew] = -1) Or " & _
"([Status] = 'Past' And [ckStatusPast] = -1)"


intx = DCount("[StudentID]", "ActivityStatusQuery", strcriteria)
--------------
I get the following error when exceuiting the DCount line:

The expression you entered has a query parameter produced the following
error: The object dosen't contain the automation object 'StudentID'

Any ideas???

Thanks

 

[Guest]

OK, Its saying that you don't have StudentID in your "ActivityStatusQuery"
query.
If it looks like you do have such a field, check the field in the base table
and make sure it doesn't have a caption set. Access tends to propagate these
or not at inconvenient times.

Thanks Chris,

I created the query and have the following code in my on-click event:
-------------
strcriteria = "([Status] = 'Current' And [ckStatusCurrent] = -1) Or " & _
"([Status] = 'New' And [ckStatusNew] = -1) Or " & _
"([Status] = 'Past' And [ckStatusPast] = -1)"


intx = DCount("[StudentID]", "ActivityStatusQuery", strcriteria)
--------------
I get the following error when exceuiting the DCount line:

The expression you entered has a query parameter produced the following
error: The object dosen't contain the automation object 'StudentID'

Any ideas???

Thanks

You can't DCount multiple tables at once, but you can DCount a query that
combines multiple tables.
Write a query that returns StudentId, Status and Activity, then use the
query in your DCount function

 

[Guest]

Thanks again Chris

My base tables looked fine, but when I checked the field names in my query
the StudentID field was strange, so I deleted the field and re-added it and
now everything works great.

Thanks for your help

OK, Its saying that you don't have StudentID in your "ActivityStatusQuery"
query.
If it looks like you do have such a field, check the field in the base table
and make sure it doesn't have a caption set. Access tends to propagate these
or not at inconvenient times.

Thanks Chris,

I created the query and have the following code in my on-click event:
-------------
strcriteria = "([Status] = 'Current' And [ckStatusCurrent] = -1) Or " & _
"([Status] = 'New' And [ckStatusNew] = -1) Or " & _
"([Status] = 'Past' And [ckStatusPast] = -1)"


intx = DCount("[StudentID]", "ActivityStatusQuery", strcriteria)
--------------
I get the following error when exceuiting the DCount line:

The expression you entered has a query parameter produced the following
error: The object dosen't contain the automation object 'StudentID'

Any ideas???

Thanks

You can't DCount multiple tables at once, but you can DCount a query that
combines multiple tables.
Write a query that returns StudentId, Status and Activity, then use the
query in your DCount function

:

I have two tables:
Student table with fields of: StudentID and Status (values of Current, New,
Past) plus other fields.
Activity table with fields of: StudentID and Activity plus other fields.

The tables have a one (student table) to many (Activity table) relationship

I have a form where the user selects (using check boxes) a status
(ckCurrent, ckNew or ckPast) and then clicks on a command button which will
produce a report. In the on-click event of the command button I want to use
Dcount to insure that there are entries in the activity table for students
with the selected status? Can this be done, where two tables are involved?

Example. If the user selects “ckPast†and there are no entries in the
activity table for students with a status of Past, I want to display a
message that there are no students for the selected status.

Any help would be appreciated.
Thanks