Microsoft Office Tutorials and References
In Depth Information
Creating a Dynamic Crosstab Report
CurrentDb.Execute UPDATE tblMove SET Sequence = & dbKeyFrom &
WHERE Sequence= & dbKeyTo & ;¶
CurrentDb.Execute UPDATE tblMove SET Sequence = & dbKeyTo & WHERE
End Sub¶
Testing for selection must be performed, and testing for move possibilities has
been added. The Up on the first row and the Down on the last row are just
ignored. An error message displays only when no row has been selected.
The Me.refresh makes the changes visible when the table is updated. Because
the index of the selected item will be lost, we must first save the index and fill
the Key From and the Key To so that the sub performs the switch of the rows,
which can be called as follows:
intSaveIndex = Me.lstMove.ListIndex¶
dbKeyFrom = Me.lstMove.Column(0, Me.lstMove.ListIndex)¶
dbKeyTo = Me.lstMove.Column(0, Me.lstMove.ListIndex + 1)¶
The switch in the general subMove is done with three UPDATE statements:
1. Neutralize the row to be moved by setting the key to zero (0).
2. Move the row that is on the location needed to the previous location of
the selected row.
3. Update the Neutralized row to get the correct value.
Creating a Dynamic Crosstab Report
This procedure demonstrates a way to display crosstab query data dynamically
in a report.
Example file:
A015.mdb with
and report
Scenario: When using a crosstab query for a report, notice
not only that the field contents are transformed into
fieldnames, but also that adding a new value in the Column
field doesn’t show on the report. Removing a value entirely
causes the report to fail. Here, we make a report that
handles all field values from a query.
Search JabSto ::

Custom Search