report with 'standard' subreport

[jovobas]

I've created a database to keep track of applications for disability.
There are four tables involved: tbl_applicants, tbl_applications,
tbl_disabilities and tbl_applications_disabilities (relational table
connecting the application with the disability).

At the end, a report is made per application containing a subreport
with the listed disabilities. The easy part is creating the report so
only those disabilities that were applied for are printed.
The hard part is making it sure every report displays the whole list
of disabilities with the applied ones checked.

So far I've created a query with subquery that looks like this
select * from tbl_disabilities left join (select * from
tbl_applications_disabilities where appl_id=Reports![myreport]!
[application_ID])
which gives me exactly what I want: all disabilities with a reference
if they were applied for.
This is the recordsource for the subreport which is not linked to the
main report.

It works but with one major disadvantage: it is only usable for 1
subreport. Because I need several main reports for different parties,
but all with the same subreport, I'm stuck with making a subreport and
query for each one. Not to mention the maintenance when something
would change.

Is there a more elegant way to achieve my wishes?
Thanks in advance for any insights.

 

[Tom Wickerath]

Hi Jovobas,

It works but with one major disadvantage: it is only usable for 1
subreport. Because I need several main reports for different parties,
but all with the same subreport, I'm stuck with making a subreport and
query for each one.

You should be able to use the same subreport for any number of reports, if
each of the reports should show the same subreport. Perhaps the reason you
are having troubles is that the query for your subreport includes a WHERE
clause:

where appl_id=Reports![myreport]![application_ID])

that specifies a hard-coded report name: [myreport].

Have you tried removing this WHERE clause, and inserting the appropriate
field names into the Link Child Fields ([appl_id]) and Link Master Fields
([application_ID]) properties of the subreport container (the control that
houses the subreport). You should find these two properties on the Data tab
of the Properties dialog for this control.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I've created a database to keep track of applications for disability.
There are four tables involved: tbl_applicants, tbl_applications,
tbl_disabilities and tbl_applications_disabilities (relational table
connecting the application with the disability).

At the end, a report is made per application containing a subreport
with the listed disabilities. The easy part is creating the report so
only those disabilities that were applied for are printed.
The hard part is making it sure every report displays the whole list
of disabilities with the applied ones checked.

So far I've created a query with subquery that looks like this
select * from tbl_disabilities left join (select * from
tbl_applications_disabilities where appl_id=Reports![myreport]!
[application_ID])
which gives me exactly what I want: all disabilities with a reference
if they were applied for.
This is the recordsource for the subreport which is not linked to the
main report.

It works but with one major disadvantage: it is only usable for 1
subreport. Because I need several main reports for different parties,
but all with the same subreport, I'm stuck with making a subreport and
query for each one. Not to mention the maintenance when something
would change.

Is there a more elegant way to achieve my wishes?
Thanks in advance for any insights.

 

[Roald]

Hi Tom,

thanks for the suggestion. I had already tried that without success.
The removal of the where clause shrinks the query (and the subreport)
to only those disabilities that are applied for, and not generating a
'standard' subreport.
Meanwhile I have tried the following:
select * from tbl_applications_disabilities as tbl_ad right join
[select * from tbl_applications, tbl_disabilities]. as qry_ad on
tbl_ad.disability_id=qry_ad.disability_id and
tbl_ad.appl_id=qry_ad.appl.id

This query generates an outer join (qry_ad) with all possible
combinations of applications and disabilities which is than left
joined with the relations table on both id's. It return all possible
combinations with information wether or not that combination exists
for any application.
I can use this query in the subreport and link qry_ad.appl_id with the
appl_id in the master report(s). YES.

I had hoped to use a similar query for the matching input form with
'inconsistent update', but that was pushing my luck . I will use the
same query and use some clever VBA-scripting to update or insert
records.

Tnx
Jovobas

HiJovobas,

It works but with one major disadvantage: it is only usable for 1
subreport. Because I need several main reports for different parties,
but all with the same subreport, I'm stuck with making a subreport and
query for each one.

You should be able to use the same subreport for any number of reports, if
each of the reports should show the same subreport. Perhaps the reason you
are having troubles is that the query for your subreport includes a WHERE
clause:

     where appl_id=Reports![myreport]![application_ID])

that specifies a hard-coded report name: [myreport].

Have you tried removing this WHERE clause, and inserting the appropriate
field names into the Link Child Fields ([appl_id]) and Link Master Fields
([application_ID]) properties of the subreport container (the control that
houses the subreport). You should find these two properties on the Data tab
of the Properties dialog for this control.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________

I've created a database to keep track of applications for disability.
There are four tables involved: tbl_applicants, tbl_applications,
tbl_disabilities and tbl_applications_disabilities (relational table
connecting the application with the disability).

