Need Flexgrid Guru

[LarryP]

I'm working on an application where FlexGrid might be helpful, but even with
msflxgrd.ocx installed and registered I still get the licensing issues
message. I've asked our IT people to work through that, but in the meantime:

What I need to do if possible is emulate Excel in a form to some extent to
get around the page width limits. The data I need to display in the form
involves, at a minimum, monthly values going out 36 months, plus a couple
additional columns, so there's no way I can squeeze it all into the available
22" of form width. Will a FlexGrid solve this for me by giving me a
horizontal scroll capability? If FlexGrid isn't the solution, is there
another way to do what I need?

 

[LarryP]

I'm also looking at the simple Spreadsheet 11.0 control, where I don't have
any licensing issues. It has the scrolling capability that would solve my
most urgent need, but if there's a way to populate it from a query or table
rather than having to link to an actual Excel workbook, I haven't figured it
out yet.

 

[Peter Hibbs]

Larry,

Yes, you can certainly have horizontal scrolling in a Flex Grid. Have
a look at my demo Flex Grid program at :-
http://www.rogersaccesslibrary.com/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4
for some examples.

Regarding the license issue you have a few options.

1. You could purchase Visual Studio 6 from Microsoft. This includes
Visual Basic 6, Visual C++ 6, Visual Fox Pro 6, Visual J++ 6, etc. The
Flex Grid control comes with this software and you automatically get
the license to use it included. If you intend to use the Flex Grid
control a lot (and I have used it in dozens of projects) then this may
be your best bet. Perhaps you can find a cheap copy on ebay, (you may
be able to get it by just purchasing VB6 but I'm not sure if the Flex
Grid license comes with that version).

2. If money is no object, there are other Flex Grid controls available
from other software houses. They are more comprehensive than the MS
version but they are also fairly expensive. I have not tried them
myself but have a look at the VS Flex Grid Pro control at :-
http://www.componentsource.com/products/componentone-vsflexgrid-pro/index-gbp.html

3. For a one-off project you can just import one of the forms with a
Flex Grid control from the demo program and then change the properties
and VBA code to suit your project.

4. You can download the free Visual Basic Express edition from
Microsoft at http://www.microsoft.com/express/vb/
which I believe includes the Flex Grid license (although I have not
confirmed that).

HTH

Peter Hibbs.

 

[LarryP]

Thanks, Peter. MS Flexgrid is really nifty, lots of cool stuff I've wished
for. The licensing thing, unfortunately, has to be run thru our IT, which
maintains an iron grip on anything to do with software (probably for good
reasons, but it does tend to make things move slowly!). I'll ask them about
the free download thing, maybe that will be palatable to them. Last resort,
I'll plagiarize from the demo as you suggested.

 

[Albert D. Kallal]

perhaps you consider a continues form, or a datasheet????

here is some screen shots....
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

The above might give you some ideas. The advantage is you don't have any 3rd
party licensing etc. problems....

 

[LarryP]

Yes, simply including all my 40-odd fields in a form and forcing it to
datasheet view would solve the problem of insufficient screen width. I like
all the extra functionality of FlexGrid, but if IT puts that in the too-hard
pile I'll probably go with your suggestion. Thanks.

 

[David W. Fenton]

I'm working on an application where FlexGrid might be helpful

An Access application? That is, using Access to develop the front
end?

What in the world do you think the Flexgrid provides that you can't
get from a standard Access continuous form? Is it worth the hassle
of working around all the incompatibilities to use a non-native
control when the native control should provide everything you'll
ever need in a database application?

 

[LarryP]

Yes, an Access (2003) application. As I originally said, my #1 problem is
about showing roughly 40 fields of information horizontally, each needing
roughly 1" of screen width. Since forms can only be 22" wide, there's the
rub. Albert Kallal suggested datasheet view. That does give me the
"Excel-like" capability to scroll through as many fields horizontally as I
like (within standard limits on query design), and some features like
conditional formatting and event-driven actions are possible. Going that
route, though, I lose the ability to, for example, show totals beneath each
column or incorporate other controls, subforms, etc. My hope is that one or
the other of the grid controls will let me have the best of both worlds.
Unfortunately, I've tinkered with the Spreadsheet 11.0 and FlexGrid, but
haven't yet found a good how-to reference (site, book, whatever) on
maniuplating those controls. I can probably figure it out from Peter Hibbs's
sample database eventually, but if you (or anybody) knows of a FlexGrid for
Dummies book or site, by all means point me to it.

 

[David W. Fenton]

my #1 problem is
about showing roughly 40 fields of information horizontally, each
needing roughly 1" of screen width.

Sounds like you want to torture your users.

I think the problem is with your original goal. There's a good
reason Access limits you to 22" -- in fact, I'd say 22" is actually
an artifact of the commonalities between forms and reports (which
could very logically be printed on paper approximately that wide),
and for forms, I'd say the upper limit is too large by a factor of
at least 2.

You're contemplating implementing a horrid, difficult-to-use
interface for your users. Abandon the original problematic design,
and you'll likely have a much easier time of it.

 

[Keith Wilby]

my #1 problem is
about showing roughly 40 fields of information horizontally, each needing
roughly 1" of screen width.

Schema design issues aside, have you considered arranging this data into
logical groups and putting each group on its own page on a tab control on
the sub-form?

