"Penny" <pe***@spampolice.com> schreef in bericht
news:11***************@angel.amnet.net.au...
Bas,
Thanks for your reply.
Easiest way is to use the cross-tab report wizard, and then look at the
result (in detail; especially the code behind the report).
I attempted this but [..]
Penny,
I learned this (many years ago, in Access95) by using the cross-tab report
wizard. I tried to find this now (in Access2000), and I did not succeed. I
imagine that this wizard is no longer standard available, hence you looked
in the wrong area.
The trick is to make a report dynamic by creating too many (say 20) fields,
and using code during the build-up of a page to populate these dummy fields
with real data. In reporting this is complex by the way. It is done by
opening a second recordset from vb-code behind the report, and using the
various events to fill the dummy fields and hide the dummy fields that are
not used.
In forms this would be a lot easier. You can do it by creating a custom
function that generates the data for a list box control.
This is known as a call back function. Set the RowSourceType of the list box
to the name of your function and keep the RowSource empty. You should be
able to find this in the help files.
Basically a callback function is a function that is called a number of times
during the build up of a form. Each time a different parameter is passed,
and depending on the parameter, the function gives different output. Below
is the example that I found in the help files, and the code should be easily
understood (despite it's in Dutch).
To work this around to your situation, focus on the parameter Code. The
important stages are "Initialise", "GetRowCount", GetColumnCount" and
"GetValue".
You'd need to define a (global !) recordset that is
- initialised and opened during "Initialise", and
- probed for the number of columns (rst.fields.count),
- probed for then number of rows (rst.recordcount)
- read for the data (rst.absoluteposition = row, rst.fields(col).value)
I tried this just now, but Access crashed during the test prior to saving
the data, so I can't give you the real example. I know it's possible though
(and be sure to frequently save up your work !)
Good luck,
Bas.
(Copy from the Access-help files)
Function LijstMaandagen(vld As Control,id As Variant, _
rij As Variant,kol As Variant,code As Variant) _
As Variant
Dim intOffset As Integer
Select Case code
Case acLBInitialize ' Initialiseren.
LijstMaandagen = True
Case acLBOpen ' Openen.
LijstMaandagen = Timer ' Unieke id.
Case acLBGetRowCount ' Aantal rijen ophalen.
LijstMaandagen = 4
Case acLBGetColumnCount ' Aantal kolommen ophalen.
LijstMaandagen = 1
Case acLBGetColumnWidth ' Kolombreedte ophalen.
LijstMaandagen = -1 ' Standaardbreedte gebruiken.
Case acLBGetValue ' Gegevens ophalen.
intOffset = Abs((9 - Weekday(Now))Mod 7)
LijstMaandagen = Format(Now() + _
intOffset + 7 * rij,"d mmmm")
End Select
End Function