At the end, a report is made per application containing a subreport
with the listed disabilities. The easy part is creating the report so
only those disabilities that were applied for are printed.
The hard part is making it sure every report displays the whole list
of disabilities with the applied ones checked.

So far I've created a query with subquery that looks like this
select * from tbl_disabilities left join (select * from
tbl_applications_disabilities where appl_id=Reports![myreport]!
[application_ID])
which gives me exactly what I want: all disabilities with a reference
if they were applied for.
This is the recordsource for the subreport which is not linked to the
main report.

It works but with one major disadvantage: it is only usable for 1
subreport. Because I need several main reports for different parties,
but all with the same subreport, I'm stuck with making a subreport and
query for each one. Not to mention the maintenance when something
would change.

Is there a more elegant way to achieve my wishes?
Thanks in advance for any insights.

 

[Roald]

Good news,

the inconsistent update feature does work. However it has some very
weird behavior:
the query specified in the previous post was made dynaset
(inconsistent updates). Updating information works fine with no
anomalies. However when i add a new record in the relation table by
entering data in the right columns (special care to foreign keys!!!),
Access warns me that the record has been removed.

That's only the half of it: because the data entered IS saved in the
database. When I close and reopen the query: the inserted data is
there. The same happens when I try to enter data in the form that goes
with it: changing data is flawless, inserting data also works, but
when I leave the record, access tries to convince me the data has been
removed. Requerying or recalculating the form does help, only closing
and reopening shows the newly inserted data.

Has anyone encountered this behavior before? And if so, what can you
do about it?
Greetings
Jovobas

Hi Tom,

thanks for the suggestion. I had already tried that without success.
The removal of the where clause shrinks the query (and the subreport)
to only those disabilities that are applied for, and not generating a
'standard' subreport.
Meanwhile I have tried the following:
select * from tbl_applications_disabilities as tbl_ad right join
[select * from tbl_applications, tbl_disabilities]. as qry_ad on
tbl_ad.disability_id=qry_ad.disability_id and
tbl_ad.appl_id=qry_ad.appl.id

This query generates an outer join (qry_ad) with all possible
combinations of applications and disabilities which is than left
joined with the relations table on both id's. It return all possible
combinations with information wether or not that combination exists
for any application.
I can use this query in the subreport and link qry_ad.appl_id with the
appl_id in the master report(s). YES.

I had hoped to use a similar query for the matching input form with
'inconsistent update', but that was pushing my luck . I will use the
same query and use some clever VBA-scripting to update or insert
records.

TnxJovobas

HiJovobas,

You should be able to use the same subreport for any number of reports,if
each of the reports should show the same subreport. Perhaps the reason you
are having troubles is that the query for your subreport includes a WHERE
clause:

     where appl_id=Reports![myreport]![application_ID])

that specifies a hard-coded report name: [myreport].

Have you tried removing this WHERE clause, and inserting the appropriate
field names into the Link Child Fields ([appl_id]) and Link Master Fields
([application_ID]) properties of the subreport container (the control that
houses the subreport). You should find these two properties on the Datatab
of the Properties dialog for this control.

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/
__________________________________________

I've created a database to keep track of applications for disability.
There are four tables involved: tbl_applicants, tbl_applications,
tbl_disabilities and tbl_applications_disabilities (relational table
connecting the application with the disability).
At the end, a report is made per application containing a subreport
with the listed disabilities. The easy part is creating the report so
only those disabilities that were applied for are printed.
The hard part is making it sure every report displays the whole list
of disabilities with the applied ones checked.
So far I've created a query with subquery that looks like this
select * from tbl_disabilities left join (select * from
tbl_applications_disabilities where appl_id=Reports![myreport]!
[application_ID])
which gives me exactly what I want: all disabilities with a reference
if they were applied for.
This is the recordsource for the subreport which is not linked to the
main report.
It works but with one major disadvantage: it is only usable for 1
subreport. Because I need several main reports for different parties,
but all with the same subreport, I'm stuck with making a subreport and
query for each one. Not to mention the maintenance when something
would change.
Is there a more elegant way to achieve my wishes?
Thanks in advance for any insights.

 

[Roald]

On the matter of the weird behavior of the inconsistent updates:
In the forms I'm using a simple me.recalc in the AfterUpdate event
didn't do the trick for some reason. So with a little trial and error
I came up with this solution in the AfterUpdate-section:
Application.Echo False
Me.FilterOn = False
Me.Recalc
Me.FilterOn = True
Application.Echo True
This freezes the app, disables the filter, recalcs (requery works too)
the form and turns everything back on. It works the way it should: no
more ambiguous "#removed" alerts from access. There is off course a
lag time which is not so nice for the user, but it beats the
alternative.

Greetings
jovobas