Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Crosstabs, forms and updating

From Wiki

Jump to: navigation, search

I have several times seen questions about displaying row data as columns in a form, generally because the user wishes to present a week's worth of data. You can write the data into a grid and then update any edits back to the table, but it is fiddly and annoying, especially when a crosstab may get you the data just the way you want it.

It is quite possible to display a crosstab in a form, but clearly, you cannot edit the data, however, here is a simple workaround. You will need:

A table

table1.png

A crosstab query based on the table

query1.png

A form

form1.png

Containing

A subform for the crosstab query

thextab.png

As you can see, the Source Object for the subform control is set to Query followed by the name of the crosstab query:

   Query.Query1

That is all you have to do, the query will be displayed, and you can now refer to it as a form.

A textbox

The textbox is illustrated in the above image and the control source is set to:

   =[Forms].[Form1].[TheXTab].[Controls](0)

This refers to the first column of the crosstab control and you will see the contents change as you move from row to row,

A subform for the data

thesubform.png

In order to edit the data, we need to extract the whole group, "b" in this case, and this can be done by using the textbox as the link master field and the field (column) name for the group as the link child field, as illustrated in the image above.

Code

If you want to update the crosstab as you go, you will need a little code.

  1. Private Sub Form_AfterUpdate()
  2. Forms.Form1.TheXTab.SourceObject = "Query.Query1"
  3. End Sub

The code should be attached to the form contained by TheSubform control, in this example it is called Table1_Form, as can be seen in the image above. It does seem like overkill to reassign the source object, but this is the only way I could find to efficiently update the crosstab.

When data in TheSubform is updated, the crosstab is updated, and when a line in the crosstab is selected, the data for that line is displayed.

I am not sure how much use this is, but it is quite good fun.

833 Rating: 3.1/5 (54 votes cast)