ansaurus

Question

access report from dynamic crosstab query and vba to "manually" generate reports

Answer 1

+1 A:

Sounds like you want to dynamically create the report and avoid all the dummy text boxes.

Jeff O 2010-02-02 18:59:40

That's a really super article, but it seems overly complicated to me to create on-the-fly reports for something that's a repeating activity, and the only thing that changes is the exact number of columns in the crosstab depending on the particular data set.

David-W-Fenton 2010-02-02 23:01:28

You may find there are a minimum of 3-5 columns, so the rest could be created dynamically. Positioning would be as sophisticated in a cross-tab. What about openning the report programatically in design mode?

Jeff O 2010-02-03 02:28:32

To me, having to open any object in design mode is an error. For one, it won't work in an MDE/ACCDE. Additionally, if you're trying to add controls on the fly, you'll eventually run out, as there's a limit of 700-odd controls over the lifetime of the form/report, regardless of whether you delete some or don't save the object. So, in general, not advisable at all.

David-W-Fenton 2010-02-04 00:42:14

I thought that would present problems. Seems like a bit of a hack. I inherited an app that did this and seemed like the saving of the changes was a bit precarious.

Jeff O 2010-02-04 03:06:40

Answer 2

A:

In regard to:

I can't find a way to create new pages in the report with VBA, and thus I'm limited only to the first page.

Your solution #1 seems to assume an unbound report.

I think what I'd do is have the form the crosstab as the rowsource, so you'd have records to generate the pages, and then define your report's controls with no ControlSource (except for the controls that are bound to fields that are always present in the CrossTab). Then you could assign the ControlSources at runtime based on the particular columns. Here's the SQL for a crosstab grabbed from an app I'm working on now:

  TRANSFORM First(impNoMatch.PersonID) AS FirstOfPersonID
  SELECT impNoMatch.LastName, impNoMatch.FirstBame
  FROM impNoMatch
  GROUP BY impNoMatch.LastName, impNoMatch.FirstName
  PIVOT impNoMatch.Status;

Now, you know that the fields in the SELECT clause will always be present, so if you opened a recordset on the SQL string you are using and count the number of fields in the recordset's Fields collection (you can't use the report's Recordset unless it's an ADO recordset, i.e., not bound to the Recordsource):

  Dim strSQL As String
  Dim rsFields As DAO.Recordset
  Dim lngFieldCount As Long

  strSQL = Me.Recordsource
  Set rsFields = CurrentDB.OpenRecordset(strSQL)
  lngFieldCount = rsFields.Fields.Count

From that, since you know the number of fields in the SELECT statement (i.e., the row headings), you can calculate the number of dynamic controls you want to assign, and you can use this recordset's fields collection to assign the ControlSources and unhide the controls.

You'd start out with all your controls that will display the dynamic fields set so their Visible property is FALSE. You'd also use a naming convention for those controls. In the code below, I've used txtNN, where NN is the numeric index in the Fields collection formatted as 2 digits. Here's the code (which adds lines to what's listed above, and is run in the OnOpen event):

  Dim strSQL As String
  Dim rsFields As DAO.Recordset
  Dim lngFieldCount As Long
  Dim l As Long
  Dim strControlName As String

  strSQL = Me.RecordSource
  Set rsFields = CurrentDb.OpenRecordset(strSQL)
  lngFieldCount = rsFields.Fields.Count
  For l = 2 To lngFieldCount - 1
    strControlName = "txt" & Format(l, "00")
    Me(strControlName).ControlSource = rsFields.Fields(l).Name
    Me(strControlName).Visible = True
  Next l
  rsFields.Close
  Set rsFields = Nothing

Now, if you want to get fancy, you can reformat the controls, changing widths and horizontal/vertical position. If you do that, you have to do it in a different event, and it's a bit tricky to choose that. The only good place to put it is in a report group's header's OnFormat event. If you don't have any grouping, you can add one that doesn't do anything. In the case of my crosstab, a two-level sort on Lastname and Firstname and a header on the Firstname group with nothing in it is a good place to use the OnFormat event to change the appearance/layout of the controls on your report.

As to your question about how to learn how to do this, I recommend picking up an intermediate/advance Access programming book. The Access Developers Handbook is the gold standard on this, and includes tons of examples of programmatic control of reports.

David-W-Fenton 2010-02-02 23:31:27

Thanks for the answer. Although I used to prefer option #1, not being able to create pages manually has taken me to approach #2. Since the data to be displayed in the report is not precisely the data in the records, I've been using labels and manually setting their caption through VBA and DAO. Also, I've been using lots of Me.NextRecord=False and Me.NextRecord=True to fill values from DAO RecordSets that are not in the Reports RecordSource (lots of labor here so far, I must admit). I think, however, that I'll probably make it :)Also, thanks a lot for the documentation reference.

Marcelo Zabani 2010-02-04 14:24:09

It sounds to me like you'd be better off doing this in HTML or by automating Word. Access reports don't work that well with arbitrary output -- they are built around the assumption that the report surface is going to be tightly bound to the records behind it.

David-W-Fenton 2010-02-05 02:59:34

ansaurus

tags:

views:

answers:

access report from dynamic crosstab query and vba to "manually" generate reports

related questions