Keith.
www.keithwilby.com

 

[LarryP]

To you and David, the monster under the bed with this thing is that I've been
tasked to turn an Excel spreadsheet into an Access tool that, among other
things, gives users a variety of drilldowns at a buttonclick. But they want
it to look as much as possible like the Excel spreadsheet they're used to,
and note the word "tasked" -- they tell, I attempt to do even if that's not
the way I would have designed it. As to logically subdividing the data into
tabs or pages, 36 of the ~40 fields are simply month-by-month dollar figures
for production demand and assets (materials) to satisfy that demand. So
breaking it into logical groups doesn't work very well conceptually, and
doesn't support the old familiar "just scroll to the right" thing they
"enjoyed" in Excel. Sigh ... guess I'll keep thinking, maybe a lightbulb
will go on.

 

[Nicholas Scarpinato]

Larry, I feel your pain. I've had to do much the same thing myself over the
past few days using Hierarchial Flexgrid, and when I've posted here asking
for help I've had quite a few replies telling me that my "database design is
flawed" because I'm displaying "too much data", or my "interface is horribly
designed" and that my users will hate me for it. But as it turns out, my
users like the Flexgrid interface more than the original interface I designed
that was somewhat more, shall we say, "Access-expert accepted". Sometimes you
have to just do whatever your too-scared-to-learn-new-things users will have
the shortest time adjusting to. A few people here understand that better than
others. Unfortunately, that usually means we as developers have to work
harder to give the users what they want.

If you're attempting to build what amounts to an Access PivotTable, I can
send you a copy of what I've done and let you modify it however you need... I
just finished building one myself.

 

[LarryP]

Thanks, Nicholas, for the encouraging words. I am usually given a fair bit
of latitude to design from scratch, but now and then I'm handed one with
marching orders to "make it look like what they're already using." In this
case, since I haven't found any good single reference for how to use
FlexGrids, I'm setting up a form that essentially rotates their beloved Excel
display 90 degrees, so the months run top to bottom and the other stuff runs
horizontally, but otherwise it has much the same look. I'm hoping I can get
them to accept at least that much change.

I would, however, be interested in seeing your latest product just to
further my FlexGrid education, and if you know of a good FlexGrid Bible out
there somewhere, please point me to it. ([email protected]).

 

[Nicholas Scarpinato]

Larry, I'll be sending you an email shortly. What version of Access are you
running, I need to know so I can convert this if necessary as I'm on Access
2007.

 

[LarryP]

I'm developing in 2003, but have both available.

Larry, I'll be sending you an email shortly. What version of Access are you
running, I need to know so I can convert this if necessary as I'm on Access
2007.

 

[Nicholas Scarpinato]

Also, as far as the FlexGrid licensing is concerned... I installed Visual
Basic 2005 Express Edition as Peter mentioned previously, and yes, for now
the licensing gods seem to be appeased for both the Flexgrid and the
Hierarcihial Flexgrid. Since VB2005 Express Edition is free, maybe your IT
department won't be so averse to letting you use it.

 

[Peter Hibbs]

Nicholas and Larry,

FYI, You can now download the free Visual Basic 2008 Express Edition
from Microsoft at http://www.microsoft.com/express/vb/ which also
gives you the free license. Not sure what the difference is but I
assume that the 2008 version has some extra facilities. You can also
get Visual Web Developer 2008, Visual C# 2008, Visual C++ 2008 and SQL
Server 2008 Express, all free. It is a pretty big download though.

You can find more information on the Flex Grid on the Microsoft site,
you have to do a bit of digging to unearth it though.

In case you are interested I have just placed another Flex Grid demo
on the Web site which displays Crosstab query data on a grid. See
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=429
for the demo.

Peter Hibbs.

 

[Nicholas Scarpinato]

Peter,

I installed VB2008 EE on another machine here, and it worked well, except
that it took about an hour to install it because it wanted to install .NET
3.51 as well. Considering that my users are NEVER going to actually *use* the
program, going with the 2005 version is fine, at least for me. But I will be
sure to take a look at your Crosstab example to see if there's anything I
might have missed that would be beneficial to my users.


By the way, you don't happen to know how to make the mouse wheel work in a
flexgrid, do you? I've tried doing some research on it and all I can come up
with are solutions for VB applications, not for Access.

 

[Peter Hibbs]

Nicholas

Yes, you are right about the installation time, it took ages for me as
well.

I just tried my demo again and the scroll wheel works fine on the flex
grids. My mouse is a Microsoft optical type but whether that is
relevant or not, I don't know. Perhaps there is some setting in
Windows you can tweak, other than that I have no other ideas.

Peter Hibbs.

 

[David W. Fenton]

To you and David, the monster under the bed with this thing is
that I've been tasked to turn an Excel spreadsheet into an Access
tool that, among other things, gives users a variety of
drilldowns at a buttonclick. But they want it to look as much as
possible like the Excel spreadsheet they're used to

Just say NO.

If it's so easy, tell *them* to do it.

When they admit they can't, then perhaps you can convince them that
their conception of how it should be designed is perhaps not very
clearly thought out.

But say NO.

Over and over and over again.

I've had this happen with clients where I did it for them the right
way and they say "but that's not the way it should be!" In every
case, eventually they realize I was right all along.