473,888 Members | 1,424 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Crosstab forms: just the basics

(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(generat ed 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('Particip ates') 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
8 6081

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***@spampoli ce.com> schreef in bericht
news:11******** *******@angel.a mnet.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(generat ed 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('Particip ates') 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
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(whi ch 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***@spampoli ce.com> schreef in bericht
news:11******** *******@angel.a mnet.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(generat ed 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('Particip ates') 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

"Penny" <pe***@spampoli ce.com> schreef in bericht
news:11******** *******@angel.a mnet.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 ", "GetRowCoun t", 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.cou nt),
- probed for then number of rows (rst.recordcoun t)
- read for the data (rst.absolutepo sition = 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 acLBGetColumnCo unt ' Aantal kolommen ophalen.
LijstMaandagen = 1
Case acLBGetColumnWi dth ' Kolombreedte ophalen.
LijstMaandagen = -1 ' Standaardbreedt e gebruiken.
Case acLBGetValue ' Gegevens ophalen.
intOffset = Abs((9 - Weekday(Now))Mo d 7)
LijstMaandagen = Format(Now() + _
intOffset + 7 * rij,"d mmmm")
End Select
End Function


Apr 12 '06 #4
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******@pcdata sheet.com
"Penny" <pe***@spampoli ce.com> wrote in message
news:11******** *******@angel.a mnet.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(generat ed 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('Particip ates') 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
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.co m> wrote in message
news:pE******** *********@newsr ead3.news.atl.e arthlink.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

"PC D" <fa***@email.co m> schreef in bericht news:pE******** *********@newsr ead3.news.atl.e arthlink.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
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(generat ed 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('Particip ates') 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
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'(gra bbed 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******** **********@news svr25.news.prod igy.net...
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(generat ed 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('Particip ates') 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3218
by: Darleen | last post by:
I am seeking conceptual here on how to get started with a "3D Matrix" in Access. We run a training center which holds multiple classes in multiple cities at multiple times. So I need to create a matrix with 3 "axis". The 3 axes are: City Location Week of Class (in 14 week increments that repeat) Name of Class The end goal is to be able to have a form that shows these classes in
1
17692
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
1
3345
by: Richard Hollenbeck | last post by:
Hello Newsgroup. You have all been very helpful in the past and I thank you. I try to ask relevant questions so that they don't just benefit me, but also benefit the group. I'm currently overwhelmed by useless examples across the web on how to make "dynamic crosstab reports" without myself having a basic understanding about how to retrieve and assign recordsources, etc., from fields in a query to fields in the report. I see all these...
3
3601
by: deejayquai | last post by:
Hi I've created a crosstab query and displayed it as a sub-report in my main report. This is fine until the data changes and the column names become incorrect. I know I have to create a 'dynamic crosstab query' but I don't know how to!! I've read the "How to..." on the Microsoft site but it mainly gives an example rather than explain the basics, which I can't work out. My context is:
1
4104
by: mtech1 | last post by:
Access 2002 I am trying to create a dynamic crosstab report that parameters come from 3 different forms. I get runtime error 3070 - The Microsoft Jet database engine does not recognize 'Forms!frmDefaults!ProviderID' as a valid field name or expression, and debug takes me to line 60 below. Any Suggestions Would Be Truly Appreciated!
2
2950
by: deejayquai | last post by:
Hi I'm trying to produce a report based on a dynamic crosstab. Ultimately i'd like the report to actually become a sub report within a student end of year record of achievement. The dynamic sub-report will capture what grades the student has achieved in a list of different subjects and the reason I need it to be dynamic is that students take different subjects. Basically I've been trying to doctor the KB article on dynamic
4
2465
by: deejayquai | last post by:
I've worked through the MS KB example and it doesn't really help me as I missing the basics of what the code is doing. I've posted a couple of times here in thsi group but with no success. Could anyone recommend a book or a website that goes through the basics of how to build dynamic crosstab queries in order to create reports that allows column fields to dynamicaly change according to the source data? Regards David, UK
6
4490
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor have I been able to adapt other people's solutions/tips to fit what I need. If anyone could please help me with the following it would be really appreciated, thank you! I need to generate a Report (say: repCrossTab) that grabs it's data from the...
2
3407
by: Jim Devenish | last post by:
I wish to create a crosstab query as the record source for a report. It needs to count data between selected dates which are entered by the user in a popup window. The following Select query works: SELECT Tasks.EnquirySourceID, Tasks.BusinessUnitID, Count(Tasks.TaskID) AS CountOfTaskID FROM Tasks WHERE (((Tasks.TaskDate)>=!!)) GROUP BY Tasks.EnquirySourceID, Tasks.BusinessUnitID;
0
11181
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10778
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10439
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9597
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7990
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5819
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6014
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4642
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4245
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.