Continuous form with horizontal and vertical records

[Evilferret]

I am building a continuous subform that needs to display records from a single table both horizontally and vertically.



To illustrate my problem, my subform currently displays as follows:

Record 1
Record 2
Record 3
Record 4
Record 5
...

I would like it to display as follows:

Record 1 Record 2 Record 3
Record 3 Record 5 Record 6
...

I know that I can use print preview to set the number of columns, however I am trying to modify the display of the form on the screen, rather than the display of the form on paper.

Any assistance would be greatly appreciated.

[Rod]

Not possible with standard Access features. Could be done with a lot - stress lot - of fiddly work that simulates (approximates) your requirement. Easier if the continuous form is read only.

[Dal Jeanis]

Not possible with standard Access features. Could be done with a lot - stress lot - of fiddly work that simulates (approximates) your requirement. Easier if the continuous form is read only.

Old question, but it seemed like an interesting little test of ingenuity. It can be done in two quick steps.

Step 1 - Assuming you have 600 records, Use VBA to build a temporary table that consists of 200 records each with three foreign keys. Your choice whether to run them left-right or up-down. Let's call it tblThreeTempKey, and the three keys are FK1, FK2 and FK3. Leave extras NULL on the last record if needed.

Step 2 - Let's assume your main table is tblStuff and its primary key is PK. Here's your query join (more or less)

SELECT S1.*, S2.*, S3.*
FROM (((tblThreeTempKey AS T3
LEFT JOIN tblStuff AS S1 ON T3.FK1 = S1.PK)
LEFT JOIN tblStuff AS S2 ON T3.FK2 = S2.PK)
LEFT JOIN tblStuff AS S3 ON T3.FK3 = S3.PK)

Now each single record returned by the query combines up to three different records from tblStuff, and you can make your 3-wide form based on the query.

Not sure whether the query result will be updateable, though. It joins the same table three times, which tends to prevent updating, but each record in tblstuff will only appear once, so it might work.

[rzw0wr]

Would a Pivot table work?

Dale

[Missinglinq]

Step 1 - Assuming you have 600 records, Use VBA to build a temporary table that consists of 200 records each with three foreign keys. Your choice whether to run them left-right or up-down. Let's call it tblThreeTempKey, and the three keys are FK1, FK2 and FK3. Leave extras NULL on the last record if needed.

Step 2 - Let's assume your main table is tblStuff and its primary key is PK. Here's your query join (more or less)

SELECT S1.*, S2.*, S3.*
FROM (((tblThreeTempKey AS T3
LEFT JOIN tblStuff AS S1 ON T3.FK1 = S1.PK)
LEFT JOIN tblStuff AS S2 ON T3.FK2 = S2.PK)
LEFT JOIN tblStuff AS S3 ON T3.FK3 = S3.PK)

Now each single record returned by the query combines up to three different records from tblStuff, and you can make your 3-wide form based on the query.

Not sure whether the query result will be updateable, though. It joins the same table three times, which tends to prevent updating, but each record in tblstuff will only appear once, so it might work.

As Rod said, "with a lot - stress lot - of fiddly work!"

Linq ;0)>

[Dal Jeanis]

Dale - that's not what I think a pivot table does, but I could be wrong.

Missinglinq - ...but, fiddly work is the fun part of this job! And I came up with two other methods last night that are a LOT more fiddly.


By the way, if you add two blankish dummy records onto the end of the source file, then you can change the SQL to INNER JOINs.

[rzw0wr]

Thanks Dal.

I have never used a pivot table. Just a guess.

Dale

[Missinglinq]

...fiddly work is the fun part of this job...

True, which is why I still play with this stuff years after everyone stopped paying me to do so!

Have a great weekend!

Linq ;0)>

[Rod]

True, which is why I still play with this stuff years after everyone stopped paying me to do so!

Linq ;0)>

Likewise!

The solution proposed by Dal Jeanis implies that you know the number of columns required which is not particularly elegant. Now think about a more general-purpose solution where you can change the number of columns on the fly.

[Dal Jeanis]

ooo - a challenge.

The main problem I see with a generalized solution is the display. It's trivial to have the VBA build the key table with an arbitrary number of horizontal keys - my mental code already assumed that strategy based on Recordsequencenumber Mod N with N=3, and the blank records at the bottom of the main file need to be N - 1 blank records.

However, trying to link a fixed form to that arbitrary output isn't pretty. And I don't plan to rebuild a form on the fly, although it's technically possible if you don't like to sleep at night.

Hmmm ... you also have to account for determining and dividing the available visual real estate ...

I'd have to have some parameters for the problem I'm trying to "generally" solve, before I could take a swack at it. In other words, am I developing reusable code in anticipation of encountering similar problems in the future - in which case I'm basically there.

Hmmm.

Might be possible to develop a class object that provided multiple horizontal copies of a continuous form.

Might also be possible to load one control (for example) with the types of the columns for each record, then a second control with an arbitrary number of columns of data. The form could then unpack those columns horizontally, enabling an arbitrary number of fields and buttons into each "cell". Modifying the data in a "cell" would cause the execution of an update against the file and a requery.

Not something I'd do much work on without getting paid for it, though, and it's hard to imagine an application where this generalized solution is more proper than a well-defined interface based on an actual business use-case.

This method is more like the Anderson Consulting method of mainframe consulting from the 1980s - write the client's CICS interface using a proprietary tool that is no better than COBOL, but that forces the client to continue using your services, because no one else can understand it. (Evil laugh.)

[ssanfu]

@Dal

WOW, you are twisted...... and I mean that in the nicest, best way.

Reading your last post makes my head hurt!

[Rod]

@Dal

Provided that the number of cells is not huge causing much virtual memory swapping then Access' OOP is probably the way to go. Maybe - and this is off the cuff - a cell class and a line class plus a collection of lines. The line class may well itself be a collection. What we're suggesting is a cut down (very cut down) version of word processing. Now how to get the OOP data onto the screen? Windows APIs may be the best answer; otherwise your thoughts re controls may be worth pursuing.

On the other hand there's always Excel.

@ssanfu
Take two asprin and lie down until you feel strong again. Alternatively leave for the pub right now.

[Dal Jeanis]

@Rod - Hmmm - and each cell could have an independent query for one recorrd, if you really want to give Jet hiccups!

@Steve - the original Latin translation was slightly garbled, It was "I came; I saw; She conquered."