Exclude records from a report using programming.

[jubu]

Database (Access 2003) for applicants and nominations.

tblMasterApplicant contains a field ApplicantID which is linked one-to-many
to the field ApplicantID in tblNominations. Reports are generated from
queries based on tblMasterApplicant and other linked tables as well. From a
form, the user selects a report name from a combo box which generates the
desired report. Currently the tblNominations is NOT in the queries that
populate the reports. I would like to program a checkbox or command button
on this form, so that when it is selected, and the user selects a report
name, the report now looks at the tblNominations table and EXCLUDES anyone
that is in that table. Any suggestions would be much appreciated. Thanks in
advance.

 

[Alex Dybenko]

Hi,
you can add a following condition to docmd.openreport:

if me![somechekbox]=true then
docmd.openreport "myreport",,,"ApplicantID not in (select ApplicantID
from tblNominations)"
end if

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[jubu]

Thanks for the info, however, when I tried it on one of the reports, the
following error message appeared: Run time error '2585' - This action can't
be carried out while processing a form or report event.

Because there are about 135 different reports with different criteria, I was
hoping to do this through a form event from the form where they select the
report name from the combo box and click the checkbox to exclude nominees.
Would it even be possible from the form?
--
jubu

Hi,
you can add a following condition to docmd.openreport:

if me![somechekbox]=true then
docmd.openreport "myreport",,,"ApplicantID not in (select ApplicantID
from tblNominations)"
end if

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Database (Access 2003) for applicants and nominations.

tblMasterApplicant contains a field ApplicantID which is linked
one-to-many
to the field ApplicantID in tblNominations. Reports are generated from
queries based on tblMasterApplicant and other linked tables as well. From
a
form, the user selects a report name from a combo box which generates the
desired report. Currently the tblNominations is NOT in the queries that
populate the reports. I would like to program a checkbox or command
button
on this form, so that when it is selected, and the user selects a report
name, the report now looks at the tblNominations table and EXCLUDES anyone
that is in that table. Any suggestions would be much appreciated. Thanks
in
advance.

 

[Alex Dybenko]

Hi,
first of all - check that where condition I wrote is correct (not sure about
fields and tables names)
then try to debug and check where this error occurs

Because there are about 135 different reports with different criteria, I
was
hoping to do this through a form event from the form where they select the
report name from the combo box and click the checkbox to exclude nominees.
Would it even be possible from the form?

yes, this was my idea also, instead of "myreport" you can pass the name of
report

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Thanks for the info, however, when I tried it on one of the reports, the
following error message appeared: Run time error '2585' - This action
can't
be carried out while processing a form or report event.

--
jubu

Hi,
you can add a following condition to docmd.openreport:

if me![somechekbox]=true then
docmd.openreport "myreport",,,"ApplicantID not in (select
ApplicantID
from tblNominations)"
end if

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Database (Access 2003) for applicants and nominations.

tblMasterApplicant contains a field ApplicantID which is linked
one-to-many
to the field ApplicantID in tblNominations. Reports are generated from
queries based on tblMasterApplicant and other linked tables as well.
From
a
form, the user selects a report name from a combo box which generates
the
desired report. Currently the tblNominations is NOT in the queries
that
populate the reports. I would like to program a checkbox or command
button
on this form, so that when it is selected, and the user selects a
report
name, the report now looks at the tblNominations table and EXCLUDES
anyone
that is in that table. Any suggestions would be much appreciated.
Thanks
in
advance.

 

[jubu]

I apologize for the delay in replying to your latest response. I found that
another If statement that I had in the event was causing me some problems,
and this morning I discovered that if I switch the order of the two If
statements, it works! Thank you so much for your assistance.
--
jubu

Hi,
first of all - check that where condition I wrote is correct (not sure about
fields and tables names)
then try to debug and check where this error occurs

Because there are about 135 different reports with different criteria, I
was
hoping to do this through a form event from the form where they select the
report name from the combo box and click the checkbox to exclude nominees.
Would it even be possible from the form?

yes, this was my idea also, instead of "myreport" you can pass the name of
report

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Thanks for the info, however, when I tried it on one of the reports, the
following error message appeared: Run time error '2585' - This action
can't
be carried out while processing a form or report event.

--
jubu

Hi,
you can add a following condition to docmd.openreport:

if me![somechekbox]=true then
docmd.openreport "myreport",,,"ApplicantID not in (select
ApplicantID
from tblNominations)"
end if

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Database (Access 2003) for applicants and nominations.

tblMasterApplicant contains a field ApplicantID which is linked
one-to-many
to the field ApplicantID in tblNominations. Reports are generated from
queries based on tblMasterApplicant and other linked tables as well.
From
a
form, the user selects a report name from a combo box which generates
the
desired report. Currently the tblNominations is NOT in the queries
that
populate the reports. I would like to program a checkbox or command
button
on this form, so that when it is selected, and the user selects a
report
name, the report now looks at the tblNominations table and EXCLUDES
anyone
that is in that table. Any suggestions would be much appreciated.
Thanks
in
advance.