Access report from dynamic crosstab query and vba for "manual" report generation

Question

I ran into the problem of generating reports on integrated access (for the complex, I mean data processing, a variable number of fields, etc.).
Let me explain in more detail some of the things I need to implement:

• Some fields should not be displayed according to some values ​​in the request
• If any record does not exist, a beautiful (very noticeable) message should appear instead of the values ​​that will be there (suppose, for example, that a record from 03/04/2009 in the date field exists, a record from 03/06/2009 in the date field also exists, but the record from 05/05/2009 does not exist. Before showing the data related to the last record, I should type something like "It was not shown on 03/05/2009/2009")
• A histogram that takes as the data not the values ​​in the records, but instead something else that is calculated by the set of records (for example, the average value of all estimates for a given date). The number of episodes in this diagram also changes according to the values ​​in the records; this diagram will not be in the details section, but instead in the page heading or some group heading.

It should also be noted that the query is a TRANSFORM query (more precisely, an INNER JOIN from many TRANSFORM queries), and therefore, the number of columns returned by the query varies. Although in the past I was not able to bind this request as a record source for a report, somehow Access stops complaining at the moment (can someone clarify this? Is this normal I should not worry about it and use it as a record source or should i avoid it?)

There are two options for achieving what I want (what I see now):

• Create a report without a record source and a large number of unrelated fields, as well as through several events (Report_Open, Section_Format, etc.) and use the DAO to manually set the values ​​of these fields. Changing a series of chart data is also possible through VBA.
• - VBA , .

, 2 , , 1 Excel ( DAO), , ( )

1, , :

• VBA, .
• - , , VBA Access.

, 2 , , , , , , , .

, :

• Access VBA?
• , ?
• , , - , ? Access Reports (, ?)

Answer 1

, .

Answer 2

:

VBA, , , .

# 1, , .

, , , - , , ControlSource ( , CrossTab). ControlSources . SQL - , :

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

, SELECT , , SQL, , ( report Recordset, ADO, .. ):

  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

, SELECT (.. ), , , , ControlSources .

, , Visible FALSE. . txtNN, NN - , 2 . ( , , OnOpen):

  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

, , , / . , , . - OnFormat . , , . - Lastname Firstname Firstname, , OnFormat / .

, , , / Access. Access - , .