crosstab in flexgrid or other way

[Mark Andrews]

I'm looking for an inexpensive grid (a few hundred bucks would be ok)
control to take crosstab data into and let the user make edits and then save
it back to the database. The crosstab query will only have one value per
cell. I won't be able to convince the users to abandon the current Excel
solution without going down this route. A typical grid will have 20 rows by
5 to 40 columns.

I found this example:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=429

which is close but don't want the popup form on every cell, just want the
user to edit the cell.

I also have the other flexgrid demo:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=180

which has one example where the user just types into the grid directly.

Has anyone done a crosstab to a grid and the user can edit directly in the
grid?

I would use standard Access forms, controls if there was a better way to go
that route.

I would like to have two left columns be locked and totals on the far right
be locked and have column headers be locked. Editable numbers in all other
cells. Access 2003.

Thanks in advance,
Mark

 

[Tom van Stiphout]

On Tue, 27 Jan 2009 19:39:28 -0500, "Mark Andrews"

All crosstab queries are totals queries and as such non-updatable.
So if you want to update the values, you need to first write them to a
table, then allow the user to make edits.
No 3rd party grid control is needed for this, just a subform, perhaps
in datasheet view.

-Tom.
Microsoft Access MVP

 

[Peter Hibbs]

Mark,

Can you not combine the two options, that is use the Crosstab flex
grid example and use the cell editing example from the other demo. You
would need to be able to identify which record is being displayed in
the flex grid from the row and column numbers so that when you leave
the cell (actually the floating subform) you can update the cell on
the flex grid and also write the new data back into the appropriate
field in the record.

Regarding the totals on the right of the flex grid - that is a bit
trickier because you cannot have fixed columns on the right of the
grid (as you have obviously discovered). The only method I can think
of is to add another single column flex grid to the right of the main
grid and display the totals in that.

Peter Hibbs.

 

[Mark Andrews]

Won't that approach cause the database to grow constantly? I think that is
why I was leaning towards more of a data gets manipulated in memory using a
grid control of some sort.

 

[Mark Andrews]

Peter,

That all sounds possible, I haven't looked too much into the actual code
behind each demo. I guess that's where I go from here.
I was hoping for, users edits various cells and then a way to do a bunch of
inserts or updates into the database after the form is closed.

It does sound like a good example for one of you flex grid gurus to pursue,
hint hint.

Thanks,
Mark

 

[Peter Hibbs]

Mark,

As you say, if you want to use flex grids then you really have to get
into VBA programming so I guess that would be your first priority.

As a matter of interest, what sort of data are you trying to display
on your form and how is it stored in your table/s. When I get time it
might be an interesting project to add a 'cell editing' facility to my
Crosstab Flex Grid demo rather than use the pop up form to change the
contents (BTW what don't you like about that option, it is much easier
to code than editing a cell directly).

Peter Hibbs.

 

[Mark Andrews]

VBA programming is no problem for me, I just try and steal as much as
possible instead of recreating it.

I have information on government grants so it will be Grant Number as column
headings and objective as row headings
The values in the cells are always numbers.


Example "how many jobs were created by each grant" 100 300 50
total: 450

Between 5 and 40 grants and about 40 objectives, probably have dropdowns at
the top of the page where you pick office and month/year and then the
corresponding data is displayed and can edited.

I'm afraid that the excel lovers that I am trying to convince will say the
popup on every cell is annoying.
The other example that looks like you are just editing a cell in a flexgrid
looks close enough to excel for me.

I'll probably beat you to changing your demo, I'll send you the code if I
can.

have you compared other grid controls? Just wondering if flex grid is the
control I should use.
I haven't looked at ActiveX control on the market for a long time. I would
love to see an article describing the current state of
what grid controls can be purchased that work with Access. Example I looked
at componentone but I don't think I want to buy the entire activex suite for
$1000 just to get a grid (my needs are not that complex for this project).

Thanks for your examples they do provide a nice headstart! Do you accept
direct emails if I have a question as I proceed today?

Mark

 

[Peter Hibbs]

Mark,

The only other commercial Flex Grid control that I know of is from
ComponentSource but I have not used it myself and I am not sure how
much it costs but I believe you can edit cells directly with that one
as well as a whole lot of other useful facilities. Have a look at the
VS Flex Grid Pro control at :-
http://www.componentsource.com/products/componentone-vsflexgrid-pro/index-gbp.html
If you do decide to use that one I would be interested to hear how you
get on with it.

Feel free to email me if you have any flex grid questions, I will try
and answer them if I can and I would be interested to see your
database if you do get it working how you want.

I also had another thought about the totals column on the right of the
grid. Rather than add another flex grid control to the form I would
just overwrite the data in the right most column in the grid with all
the totals for each row any time you change any data in a cell.

Another thought, I believe Access comes with a Spreadsheet ActiveX
control which is like a mini Excel and you should see it in the list
of ActiveX controls, it is called Microsoft Office Spreadsheet 10.00
(or whatever version you are using). I have tried it out and it works
quite well but I have never used it in a proper database program so I
am not sure how you would program it with VBA. Might be worth looking
at if your users are wedded to Excel.

Peter Hibbs.

 

[Mark Andrews]

Thanks for the tips!

Mark

Mark,

The only other commercial Flex Grid control that I know of is from
ComponentSource but I have not used it myself and I am not sure how
much it costs but I believe you can edit cells directly with that one
as well as a whole lot of other useful facilities. Have a look at the
VS Flex Grid Pro control at :-
http://www.componentsource.com/products/componentone-vsflexgrid-pro/index-gbp.html
If you do decide to use that one I would be interested to hear how you
get on with it.

Feel free to email me if you have any flex grid questions, I will try
and answer them if I can and I would be interested to see your
database if you do get it working how you want.

I also had another thought about the totals column on the right of the
grid. Rather than add another flex grid control to the form I would
just overwrite the data in the right most column in the grid with all
the totals for each row any time you change any data in a cell.

Another thought, I believe Access comes with a Spreadsheet ActiveX
control which is like a mini Excel and you should see it in the list
of ActiveX controls, it is called Microsoft Office Spreadsheet 10.00
(or whatever version you are using). I have tried it out and it works
quite well but I have never used it in a proper database program so I
am not sure how you would program it with VBA. Might be worth looking
at if your users are wedded to Excel.

Peter Hibbs.

 

[Mark Andrews]

Just as a follow up I was able to combine the two examples with little
problem. The more difficult part was getting the crosstab to generate in
exactly the right ways.
I ended up with multiple queries (some that get updated in code that drive
the ending crosstab query).

I ended up using a method where the grid is loaded after combo boxes are
updated and the grid is saved at close and before combo boxes are loaded.
Load was very similar to the examples.

On saving I also used the varray to compare with the flex grid to determine
is the cell was changed and if so the record was either inserted, updated or
deleted (which ever was needed).

At some point in the future I might generize the example and put it on my
site.

The real grid from componentone is only sold as part of the activex suite
$800, the guy offered me $50 off.

If anyone knows of a good grid component for a few hundred bucks I might be
interested.

Mark
RPT Software
http://www.rptsoftware.com