Updateable CrossTab Form. (1 Viewer)

Status
Not open for further replies.

ChrisO

Registered User.
Local time
Tomorrow, 01:46
Joined
Apr 30, 2003
Messages
3,202
Updateable CrossTab Form.

Sorry, but this might get a bit verbose.

Forward:
CrossTab queries are good at displaying data in a spreadsheet manner, but that leads to three distinct problems.
1. A large number of columns can exceed the screen width in both Forms and Reports.
2. When generated automatically, column header names and control source assignment is fixed.
3. A CrossTab query is not updatable.

To overcome problem 1, the number of columns need to be restricted to what will fit into the screen width. Because of problem 2, the restriction of number of columns means we need to adjust column headers and control sources on the fly.

Complacence with problem 1 and 2 implies more complication.
A Form width can be wider than the screen display area and that requires a horizontal scroll bar to view all columns. In spreadsheet view, the primary axis may very well be the vertical axis on the left and not the horizontal axis at the top. Hence, scrolling horizontally should not affect the vertical primary axis position but only the horizontal axis.

A Form height can be higher than the screen height. That, too, requires a scroll bar. A vertical scroll bar should scroll the Form vertically without changing the position of the field with focus. As the Form is scrolled vertically the fixed primary axis should also scroll.

To achieve that we have two synchronised subforms. The subform to the left is the primary axis. It holds its position on a horizontal scroll of the subform to its right. If the subform to its right scrolls vertically then the primary axis to the left also scrolls. The subform to the right holds column position on its control with focus. The subform on the right can be scrolled with the scroll bar or with its navigation buttons.

Note here it’s the other way around to the way we might think. The primary axis on the left becomes the slave to the secondary axis on the right which is the control. However, the primary axis on the left only responds to vertical movement as dictated from the right but not the right’s horizontal position.

To overcome problem 3 requires some VBA, but perhaps not too much.
Double clicking at an intersect of axes invokes a single change Form.
Double clicking on a column name invokes a range change Form.
Each invoked Form behaves differently

A screen dump of the Main Form:-



List of Header Names
Because a CrossTab Form is restricted by what can be horizontally displayed on the screen, the number of Header names is also restricted. The number of Header names in the list can exceed the maximum display number but only up to the maximum can be selected, in this case the maximum is 19. If less than the maximum is selected then the column widths remain the same but simply get removed. If the number of selected Header names drops below 11 then the column widths expand to fill the available space. The Form also allows the entry of new Header names and those new entries default to not selected for display.

This selection also affects the two Reports. Each Report automatically expands its column widths automatically to fill the available space. However, if the number of selected Header names drops below 11 the Report in portrait view is used else the Report in landscape view is used. Again here, screen real estate is critical and the two Reports are designed to use A3 paper. If you could go for printing in A0 format, in colour, then it become a whole new ball game.

This Form also drives the CrossTab query by virtue of the queries IN clause. It both limits and sorts the column Headers. It is a string returned by Public Function PersonList(). Therefore, both this Form and that public function are at the heart of the matter.

The query is done in VBA because that is the way I prefer things. If you wish you can dump the SQL to a query for viewing.

A screen dump of the Header names selection Form:-



Conditional Formatting:
As can be seen by the below screen dump conditional formatting can be applied to the Job names. In this case, however, the end user can change the formatting to suit themselves. There are four conditions, default, condition 1, condition 2 and condition 3.

Each of the four conditions can be user configured for Bold, Italic, Underline, Back colour and Fore Colour. Each of the Job names can be assigned a condition number by the user. The user may also enter a new Job name.

A screen dump of the Conditional Formatting Form:-



Public Constants:
In this project, public constants are handled differently. (I am aware that, strictly speaking, they are not constants; they can be written to.)
What it represents is a self initialising class structure with Intellisense.
As such it attempts to organise public constants in some logical manner.
Whether or not it is a good idea remains to be seen; sometimes these things simply need to be done to see if the spanner fits the nut.


Tested with:-
Version..............: Access 2003 (Access 2003 file format).
Regional settings.: English, French, German and Portuguese.
References.........: None.
Error handling.....: None.

A demo is attached.

If you have any questions, please ask them in the appropriate forum.

Chris.
 

Attachments

  • ConditionalFormatting.PNG
    ConditionalFormatting.PNG
    22.1 KB · Views: 5,027
  • Main.jpg
    Main.jpg
    101.3 KB · Views: 5,148
  • NamesInList.PNG
    NamesInList.PNG
    10.4 KB · Views: 4,518
  • UpdateableCrosstabForm_A2003_V1.zip
    199.3 KB · Views: 2,083
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom