direction in setting of a collection (please)

[markjkubicki]

initially I had a continuous report with multiple text controls at each record



at individual instances of the report, the visibility of those controls as well as the specific content them is effected by the values of an unrelated table of parameters (tblReportParms) (it only has one record) and the visibility/content of the controls is determined during the DetailFormat event of the report.

Rather than DLookUp the values of tblReportParms at each record (at each DetailFormat event for that record), I read those values into a collection at the ReportLoad event, and then refer to them as necessary (as a variable ?).

to set up the collection:

    Dim dbs As Database
    Dim rst As DAO.Recordset
    Dim vField As DAO.Field
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tbeReportParms")

    For Each vField In rst1.Fields
        ReportParms.Add vField.Value, "v" & vField.Name
    Next vField

in the DetailFormat event of the report, to use those values
(like this...):

        If ReportParms!vShortDescription = "false" Then
            strDescription = strDescription & strBaseDescription
        Else
            If InStr(1, strBaseDescription, ". ") > 0 Then
                strDescription = strDescription & Mid(strBaseDescription, 1, InStr(1, strBaseDescription, ". "))
            Else
                strDescription = strDescription & strBaseDescription
            End If
        End If

_____________
the report has been reformatted to combine the text controls onto multiple subReports -but the "effected" visibility and content of the control is still relevant. All of the subReports still need to make reference to the values of tblReportParms. Perplexingly, creating the collection at the main Report doesn't work as the subReports are opened first (and the necessary values will not yet been created...)

AND HERE'S WHERE I AM LOST...
Behind the command control that will trigger the report to open (but in front of the code line that actually opens the report), I thought that I could create a collection (?) as I had previously done (with the values of tblReportParms), store the values as public(?) variables and then reference them in the same way... THIS is where I am making a mess of things, running in circles, and into dead ends.

currently, I have (going back to where I started after many iterations):
at the top of the module that contains code for the report open event:

    Public dbs As Database
    Public rst As DAO.Recordset
    Public vField As DAO.Field

at the ReportOpen event (in the report's module):

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tbeReportParms")

    For Each vField In rst1.Fields
        ReportParms.Add vField.Value, "v" & vField.Name
    Next vField

[/CODE]
and then in the DetailFormat event of the main Report AND each of the subReports...
(like this...):

        If ReportParms!vShortDescription = "false" Then
            strDescription = strDescription & strBaseDescription
        Else
            If InStr(1, strBaseDescription, ". ") > 0 Then
                strDescription = strDescription & Mid(strBaseDescription, 1, InStr(1, strBaseDescription, ". "))
            Else
                strDescription = strDescription & strBaseDescription
            End If
        End If

but it is obviously deficient !
ANY thoughts, direction, or kind words will be greatly appreciated in advance,
mark

[Micron]

You don't seem to be creating or using a collection object (a collection is a special type of object or container). This looks awfully complicated for what I think you are trying to achieve. Perhaps it's not as simple as I read it.

The parameter value is a field in each report record? If so, consider Conditional formatting on the controls. I know you can conditionally format a control & AFAIK you can also set its visibility.

[davegri]

I don't follow what you're doing, but if you need some variables available throughout your code in any module, I would use Tempvars instead of a global collection.

    dim rst as dao.recordset
    Set rst = CurrentDB.OpenRecordset("tbeReportParms")
    rst.movelast
    rst.movefirst
    Do While Not rst.eof
    
        Tempvars.add "tvField1", rst!field1
        Tempvars.add "tvField2", rst!field2
   and so on....
        rst.movenext
    Loop

Do something like above at startup, then you can refer to any of the tempvars, like

strDescription = Tempvars!tvField1

[markjkubicki]

this might be it!
(thnx)

off in on different focus at the moment; never-the-less, I'll give it a go ASAP, and let you know how it turns out...

[markjkubicki]

this might be it!
(thnx)

off in on different focus at the moment; never-the-less, I'll give it a go ASAP, and let you know how it turns out...

That worked.
...TempVars ....Lear something new every day!
Thnx

[davegri]

Access is vast, and has a vast user base. We all learn from each other. This is the place.