Data entry in a crosstab-like form?

[Alisa]

I know I am not the only person who has struggled with this, but I haven't been able to find any solutions . . . yet (I'm still optimistic).

I will try to explain what I am doing:
I have a Categories table:
CatID
CatName

and I have an Subcategory table:
SubCatID
CatID
SubCatName

There are multiple subcategories in each category

I also have an Axes table:
AxisID
Cat1ID
Cat2ID

Each axis consists of a pair of categories

Finally, I have a Rules table:
RuleID
AxisID
SubCat1
SubCat2
Rule

one rule is defined between every possible combination of subcategories for each axis - the subcategories listed in the SubCat1 column are from the category that is listed under Cat1ID in the Axes table, and the subcategories listed in the SubCat2 column are from the category that is listed under Cat2ID in the Axes table, if that makes any sense at all.

After the pair of categories (the axis) is created, I automatically populate the rules table with all of the possible subcategory combinations, no problem there. So my Rules table, before it is filled in, looks like this:

RulesID AxisID SubCat1 SubCat2 Rule
1........... 1........... A.......... a
2........... 1........... B........... a
3........... 1........... C.......... a
4........... 1........... A.......... b
5........... 1........... B........... b
6........... 1........... C.......... b
. . . and so on

Then the rules have to be defined by hand. They want a form that looks like this to use for the rules entry:
---------------SubCat2:
SubCat1: a ------- b------- c
A.......... Yes....... No......... No
B........... Yes....... Yes....... No
C.......... No......... Yes....... Yes


(the user will type in "Yes" or "No" in each space in the grid)

Obviously, if I base the form on a crosstab query of the rules table, then the user will not be able to enter any data.

So far the only suggestion I have seen is to dynamically create an unbound form that mimics the look of a crosstab , but with a grid size of up to 300 by 300, I am not sure that is really feasible. That would mean a total of 600 labels that would have to be defined at runtime, and 90,000 unbound text boxes, yikes! Is there any other way to go about this? I can change my data structure if it would help . . .

 

[Banana]

Alisa,

I made a sample doing just that for a "class attendance report" database. I posted it here on forums... (Aha, over there)

I must caution, however, this is not for faint-of-heart as this requires lot of VBA and elbow grease to get it to work.

 

[Alisa]

Thanks for the tip - that is an awesome form.

 

[Alisa]

I thought I would post back in case this info can help anyone else. I couldn't use an unbound form because there was just way too much data to deal with. After running into many dead ends, this is the solution that worked:
1. Create two tables that list the distinct values for the subcategories in each of the two categories.
2. Add an autonumber column to the second table
3.Create a crosstab table showing my data the way I want to show it on the form: use the subcategory names from the first category for the row headers, but use the autonumber column from the second category for the column headers.
4. Create a datasheet form bound to the crosstab table (I set the first column to locked so the user doesn't mess up the row headers).
5. When I want to use my form, I delete and recreate the two distinct tables and the crosstab table, the form stays bound regardless because it is bound based on the autonumbers, which are always 1, 2, 3, etc. So it doesn't matter that the subcategory names are different each time.
6. In the on load event of the form, I change the caption of each label to match the subcategory names, so those show up on the form as headers instead of 1, 2, 3 etc., and then I hide the columns that aren't getting used so the user won't see #Error for the columns with no data (because this form can have anywhere from 1 to 250 columns).
7. In the close event of the form, I create an "uncrosstab" table of the data using a recordset, and then update the actual data table based on the uncrosstab table.

The only things I don't like about my final solution are that the field (column) headers look the way headers should look - grey - but the row headers are in the actual datasheet on a white background. Also, I am limited to 250 subcategory column headings because of the access table limitations. So I have to actually have two of these forms as subforms (and toggle visibility) to handle all of the data because the users require up to 300 columns.

Also, here are the deadends I ran into, in case it can save somebody else some time and headaches - If I knew about these before, I could have reached the final solution a lot faster:
You can't set the vertical property of a textbox or label to yes - the text dissappears. This bug has been documented for years and Microsoft has STILL not fixed it.
A datasheet form becomes uneditable when used as a subform on a main form that has active x controls on it, such as active x controls that do allow vertical lables.
There is no way to synchronize the scrollbars of two subforms so that you can use one scrollbar to control both subforms (yes, I know you can do it with command buttons and miles of code, but not with the built in scroll bars).
An access form cannot be more than 22 inches wide.
The OWC-Spreadsheet Control would have been a perfect solution, but I couldn't find any documentation on it. Apparently, once you bind the control to a data source, the export function no longer works and getting data out of each cell individually takes way too long.
btw, I am using Access 2003/Windows XP

 

[Banana]

Alisa,

To make it show up as grey as if it was a header vertically, I would do something like this:

Create the datasheet but do not display the row headings. Then after that I'd try one of those:

1) Create another datasheet bound to same data source, but display only row heading and is set to be uneditable. Arrange it so that it overlap the first datasheet a bit to look as if it's one datasheet with both column and row headings.

2) Use CreateControl method to create temporary labels to correspond with the row. This is less than elegant solution, however. A corollary would to be create a bunch of labels and show them only for actual rows.

3) A slightly different way to do this, create three datasheets. The left and right datasheets would be uneditable, but the user could select a heading or scroll across to the header which would move over to the center datasheet and can be editable.

That said, Alisa, you shouldn't forget that datasheets has several more properties that aren't shown and are sort of documented. Look at the thread I linked earlier which should link to that article detailing the properties of datasheet that you could use to edit.

 

[Alisa]

I started out trying to do something like your suggestion and put the labels on separate subforms, but ultimately gave up because I couldn't keep the subforms synchronized while scrolling . . .

 

[Banana]

In that case, don't use the built-in scrollbar (turn it off), but rather create your own (perhaps using similar buttons as record navigation but vertically or something like that) then write VBA to move both datasheet jointly.

 

[Alisa]

Yeah, I tried that too, but the custom scroll bar solutions I was able to find either relied on command buttons (in which case you can't drag the thumb) or they relied on record numbers which means you can only use them to scroll vertically, not horizontally.

 

[Banana]

Did you look around for a custom scroll bar? I thought I saw Leban or Kaplan write a custom scrollbar that can be used and comes with the thumb?

 

[Alisa]

I found Lebans scrollbars, but the one that can be used for horizontal scrolling doesn't have a thumb, I couldn't figure out how to make the one with a thumb scroll horizontally.

 

[Banana]

Eh. Too bad.

I assume that you've already tried enabling the textbox containing the "row headings" Enabled and/or Locked property, too?

(Aha, thought of another idea. You can indeed lock the textbox. And surely if they've got datasheet to alternate colors between rows, it could be done vertically... Maybe need to google)

 

[Alisa]

I did try doing conditional formatting on the column with the row headers, but it looked really funny because a little stripe of the background color shows in between the rows, and the color changes as you scroll cause it takes a couple seconds to load, so I gave up on that too. I couldn't find any other method to control the color, because on a datasheet, if you change the back color, it affects the whole datasheet.

 

[Banana]

What did you specify the conditional formatting to be?

 

[Alisa]

field value <> null

 

[Banana]

Sorry, I wasn't clear- I meant what properties did you set when conditional formmating was applied (did you just change the backcolor property and no other property?)

I asked because I wondered if there were other properties (perhaps Special Effect for example, but I am not 100% sure on this) that could be modified as well to fix that gap?

 

[Alisa]

yes - I only changed the backcolor
As far as I can tell, none of the other settings affect fields in datasheet view . . .