Solved Incorrect output when filing report (1 Viewer)

[Sam Summers]

This is rather annoying and baffling at the moment.

This whole little system works but there are two anomalies preventing it from being perfect.

The first is in the 'control measures' section of the 'Main body' report where i am getting duplicates which i really need to prevent.

And secondly when i select 'View selected Risk assessment' from the 'View risk assessments' form, in the safety equipment required boxes they are set correctly as either 'Yes' or 'No' (Y or N) but when i select 'File Risk Assessment' the saved pdf report has them all set to 'No'

I am still working on this myself to try and solve it.

Many thanks if you can help

I am using access 2019 and you will need to use the bypass key

 

[The_Doc_Man]

Since I don't have Ac2019, I won't even try to open the file. When you get duplicates, it USUALLY means you have something else that isn't in the WHERE clause - but SHOULD be. Like, maybe another field in the record and there are two records with the same selectors you put in the WHERE clause but different values in the unspecified field. As to the report problem, remember that reports "roll their own" SQL for ORDER BY and GROUP BY, so is there a chance you are getting caught on reports with a mind of their own?

 

[Eugene-LS]

The first is in the 'control measures' section of the 'Main body' report where i am getting duplicates which i really need to prevent.

Report "SafetyInfoControlsubreport"
Set RecordSource Property to:

SELECT RiskAssessmentID, Activity.ActivityID, ControlMeasure, HazardRef
FROM (Hazard INNER JOIN (Activity INNER JOIN ActivityHazard
    ON Activity.ActivityID = ActivityHazard.ActivityID)
    ON Hazard.HazardID = ActivityHazard.HazardID)
INNER JOIN HazardControlMeasures
    ON Hazard.HazardID = HazardControlMeasures.HazardRef
ORDER BY ControlMeasure;

 

[Eugene-LS]

And secondly when i select 'View selected Risk assessment' from the 'View risk assessments' form, in the safety equipment required boxes they are set correctly as either 'Yes' or 'No' (Y or N) but when i select 'File Risk Assessment' the saved pdf report has them all set to 'No'

I couldn't figure out how it works for you.

 

[arnelgp]

hello, how are u?
i move your code from Report_Activate to Detail_Format (Mainbody Report).
so it is now "reflecting" in the PDF the "Yes"(No).

now there is printing an extra "blank" page, so i adjust your
PageBreak Control, i moved it a little higher to the page.

also there is additional "code" on the "file button" so the two
pdf will correctly save to the Folder you choose.

 

[Sam Summers]

hello, how are u?
i move your code from Report_Activate to Detail_Format (Mainbody Report).
so it is now "reflecting" in the PDF the "Yes"(No).

now there is printing an extra "blank" page, so i adjust your
PageBreak Control, i moved it a little higher to the page.

also there is additional "code" on the "file button" so the two
pdf will correctly save to the Folder you choose.

Hi Arnel, I'm ok but lost my job and hence haven't been on here for a while as my projects were no longer needed.

 

[Sam Summers]

Once again guys you have done it! Thank you all SO SO much and feel free to use what i have created if it is of use to you.

Arnelgp previously solved some big issues for me along with other members but i think it is now far better thanks to you guys.
I can design but do not have quite the knowledge that you guys have.
Thank you!!!