[AccessD] Painted into a corner

For the heck of it, let me post the dynamic column query code for you.  Use
it as you please.

For this sample, we will assume:
- The name of the query is qryRptXTab.
- The Report contains multiple unbound textboxes named txtCol1, txtCol2,
txtCol3...

Private Sub Report_Open ()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim fld As DAO.Field
    Dim lngCol As Long

    Set db = DBEngine(0)(0)
    Set qdf = db.QueryDefs("qryRptXTab")

    'Because this query references form controls, we need to evaluate 
    'them and supply the control values to the query

    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm

    For Each fld In qdf.Fields
        Select Case fld.Name
        Case "MyRowFieldName","MyOtherRowFieldName"
            'These fields are setup at design time; skip them
        Case Else
            'Dynamically fill the unbound textboxes with the remaining
fields
            lngCol = lngCol + 1
            Me("txtCol" & lngCol).ControlSource = fld.Name
        End Select
    Next fld

    Set db = Nothing
    Set qdf = Nothing

End Sub

-----Original Message-----
From: John Clark [mailto:John.Clark at niagaracounty.com]
Sent: Wednesday, March 12, 2003 3:23 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Painted into a corner


I was just reading something in the MS knowledgebase, right before I
read this Email, and I was thinking, "Don't tell me..." And, now you are
giving me confirmation..."you can't get there from here." I entered in
the names in the properties and it worked like a charm...again I learn
something. 

The whole point of going about it the way I did though(i.e.
dynamically), was to allow for flexibility. Therefore, typing in the
names, severely defeats the purpose. Yesterday, I had come up with a way
of getting the same information into the report, only it was in a
different format. I did this by using three separate queries, and three
sub-reports. I am going ahead with that, and I added a button on my
reports menu that runs the query, and the can print that if they need
to. I might just take it out, after I let them see it.

Thank you for your help!

John W Clark