How do I bind a report field in Report_Open when I build the record Source on the fly?-CodePudding

For an existing report, I build the record source in the Report_Open event, so that it looks like this (it varies according to values passed in OpenArgs):

> SELECT tblSpecies.commonname AS Species,
>        Month(tblLocationVisit.VisitDate) AS qMonthNumber, 
>        MonthName(Month(tblLocationVisit.visitdate)) AS qMonth,
>        (SUM(Nz(tblSighting.Adults))   SUM(Nz(tblSighting.Emerging))) AS TotalAdults, 
>        tblSighting.SpeciesID    FROM tblSpecies INNER JOIN
>                    ((tblSite INNER JOIN tblLocationVisit ON tblSite.ID = 
         tblLocationVisit.SiteID) 
>                    INNER JOIN
>                      tblSighting ON tblLocationVisit.ID = tblSighting.LocationVisitID) ON 
>                      tblSpecies.ID = tblSighting.SpeciesID    GROUP BY  
                       tblSpecies.commonname, Month(VisitDate), MonthName(Month(visitdate)), 
>                      tblSighting.SpeciesID    HAVING (((SUM(Nz(tblSighting.Adults)))>0) OR 
                       (SUM(Nz(tblSighting.Emerging)) > 0));

Species, MonthName, TotalAdults and a sum of TotalAdults are the only values presented in the report and it all works. In the design sheet for the report, there is a static query given as the Record Source but this is replaced in the Report_Open event, as described above. The static query exists purely for binding the report fields.

I now want to add a count to the report, so that:

> SELECT COUNT(tblLocationVisit.ID) AS qVisitCount,
>        tblSpecies.commonname AS Species,
>        Month(tblLocationVisit.VisitDate) AS qMonthNumber, 
>        MonthName(Month(tblLocationVisit.visitdate)) AS qMonth,
>        (SUM(Nz(tblSighting.Adults))   SUM(Nz(tblSighting.Emerging))) AS TotalAdults, 
>        tblSighting.SpeciesID    FROM tblSpecies INNER JOIN
>                    ((tblSite INNER JOIN tblLocationVisit ON tblSite.ID = 
                      tblLocationVisit.SiteID) 
>                    INNER JOIN
>                      tblSighting ON tblLocationVisit.ID = tblSighting.LocationVisitID) ON 
>                      tblSpecies.ID = tblSighting.SpeciesID    GROUP BY  
               tblSpecies.commonname, Month(VisitDate), MonthName(Month(visitdate)), 
>              tblSighting.SpeciesID    HAVING (((SUM(Nz(tblSighting.Adults)))>0) OR 
               (SUM(Nz(tblSighting.Emerging)) > 0));

The new query works when run standalone; there is no "Order By" in the property sheet for the query. I have replaced the static query in the report's Record Source with the new query including the Count; I have also added code so that the Report_Open event adds the Count to the record source. The new report field, xVisitCount, is bound to qVisitCount in the static query.

On running the report, I am asked to "Enter Parameter Value" for xVisitCount, so the binding isn't working; whatever I enter there, appears in the report.

If I remove the control source for the new field, there is no "Enter Parameter Value" prompt and the report field values are blank.

I think I need to change the binding for the new display field (xVisitCount) during the Report_Open event, either before or after assigning Me.RecordSource but I don't know how to do this.

I developed the original project a couple of years ago and my MS Access, while never very strong, is now very rusty. I have tried to format the code sensibly but I'm not sure it will display very well - I may have even trashed the accuracy of the code but I'm hoping this won't prevent your understanding.

CodePudding user response：

If Access asks for the value of xVisitCount, it looks for this field in the record source.

To avoid any confusion, you should name the field the same everywhere, e.g. qVisitCount:

in the static query
the report field Name
the report field ControlSource
in the dynamic query.

See also How to debug dynamic SQL in VBA - copy your created SQL to a new query to make sure it returns the fields and data you expect it to.