MS Access Form display 2 field table in multiple columns

Clay Willing
Clay Willing used Ask the Experts™
on
Hi Experts All,

I have rather strange request for help regarding a form layout or formatting option.  I have a table with a max of 30 items (I would hope) that has a few related fields.  The form I would like to display this on I want to be wide rather than tall so I want to split the table into 9 columns rather than 3 tall ones.

I have a solution where i count the number of entries in the table and set the control source of a subform to "SELECT * FROM tblCritical WHERE [critID]<= 8 " then a second subform with "SELECT * FROM tblCritical WHERE [critID] >8 and [critID]<=16 " etc but is there a better way of doing so? However, this early on in the development of the form I havent taken into account what happens if the SQL causes and error or anything like that.

Any assistance greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
You might want to consider using a report instead, as reports can be presented in multiple columns.  Of course, this would only be practical if you don't need to edit the data, just display it.
Clay WillingEng Ops Mgr

Author

Commented:
No doubt a report would be able to display the data too but I still dont know how I would produce multiple columns other than using multiple subreports.  

However, this form is a status page that my users see when they open the application so i need the ability for them to login to the rest of the app or close down the database.  Therefore a reoprt just isnt the right method of delivery.  

Also, I want the user to be able to change the state of the items in the list dependent on the 'real time' situation so interaction is necessary.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<I still dont know how I would produce multiple columns other than using multiple subreports.  >
Muttiple subreports would be the only way that I know of to give you the output you are requesting here.

Besides, even if you used subreports(or sub forms), ...you would have to somehow Synchronize them to all be on the same record

Can you state the need for this interface?
Unless I am not understanding something, ...I have never seen or heard of an interface like what you are asking for here...

(Perhaps you could post a graphic of what you are envisioning...)

Perhaps there is another approach...
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Clay WillingEng Ops Mgr

Author

Commented:
Okay.  So I have attached a picture of the form and you can see that the 2 subforms allow me to display the table across the page rather than down it.

The equipments will be R, A, G dependent on whther they have a jobcard against the whole system or just a part or none at all. So Fully Serviceable, at a reduced capability or Fully 'S'.

Conditional formatting based on a (not shown) status field which is 0,1 or 2 takes care of the coloring.  The page refreshes on a regular basis so that each time the jobcads associated with the equipment close, the status will be updated.  I just think it looks better across the form rather than having all 30 in a direct line downwards.  The difficulty being that these equipments could expand over time and I would have to rengineer the form adding more subforms as I require.  That may mean creating identical tables on the fly which I believe is possible just quite difficult and possibly beyond me.

Alternatively I could programmatically build a webpage in html.......and show that?

Just trying to find a more elegant solution....

Does that give a better understanding?
NewStatus.png
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<So I have attached a picture of the form and you can see that the 2 subforms allow me to display the table across the page rather than down it.>
So why not just use that?

<The difficulty being that these equipments could expand over time >
Then what happen if there are 50, 100 or 100 records to display?
Will you continue to make subforms?

<Just trying to find a more elegant solution....>
A single scrollable form has always been used to display this type of info.

So,  If there is some web technology that does what you are asking, ...then roll with that.
Clay WillingEng Ops Mgr

Author

Commented:
I don't expect the equipments to expand past 50 ever but this is suppoed to be an easily read dashboard real time (ish) display.

Any idea how i can utilise a web control to accept html? I already do something similar for the body property of outlook messages so I can construct colored html based on recordsets by just building up line by line html into a string.

Appreciate any pointers in that regard
MIS Liason
Most Valuable Expert 2012
Commented:
You can use a "web Browser" control to display HTML data.

Still not sure this is worth all the trouble though, ...with a max of 50 records, a scrollable form would work just fine...IMHO.

Other techniques to do what you are asking would be to create a "Numbering query" from this table and filter the individual subform for 1-7, 7-14, ...etc

Jeff
Clay WillingEng Ops Mgr

Author

Commented:
Thanks Jeff,

I will have to look up numbering query to drag the data out correctly.

The end game for all this is intended to be a dashboard of status that is displayed at all times on a video wall so a 2 column list that scrolled off the bottom of the page just wouldn't cut it.

However, thanks for the sounding board.  I have done some investigation into my web browser idea and I am reasonably content I can generate the dashboard I am thinking of by running a few queries and updating the status field to display the relevant coloring.  Just deciding what it should look like now.  Also the web browser control was frustrating at first but I think I have it nailed now.  The info on this control around the web is rubbish.:-).

The interaction bit is a bit of a quandary as my original premise was to check for open and closed jobcards to show RAG but I will use the double click event to open the status table and allow my users to add quick comments.

Thanks again I will accept previous comment as solution.

Clay
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
OK

Great.

Enjoy the weekend

;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial