By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,663 Members | 2,157 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,663 IT Pros & Developers. It's quick & easy.

Crosstab forms: just the basics

P: n/a
(Access 2003 Multiuser Split DB, Windows XP Pro)

Hi All,

I would really appreciate just some basic tips on how to make a Crosstab
Form based on a Crosstab Query. The query always has the same number of
records(generated from a table of predefined 'timeslots'). The number of
columns(one for each Consultant) varies depending on how many of the
Consultants have a yes/no field('Participates') set to yes. Therefore the
Crosstab query generates the appropriate 'consultant/timeslot' grid
everytime.

The query needs to be manifested as a form so any one of the cells can be
double clicked into and that cells unique data sent to the OpenArgs of
another form. Obviously the form will need code to produce the correct
number of controls(text boxes) so the column count is correct depending on
how many Consultants are participating.

ANY pointers on how to get going on this would be greatly appreciated.

Regards,

Penny
Apr 9 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a

Easiest way is to use the cross-tab report wizard, and then look at the
result (in detail; especially the code behind the report).

Bas Hartkamp.
"Penny" <pe***@spampolice.com> schreef in bericht
news:11***************@angel.amnet.net.au...
(Access 2003 Multiuser Split DB, Windows XP Pro)

Hi All,

I would really appreciate just some basic tips on how to make a Crosstab
Form based on a Crosstab Query. The query always has the same number of
records(generated from a table of predefined 'timeslots'). The number of
columns(one for each Consultant) varies depending on how many of the
Consultants have a yes/no field('Participates') set to yes. Therefore the
Crosstab query generates the appropriate 'consultant/timeslot' grid
everytime.

The query needs to be manifested as a form so any one of the cells can be
double clicked into and that cells unique data sent to the OpenArgs of
another form. Obviously the form will need code to produce the correct
number of controls(text boxes) so the column count is correct depending on
how many Consultants are participating.

ANY pointers on how to get going on this would be greatly appreciated.

Regards,

Penny

Apr 11 '06 #2

P: n/a
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 because the crosstab query uses another query to
ascertain the number and details of the consultants(which will be the
columns), the report wizard has run the first query and the resultant
consultants are given as fields to be selected as columns in the 'select
queries/fields' page of the wizard. So the report it produces will not be
'dynamic' if you know what I mean.

I guess I need a way to 'read' the query results(records and columns) whilst
dynamically producing the controls for each on the form.

Kind Regards,

Penny

"HS Hartkamp" <ha**************@wanadoo.nl> wrote in message
news:44**********************@news.wanadoo.nl...
Easiest way is to use the cross-tab report wizard, and then look at the
result (in detail; especially the code behind the report).

Bas Hartkamp.
"Penny" <pe***@spampolice.com> schreef in bericht
news:11***************@angel.amnet.net.au...
(Access 2003 Multiuser Split DB, Windows XP Pro)

Hi All,

I would really appreciate just some basic tips on how to make a Crosstab
Form based on a Crosstab Query. The query always has the same number of
records(generated from a table of predefined 'timeslots'). The number of
columns(one for each Consultant) varies depending on how many of the
Consultants have a yes/no field('Participates') set to yes. Therefore the
Crosstab query generates the appropriate 'consultant/timeslot' grid
everytime.

The query needs to be manifested as a form so any one of the cells can be
double clicked into and that cells unique data sent to the OpenArgs of
another form. Obviously the form will need code to produce the correct
number of controls(text boxes) so the column count is correct depending
on how many Consultants are participating.

ANY pointers on how to get going on this would be greatly appreciated.

Regards,

Penny


Apr 12 '06 #3

P: n/a

"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


Apr 12 '06 #4

P: n/a
Penny,

I have an appopintments module that lists all the time slots down the left
side and the number of columns and the column fields can be configured to
whatever you need. My module would greatly simplify what you are trying to
do. You can enter your data directly into the module rather than clicking an
intersection of a row and column and opening another form. I can implement
the appointment module in your database for you for a reasonable fee. If you
are interested, contact me at my email address below. If you would like to
see a screen shot, I can send one to you.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"Penny" <pe***@spampolice.com> wrote in message
news:11***************@angel.amnet.net.au...
(Access 2003 Multiuser Split DB, Windows XP Pro)

Hi All,

I would really appreciate just some basic tips on how to make a Crosstab
Form based on a Crosstab Query. The query always has the same number of
records(generated from a table of predefined 'timeslots'). The number of
columns(one for each Consultant) varies depending on how many of the
Consultants have a yes/no field('Participates') set to yes. Therefore the
Crosstab query generates the appropriate 'consultant/timeslot' grid
everytime.

The query needs to be manifested as a form so any one of the cells can be
double clicked into and that cells unique data sent to the OpenArgs of
another form. Obviously the form will need code to produce the correct
number of controls(text boxes) so the column count is correct depending on
how many Consultants are participating.

ANY pointers on how to get going on this would be greatly appreciated.

Regards,

Penny

Apr 13 '06 #5

P: n/a
Penny, be careful. Steve has very poor ethicals and very questionable
technical skills.

Stevie, stevie stevie, you never learn. These newsgroups are for FREE
support not a vehicle for your lies, slurs and job hunting.

