Avatar of Clay Willing
Clay Willing
Flag for United Arab Emirates asked on

MS Access Form display 2 field table in multiple columns

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.
Microsoft AccessMicrosoft Applications

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
Helen Feddema

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 Willing

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 Coachman

<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...
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Clay Willing

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?
Jeffrey Coachman

<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 Willing

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Clay Willing

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.

Jeffrey Coachman



Enjoy the weekend