referring to an Active x spreadsheet control

[CJ]

Hi,

I have a requirement to develop a form which enables users to write a
complex calculation and save the result. There are over a hundred
calculations and which one to use is triggered by a value in the data. Just
to make things more complicated, the calculations change over time. The users
are not sql query proficient and I could write a query builder for them but
it occurs to me that a potentially simpler solution would be to use an active
x spreadsheet which would take some values from the form which are then
manipulated by the spreadsheet and then 'post out' to a result field.

One other requirement is that the calculation is saved so it can be audited
at a later time.

However I cannot see how to access the active x spreadsheet to put values in
or receive them out (based on an event). Please can someone tell me if this
is possible or alternatively if I am barking up the wrong tree.
Alternatively, can someone suggest an alternative methodology?


Many thanks

CJ

 

[Mark Andrews]

I remember doing an application a long time ago that tracked measurements
(taken say 3 times/day every day) and the user could add in their own
formulas.
I think I ended up using the "Eval" function to execute the formula at
runtime (with appropriate error checking), after perhaps some code
substituted the data values.

It might help to give some more detailed examples for anyone to suggest a
solution.

My first guess would be NOT to use an ActiveX spreadsheet control.

HTH,

 

[CJ]

Thank you for responding

I'm beginning to come to that conclusion myself.

I've now discovered how to reference the data,
(me.spreadsheet0.cells(1,1).value) but the active x control seems to be
limited in use. i.e. I want to be able to lock certain cells containing
headers for example and it would be useful to be able to specify background,
formats etc for those cells that are used for 'input' and 'output' since they
need to be under program control.

It would also be useful to be able to limit the potential size in terms of
number of columns and rows otherwise the code to capture the spreadsheet
contents for future reference could take a long time to run (I haven't tried
it yet so may be pleasantly surprised!). At the moment I would anticipate the
code to be something like:

longstring=""
for c=column(first) to column(last) do
for r=row(first) to row(last) do
longstring= longstring & "|" $ c $ "|" & r & "|" &
me.spreadsheet0.cells(r,c).value2

I've used the eval function before but this particular situation will almost
certainly end up being 'high maintenance'

CJ

 

[Mark Andrews]

Still not exactly sure what you need to do? Lots of examples using the
flexgrid control out there, or you could buy one with more features or you
could use built in access datasheets or controls. Finding it difficult to
answer without specifics.

Mark