ActiveX Grid for Access 2010 Report

Guus2005

AWF VIP
Local time
Today, 15:18
Joined
Jun 26, 2007
Messages
2,642
Hi,

I need a grid control for a report because the alternative would be to add over 400 textboxes and labels to the report.

In the past i have worked with ComponentOne Flexgrid, which worked very well but it is quite expensive.

An embedded Excel object would be a fine candidate but you need to have Excel installed for that to work and it is quit a pain to embed an Excel object in a report.

As you can see in the attached picture the report has a lot of fields. It is a design sheet for an engine. I have to create several each with a different format.

Do you know of a grid i can use?

Thanks!
 

Attachments

  • ReportGridOnly.png
    ReportGridOnly.png
    21.3 KB · Views: 107
If you have to add 400 boxes then the design is wrong.
The report cycles thru records on the detail group band. Filling in the boxes as it goes.
Nobody wants to read 400 boxes per line.
 
@Ranman256: The report shows one record. A single record can have up to 400 fields. It contains all specs of an Engine, its dimensions, settings and current values as i have shown in the attached picture. I know it is not your regular report.

@ridders, thanks for the link to rogers acces library. The FlexGrid demo looked promissing but i could register the msflxgrd.ocx file. Viewing the ocx file with the dependency walker it shows a lot of missing dll's. Apparently this ocx is not able to run under Windows 10.

I am still open for suggestions.

Thanks!
 
I tried it a couple of years ago and thought it was using Win10.
Suggest you contact Peter Hibbs direct - he was very helpful.
He has his own website full of flexgrid advice & examples.

Peter is active on Utter Access but if it helps I can send you his email via a PM.
 
Since Access does not support tables or queries with more than 255 columns, I'm not sure what your table looks like. If it is properly normalized, you can use one or more subreports to display data from the various tables.
 
Pat said it briefly. Guus, I've seen you around for a while and think it very likely that you understand the need for normalization, so I won't go there. I'll address structural and procedural issues instead.

A single record can have up to 400 fields.

No it can't, not if Access is involved. You are looking at limits of no more than 255 fields and no more than (about) 4000 total bytes in the record split among all integers, reals, and text. You can't even exceed those limits with a query because the limits are on recordsets, not just on tables.

With some judicious formatting, you MIGHT be able to have a parent record nearest the top of the page and several child records below it, with each child record being captured in a sub-report. Pick some things that are the most important part of the record to define the parent and then build a one/many relationship with the other parts. Even though it seems like it should be one/one, remember that with relational integrity, one/one tables can get in a deadly embrace if you try to add records to one of those tables first.

That table split would also take away some of the pressure associated with having so many controls on a given report, since there is a limit of (I think) 768 controls per report. If you really had 400 fields, you start over halfway to the limit and that leaves you in a precarious position for growth or "special features" that might need to be added. You also need to watch out for any of those elements having associated labels that you use for captioning since labels are controls too.

As to the grid, though... that's just a matter of being sure that in design mode you have "Snap to Grid" set and then make the controls have solid borders. Then drag-n-drop them to the desired alignment. You can also do a bulk sizing by drawing a box around the controls, showing the Properties panel, and putting some appropriate number in the Height property while your multi-control group is selected. All of them will snap to that size at the same time. If you pick a height that comes out even with respect to twips, you can do this with no apparent overlap or blurring of the text boxes.

This is going to be a "divide and conquer" situation. Divide your report data into groups that then are moved to child tables. Divide your report into various blocks on the page to allow for the parent fields and sub-reports for the child fields.

If it happens that some of the information you want to display is "boilerplate" for which many fields are instantly specified when you know one item (say, the total displacement of the engine in cu.in. or cc), then you might be able to move some of the engine information out to a table of standard info for engines of a particular size. OK, it might not be size that defines the choice of common elements, but perhaps you see what I mean, and that commonality would contribute to reduction of the table size to fewer than 400 fields.
 
Since Access does not support tables or queries with more than 255 columns, I'm not sure what your table looks like. If it is properly normalized, you can use one or more subreports to display data from the various tables.

My tables look like this:
One table for the object, which has 4 fields
One table with all possible properties.
One table with a link to the object, a link to the property and a field for the value.

That's all. It can accomodate any kind of object with 1 or 400 properties.

Perhaps i need to try a different approach.

Everybody thanks for your input!
 
You are clearly normalized. I merely wonder about what it would take to populate that report since you NOW have described a case where you don't have 400 fields in 1 record - you have a few hundred records with one functional field each. This is beginning to sound like an UNBOUND report (or lightly bound) where most of the items would have to be populated from a recordset iteration in the report's OnCurrent event or in the Detail Section's OnFormat or OnPrint event.

However, you still would have be careful regarding the limit on the number of controls including labels.
 
Create layout in Excel/Word/LibreOffice/Whatever.
Insert text for cell references to the cells you want to insert values to.
i.e [1-1] to [1-10] = cells 1 through 10 on row 1.
Save as HTML.
Use this file as a template.
Open the file, replace placeholders with values from recordset, save as new file, load into Web control.
 
No one wants to look at a row containing 400 columns. Look at the report options for snaking columns. That might solve your problem of displaying all items in some way other than 400 across or 400 down.
 

Users who are viewing this thread

Back
Top Bottom