You have been a reasonable good boy for the past few weeks, but you have
fallen into your old bad habits. Is the job at the gas station not paying
enough?

John... Visio MVP
"PC D" <fa***@email.com> wrote in message
news:pE*****************@newsread3.news.atl.earthl ink.net...
Penny, I can implement the appointment module in your database for you for a
reasonable fee. If you are interested, contact me at my email address
below. If you would like to see a screen shot, I can send one to you.

Apr 13 '06 #6

P: n/a

"PC D" <fa***@email.com> schreef in bericht news:pE*****************@newsread3.news.atl.earthl ink.net...

<snipped all the advertising garbage here >

--
To Steve:
No-one wants your advertising/job hunting here!
Over 700 !! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R
Apr 13 '06 #7

P: n/a
Penny wrote:
(Access 2003 Multiuser Split DB, Windows XP Pro)

Hi All,

I would really appreciate just some basic tips on how to make a Crosstab
Form based on a Crosstab Query. The query always has the same number of
records(generated from a table of predefined 'timeslots'). The number of
columns(one for each Consultant) varies depending on how many of the
Consultants have a yes/no field('Participates') set to yes. Therefore the
Crosstab query generates the appropriate 'consultant/timeslot' grid
everytime.

The query needs to be manifested as a form so any one of the cells can be
double clicked into and that cells unique data sent to the OpenArgs of
another form. Obviously the form will need code to produce the correct
number of controls(text boxes) so the column count is correct depending on
how many Consultants are participating.

ANY pointers on how to get going on this would be greatly appreciated.

Regards,

Penny


Penny, I don't have a form that does that, but I have done it frequently
with reports. I have several that do precisely what you describe needing.

I have the controls already created on the report, but hidden. The Form
Open event does a simple query that determines how many columns will
appear. The report then makes the correct number of columns visible,
sets their positions and widths to make the report fill to the margins.

The result, at least for a report, is perfect, even though the number of
columns shown varies. For a form, I suspect there would be some
different requirements. The report has to fit in the margins, so I have
to adjust the width of the controls. Consequently, I'm limited in range
of the number of columns allowable. On a form, you can permit
horizontal scroll bars, so you probably wouldn't need to adjust width.
You would, however, need to make sure that you had enough controls
created (and hidden) to cover your maximum number of columns.

I'd be happy to share code if you think it might be helpful.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Apr 13 '06 #8

P: n/a
Hi Bas and Randy,

Thanks for your advice so far. I'm now wondering if the mechanics of what
I'm trying to do will work in Access. My crosstab query returns a datasheet
looking much like the representation below.

Timeslot Mary Bob Sarah
-------- ---- --- -----
8:00 AM Interview Staff Report

8:30 AM Fire Steve

9:00 AM Call Mr Gates

9:30 AM Drinkies Drinkies Drinkies

The timeslots are 'crosstab rowheading'(grabbed from another query based on
table of timeslots), the consultant names are 'crosstab columnheading' and
the subject is 'crosstab value'. The column headings are proper greyed out
headings but the timeslots running down the left appear as the first cell in
the record.

I guess what I'm really wanting is this same data displayed in a datasheet
form. I want to double click on 'Fire Steve' and be able to access in code
the timeslot value (ie 8:30 AM) AND the consultant name (Bob - but in
reality probably his id for updating a main table later). To have these two
bits of data available simultaneously would seamingly be cross referencing
the form. I mean the crosstab query displays the subject value at the
coinciding point nicely but would/could Access be configured to allow the
accessability of the consultant id and timeslot values.

Could this be viable in Access?

Regards,

Penny

"Randy Harris" <pl****@send.no.spam> wrote in message
news:oe******************@newssvr25.news.prodigy.n et...
Penny wrote:
(Access 2003 Multiuser Split DB, Windows XP Pro)

Hi All,

I would really appreciate just some basic tips on how to make a Crosstab
Form based on a Crosstab Query. The query always has the same number of
records(generated from a table of predefined 'timeslots'). The number of
columns(one for each Consultant) varies depending on how many of the
Consultants have a yes/no field('Participates') set to yes. Therefore the
Crosstab query generates the appropriate 'consultant/timeslot' grid
everytime.

The query needs to be manifested as a form so any one of the cells can be
double clicked into and that cells unique data sent to the OpenArgs of
another form. Obviously the form will need code to produce the correct
number of controls(text boxes) so the column count is correct depending
on how many Consultants are participating.

ANY pointers on how to get going on this would be greatly appreciated.

Regards,

Penny


Penny, I don't have a form that does that, but I have done it frequently
with reports. I have several that do precisely what you describe needing.

I have the controls already created on the report, but hidden. The Form
Open event does a simple query that determines how many columns will
appear. The report then makes the correct number of columns visible, sets
their positions and widths to make the report fill to the margins.

The result, at least for a report, is perfect, even though the number of
columns shown varies. For a form, I suspect there would be some different
requirements. The report has to fit in the margins, so I have to adjust
the width of the controls. Consequently, I'm limited in range of the
number of columns allowable. On a form, you can permit horizontal scroll
bars, so you probably wouldn't need to adjust width. You would, however,
need to make sure that you had enough controls created (and hidden) to
cover your maximum number of columns.

I'd be happy to share code if you think it might be helpful.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Apr 14 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.