Access 2010 Data Macros: The Three-Legged Hunting Dog With One Bad Eye

[Neil]

The tale of your mental anguish both entertains me and I laugh while I
also recall times of code frustration and share your pain and
sympathize with your plight.

==============================

Thank you, sir! I appreciate that. I will share the latest here, in case
anyone is following this saga, both for information, as well as
entertainment, purposes.

OK, so, to recap. Reporting isn't available in Access Services in Office
365, which I'm using. So I'm using a form.

But Group By queries aren't available in Access 2010 web queries. So I'm
left with using Sum() in the form footer.

But I need group totals, not just overall totals. So I need to do
=Sum(IIF([Device]=1, [DeviceValue], 0)).

But Access web forms don't like that. Even though both Sum() and IIF() are
supported in web form controls, you can't use IIF() inside of a Sum().

So that just about ended it.

But then I realized: well, I could just de-normalize my data when uploading
to the web, and upload records for both devices to a single record with two
sets of fields. So that's what I did.

So I got the totals I needed by doing =Sum([DeviceValue1]) and
=Sum([DeviceValue2]). Seems like the end, right? No such luck! Get ready for
part 2......

Part 2

OK, so I finally got my totals on the form. However..... one of the values
is a total number of minutes. And it needs to be displayed in hr:min format
(e.g., 130 would be 2:10).

In Access this would be simple: Format([Minutes]/60/24, "h:nn").

However.... in Access web forms, you can't use custom formats; only pre-set
ones. And, wouldn't you know it, there's no pre-set format for h:nn in
24-hour format; only h:nn with AM or PM (which, obviously, wouldn't work for
what I needed).

So I was stuck with having to format the value manually. No problem, right?
Wrong.

Normally, in Access, this would be a simple thing, even when done manually.
Something like:

Hrs = Minutes/60

HrsInteger = Int(Hrs)

MinInteger = (Hrs - HrsInteger) * 60

Piece of cake. However..... in an Access web database, there is no Int
function. And, though I searched high and low, there is nothing that comes
even close to it.

So, what I was left with was converting Hrs to a string, finding the
position of the decimal point using Instr (fortunately, you CAN use Instr in
a web database!!), and then grabbing each piece to the left and the right of
the decimal point, converting to the proper values, and then finally
reconcatenating with something like =[hrsinteger] & ":" & [mininteger].

I set this up initially using a series of calculated text boxes so that if
something wasn't working in the process, I could see which text box in the
assembly line the error was. I ended up with 7 calculated controls starting
with the initial =Sum(DeviceValue1]) though the final formatted value in
h:nn format. Keep in mind that this is 7 calculated controls just to do a
simple format!

OK, so I got this working - 7 controls for the first value, then 7 more for
the second value, and then 7 more for the total. All of these were hidden,
and everything was working fine.

So the next step was to convert the 7 controls to a data macro so that I
could use it without the need for the 7 controls each time.

I wrote the data macro, got it working, and everything was fine with my test
values. I input a value to the macro, and it gave me the value in h:nn
format. I was starting to feel relieved.

But, as has been the rule on this journey, once you get one thing working,
the next "gotcha" is waiting up ahead. And, after all that, I ran into the
next "gotcha."

What was it? Simple: you (apparently) can't pass a Sum() value to a data
macro!!!!! So, after all that, I couldn't even use my data macro.

I could, of course, still implement my
7-hidden-controls-for-each-needed-format solution. That works (and, believe
me, I'm thankful for that!). But the data macro, which would make things so
much simpler (and cleaner) would not work.

The macro worked fine with regular numbers. But not Sum() values.

Took me a while to figure out that the Sum() was the problem, since the
error message you receive ("The RunDataMacro action failed to invoke a data
macro on the server. Please check your connectivity to the server.") is one
of the most unhelpful error messages of all unhelpful error messages in the
history of unhelpful error messages. So I wasted precious time trying to
figure out why it couldn't see the macro on the server. Only after much
trial and error did I determine that it was a data issue, that data macros
just don't like being passed Sum() values.

And I tried referencing the Sum() control with another control and passing
that second control's value to the macro. Didn't help. I tried storing the
Sum() value in a local variable within the calling macro first, and then
passing the local variable value to the macro. Didn't help.

So, I was stuck with having to use 7 hidden text boxes for every format I
needed (3 in one subform and 10 in another subform - a total of 91 hidden
boxes just to do formatting!), all because I couldn't pass a Sum() value to
a data macro.

I had basically given up on being able to use my data macro, because I had
to pass it a Sum() value, and resigned myself to using the 91 hidden text
boxes(!) instead.

But then, when I took a step back, I realized something that I attribute to
JUST DUMB LUCK. One of the greatest instances of dumb luck that I can
remember.

When I wrote the macro, I did it in a test database, so as not to complicate
things with my regular data. I had a simple table with a few records, and
wrote and tested the macro there. And the macro worked.

And so I realized something: the test database was using a Sum() value, and
the macro was working. Why was it working there, but not in my real
database?

Well, here's the "dumb luck" part. When I tested the macro in the test
database, for no apparent reason, I decided to pass it the value of a
control that was 2x the Sum() value. So instead of passing it, say, [txtSum]
with the value =Sum([DeviceValue1]), I passed it, say, [txtSumX2] with the
value =[txtSum]*2. And that worked with the macro!

So I realized that if you perform math on a Sum() value, the data macro will
take it; but a straight Sum() value, it won't.

So I created a second text box with the value [DeviceSum]*1. And passing it
that value works.

So if I hadn't just randomly decided to multiple my value by 2 in my test
database before passing it to the macro, I never would have figured this
out. I would be stuck at "you can't pass Sum() values to data macros,"
instead of "you can if you multiply it by 1 first"!

(And, again, I did try just referencing the [DeviceSum] text box (with the
Sum() value) in a second text box, but that didn't work. Only by doing the
*1 did it work. Also, I tried doing =Sum([DeviceSum])*1 within a single text
box. And, while that worked in Access, when put on the web it failed. So I
had to use two text boxes.)

So this technology CLEARLY is not ready for prime time! Maybe the next
version will be better. Let's hope so. Otherwise, it becomes sort of like a
bad comedy that just does not seem to want to end.

 

[Patrick Finucane]

The tale of your mental anguish both entertains me and I laugh while I
also recall times of code frustration and share your pain and
sympathize with your plight.

==============================

Thank you, sir! I appreciate that. I will share the latest here, in case
anyone is following this saga, both for information, as well as
entertainment, purposes.

OK, so, to recap. Reporting isn't available in Access Services in Office
365, which I'm using. So I'm using a form.

But Group By queries aren't available in Access 2010 web queries. So I'm
left with using Sum() in the form footer.

But I need group totals, not just overall totals. So I need to do
=Sum(IIF([Device]=1, [DeviceValue], 0)).

But Access web forms don't like that. Even though both Sum() and IIF() are
supported in web form controls, you can't use IIF() inside of a Sum().

So that just about ended it.

But then I realized: well, I could just de-normalize my data when uploading
to the web, and upload records for both devices to a single record with two
sets of fields. So that's what I did.

So I got the totals I needed by doing =Sum([DeviceValue1]) and
=Sum([DeviceValue2]). Seems like the end, right? No such luck! Get ready for
part 2......

Part 2

OK, so I finally got my totals on the form. However..... one of the values
is a total number of minutes. And it needs to be displayed in hr:min format
(e.g., 130 would be 2:10).

In Access this would be simple: Format([Minutes]/60/24, "h:nn").

However.... in Access web forms, you can't use custom formats; only pre-set
ones. And, wouldn't you know it, there's no pre-set format for h:nn in
24-hour format; only h:nn with AM or PM (which, obviously, wouldn't work for
what I needed).

So I was stuck with having to format the value manually. No problem, right?
Wrong.

Normally, in Access, this would be a simple thing, even when done manually.
Something like:

Hrs = Minutes/60

HrsInteger = Int(Hrs)

MinInteger = (Hrs - HrsInteger) * 60

Piece of cake. However..... in an Access web database, there is no Int
function. And, though I searched high and low, there is nothing that comes
even close to it.

So, what I was left with was converting Hrs to a string, finding the
position of the decimal point using Instr (fortunately, you CAN use Instrin
a web database!!), and then grabbing each piece to the left and the rightof
the decimal point, converting to the proper values, and then finally
reconcatenating with something like =[hrsinteger] & ":" & [mininteger].

I set this up initially using a series of calculated text boxes so that if
something wasn't working in the process, I could see which text box in the
assembly line the error was. I ended up with 7 calculated controls starting
with the initial =Sum(DeviceValue1]) though the final formatted value in
h:nn format. Keep in mind that this is 7 calculated controls just to do a
simple format!

OK, so I got this working - 7 controls for the first value, then 7 more for
the second value, and then 7 more for the total. All of these were hidden,
and everything was working fine.

So the next step was to convert the 7 controls to a data macro so that I
could use it without the need for the 7 controls each time.

I wrote the data macro, got it working, and everything was fine with my test
values. I input a value to the macro, and it gave me the value in h:nn
format. I was starting to feel relieved.

But, as has been the rule on this journey, once you get one thing working,
the next "gotcha" is waiting up ahead. And, after all that, I ran into the
next "gotcha."

What was it? Simple: you (apparently) can't pass a Sum() value to a data
macro!!!!! So, after all that, I couldn't even use my data macro.

I could, of course, still implement my
7-hidden-controls-for-each-needed-format solution. That works (and, believe
me, I'm thankful for that!). But the data macro, which would make things so
much simpler (and cleaner) would not work.

The macro worked fine with regular numbers. But not Sum() values.

Took me a while to figure out that the Sum() was the problem, since the
error message you receive ("The RunDataMacro action failed to invoke a data
macro on the server. Please check your connectivity to the server.") is one
of the most unhelpful error messages of all unhelpful error messages in the
history of unhelpful error messages. So I wasted precious time trying to
figure out why it couldn't see the macro on the server. Only after much
trial and error did I determine that it was a data issue, that data macros
just don't like being passed Sum() values.

And I tried referencing the Sum() control with another control and passing
that second control's value to the macro. Didn't help. I tried storing the
Sum() value in a local variable within the calling macro first, and then
passing the local variable value to the macro. Didn't help.

So, I was stuck with having to use 7 hidden text boxes for every format I
needed (3 in one subform and 10 in another subform - a total of 91 hidden
boxes just to do formatting!), all because I couldn't pass a Sum() value to
a data macro.

I had basically given up on being able to use my data macro, because I had
to pass it a Sum() value, and resigned myself to using the 91 hidden text
boxes(!) instead.

But then, when I took a step back, I realized something that I attribute to
JUST DUMB LUCK. One of the greatest instances of dumb luck that I can
remember.

When I wrote the macro, I did it in a test database, so as not to complicate
things with my regular data. I had a simple table with a few records, and
wrote and tested the macro there. And the macro worked.

And so I realized something: the test database was using a Sum() value, and
the macro was working. Why was it working there, but not in my real
database?

Well, here's the "dumb luck" part. When I tested the macro in the test
database, for no apparent reason, I decided to pass it the value of a
control that was 2x the Sum() value. So instead of passing it, say, [txtSum]
with the value =Sum([DeviceValue1]), I passed it, say, [txtSumX2] with the
value =[txtSum]*2. And that worked with the macro!

So I realized that if you perform math on a Sum() value, the data macro will
take it; but a straight Sum() value, it won't.

So I created a second text box with the value [DeviceSum]*1. And passing it
that value works.

So if I hadn't just randomly decided to multiple my value by 2 in my test
database before passing it to the macro, I never would have figured this
out. I would be stuck at "you can't pass Sum() values to data macros,"
instead of "you can if you multiply it by 1 first"!

(And, again, I did try just referencing the [DeviceSum] text box (with the
Sum() value) in a second text box, but that didn't work. Only by doing the
*1 did it work. Also, I tried doing =Sum([DeviceSum])*1 within a singletext
box. And, while that worked in Access, when put on the web it failed. So I
had to use two text boxes.)

So this technology CLEARLY is not ready for prime time! Maybe the next
version will be better. Let's hope so. Otherwise, it becomes sort of likea
bad comedy that just does not seem to want to end.

Back in the old days folks supplied canned reports. Is it possible to
persuade the powers-that-be to use canned reports. Then with a
regular client app generate table lists where you can create generic
reports. IOW, sum of the fields of importance on a daily basis,
weekly basis, monthly basis, maybe yearly basis.

Some managers like walking into a meeting with a report a foot high to
demonstrate how miuch work he is doing. The manager might only need a
few figures to make a point, but a foot high report does look
impressive.

But the important fields to help managers make a decision are finite.
If they can be defined, then canned reports can be generated. I'm
not sure when the next version of Office will be released but I expect
it will come out shortly after the next version of Windows. Folks are
under NDA regarding Access. But maybe the improvements you need will
be in place and you can adjust then to suit.

 

[Neil]

Here's an update, just to show how absurd this whole thing is. Finally got a
mechanism that works, after all the tweaking, prodding, bending, twisting,
hair pulling, etc. However, I have 3 subforms. And, while I can easily
filter one subform to show the correct data, if I try to filter a second, I
get an error that a correct subreport control name must be used. Remove the
first Filter commend, and the second one works fine. Put the first one back
in, and the second one again tells you the name is wrong (even though it
worked fine when the first one was removed).

So it seems that you can't filter more than one subform in this
oh-so-nascent technology. So, after all that, I'm kind of back at the
drawing board.

A glimmer of hope. I did stumble across this blog post by Dick Moffat
(http://dmoffat.wordpress.com/2011/10/07/tempvars-looks-like-the-secret-in-access-services-web-forms/),
where he talks about his own struggles in getting this thing to work, and he
mentions the use of tempvars as a solution. So, his solution seems very
promising. Plus, it was good to know that I'm not completely inept, since
Dick seems to have been experiencing the same kinds of issues that I've
been.

He writes in another blog post a few weeks later
(http://dmoffat.wordpress.com/2011/12/31/2011-in-review/):

"I believe it is time for Microsoft to get out and show people how to use
this stuff rather than just hoping they figure it out on their own. I don't
expect them to do anything though and if so I think we will continue to see
Excel and Access lose altitude. I don't think it's enough to just hope that
people 'get it'."

I think he's right.

 

[Neil]

The tale of your mental anguish both entertains me and I laugh while I
also recall times of code frustration and share your pain and
sympathize with your plight.

==============================

Thank you, sir! I appreciate that. I will share the latest here, in case
anyone is following this saga, both for information, as well as
entertainment, purposes.

OK, so, to recap. Reporting isn't available in Access Services in Office
365, which I'm using. So I'm using a form.

But Group By queries aren't available in Access 2010 web queries. So I'm
left with using Sum() in the form footer.

But I need group totals, not just overall totals. So I need to do
=Sum(IIF([Device]=1, [DeviceValue], 0)).

But Access web forms don't like that. Even though both Sum() and IIF() are
supported in web form controls, you can't use IIF() inside of a Sum().

So that just about ended it.

But then I realized: well, I could just de-normalize my data when
uploading
to the web, and upload records for both devices to a single record with
two
sets of fields. So that's what I did.

So I got the totals I needed by doing =Sum([DeviceValue1]) and
=Sum([DeviceValue2]). Seems like the end, right? No such luck! Get ready
for
part 2......

Part 2

OK, so I finally got my totals on the form. However..... one of the values
is a total number of minutes. And it needs to be displayed in hr:min
format
(e.g., 130 would be 2:10).

In Access this would be simple: Format([Minutes]/60/24, "h:nn").

However.... in Access web forms, you can't use custom formats; only
pre-set
ones. And, wouldn't you know it, there's no pre-set format for h:nn in
24-hour format; only h:nn with AM or PM (which, obviously, wouldn't work
for
what I needed).

So I was stuck with having to format the value manually. No problem,
right?
Wrong.

Normally, in Access, this would be a simple thing, even when done
manually.
Something like:

Hrs = Minutes/60

HrsInteger = Int(Hrs)

MinInteger = (Hrs - HrsInteger) * 60

Piece of cake. However..... in an Access web database, there is no Int
function. And, though I searched high and low, there is nothing that comes
even close to it.

So, what I was left with was converting Hrs to a string, finding the
position of the decimal point using Instr (fortunately, you CAN use Instr
in
a web database!!), and then grabbing each piece to the left and the right
of
the decimal point, converting to the proper values, and then finally
reconcatenating with something like =[hrsinteger] & ":" & [mininteger].

I set this up initially using a series of calculated text boxes so that if
something wasn't working in the process, I could see which text box in the
assembly line the error was. I ended up with 7 calculated controls
starting
with the initial =Sum(DeviceValue1]) though the final formatted value in
h:nn format. Keep in mind that this is 7 calculated controls just to do a
simple format!

OK, so I got this working - 7 controls for the first value, then 7 more
for
the second value, and then 7 more for the total. All of these were hidden,
and everything was working fine.

So the next step was to convert the 7 controls to a data macro so that I
could use it without the need for the 7 controls each time.

I wrote the data macro, got it working, and everything was fine with my
test
values. I input a value to the macro, and it gave me the value in h:nn
format. I was starting to feel relieved.

But, as has been the rule on this journey, once you get one thing working,
the next "gotcha" is waiting up ahead. And, after all that, I ran into the
next "gotcha."

What was it? Simple: you (apparently) can't pass a Sum() value to a data
macro!!!!! So, after all that, I couldn't even use my data macro.

I could, of course, still implement my
7-hidden-controls-for-each-needed-format solution. That works (and,
believe
me, I'm thankful for that!). But the data macro, which would make things
so
much simpler (and cleaner) would not work.

The macro worked fine with regular numbers. But not Sum() values.

Took me a while to figure out that the Sum() was the problem, since the
error message you receive ("The RunDataMacro action failed to invoke a
data
macro on the server. Please check your connectivity to the server.") is
one
of the most unhelpful error messages of all unhelpful error messages in
the
history of unhelpful error messages. So I wasted precious time trying to
figure out why it couldn't see the macro on the server. Only after much
trial and error did I determine that it was a data issue, that data macros
just don't like being passed Sum() values.

And I tried referencing the Sum() control with another control and passing
that second control's value to the macro. Didn't help. I tried storing the
Sum() value in a local variable within the calling macro first, and then
passing the local variable value to the macro. Didn't help.

So, I was stuck with having to use 7 hidden text boxes for every format I
needed (3 in one subform and 10 in another subform - a total of 91 hidden
boxes just to do formatting!), all because I couldn't pass a Sum() value
to
a data macro.

I had basically given up on being able to use my data macro, because I had
to pass it a Sum() value, and resigned myself to using the 91 hidden text
boxes(!) instead.

But then, when I took a step back, I realized something that I attribute
to
JUST DUMB LUCK. One of the greatest instances of dumb luck that I can
remember.

When I wrote the macro, I did it in a test database, so as not to
complicate
things with my regular data. I had a simple table with a few records, and
wrote and tested the macro there. And the macro worked.

And so I realized something: the test database was using a Sum() value,
and
the macro was working. Why was it working there, but not in my real
database?

Well, here's the "dumb luck" part. When I tested the macro in the test
database, for no apparent reason, I decided to pass it the value of a
control that was 2x the Sum() value. So instead of passing it, say,
[txtSum]
with the value =Sum([DeviceValue1]), I passed it, say, [txtSumX2] with the
value =[txtSum]*2. And that worked with the macro!

So I realized that if you perform math on a Sum() value, the data macro
will
take it; but a straight Sum() value, it won't.

So I created a second text box with the value [DeviceSum]*1. And passing
it
that value works.

So if I hadn't just randomly decided to multiple my value by 2 in my test
database before passing it to the macro, I never would have figured this
out. I would be stuck at "you can't pass Sum() values to data macros,"
instead of "you can if you multiply it by 1 first"!

(And, again, I did try just referencing the [DeviceSum] text box (with the
Sum() value) in a second text box, but that didn't work. Only by doing the
*1 did it work. Also, I tried doing =Sum([DeviceSum])*1 within a single
text
box. And, while that worked in Access, when put on the web it failed. So I
had to use two text boxes.)

So this technology CLEARLY is not ready for prime time! Maybe the next
version will be better. Let's hope so. Otherwise, it becomes sort of like
a
bad comedy that just does not seem to want to end.

Back in the old days folks supplied canned reports. Is it possible to
persuade the powers-that-be to use canned reports. Then with a
regular client app generate table lists where you can create generic
reports. IOW, sum of the fields of importance on a daily basis,
weekly basis, monthly basis, maybe yearly basis.

Some managers like walking into a meeting with a report a foot high to
demonstrate how miuch work he is doing. The manager might only need a
few figures to make a point, but a foot high report does look
impressive.

But the important fields to help managers make a decision are finite.
If they can be defined, then canned reports can be generated. I'm
not sure when the next version of Office will be released but I expect
it will come out shortly after the next version of Windows. Folks are
under NDA regarding Access. But maybe the improvements you need will
be in place and you can adjust then to suit.

================================

Yeah, that's a good idea, actually. Daily, Weekly, Month To Date, Year to
Date, etc. Generate them all each night (with current day's report generated
hourly or whatever). I'll keep that in mind.

For now, I finally got it working. But there are still a lot things to
overcome.

Seems to be some sort of 5,000 record limit that, when you hit it, it won't
return records from the table (even if your query limits the records being
returned).

Office 365 says it allows non-O365 access (but only with a Hotmail login
(typical Microsoft...). However, the Hotmail logins don't seem to work. Page
immediately times out.

And, while the page seems to work well in Chrome (get ready for this one),
it throws up errors in Internet Explorer! LOL!!! Oh, Microsoft. You're
always good for a laugh, aren't you?

Anyway, I think your idea is a good one. Even though I got it working, for
the most part, having pre-built reports with just the report data up there
and no live calculations might be a good workaround for these issues.

Neil
P.S. Sorry about the delayed response here. After going through what I
described, I needed to step away from this discussion for a bit. I'm sure
you understand.

 

[Neil]

Back in the old days folks supplied canned reports. Is it possible to
persuade the powers-that-be to use canned reports. Then with a
regular client app generate table lists where you can create generic
reports. IOW, sum of the fields of importance on a daily basis,
weekly basis, monthly basis, maybe yearly basis.

=====================

Patrick:

Just wanted to pop in here to tell you that I think you're a genius.
That was a great idea.

I talked with the client, and they were fine with having the data compiled
in Access for pre-set timeframes (daily, weekly, etc.). He said he only
mentioned the variable start and end dates as an idea, not as an absolute.
So this is great.

I'm compiling all the data in Access and throwing it up into SharePoint
tables already calculated, totaled, formatted, etc. Very nice. Much faster
to do, and will allow me to get a lot more done, instead of wrestling with
something that takes forever to do the slightest thing.

At the same time.... I'm glad I went down that other road initially. I
learned a heck of a lot - a lot more than I would have if I had taken this
easier (read: more sane, logical) path initially. So I'm kind of glad I did
that. I now have a much better understanding of how it all works, as well as
many of the pitfalls you can fall into, which I still may have to avoid. So
it's not all a waste. Just wish somehow I can get those few weeks of my life
back.... ;-)

Anyway, thanks again. Great idea! Game changer.

Neil

 

[Patrick Finucane]

Back in the old days folks supplied canned reports.  Is it possible to
persuade the powers-that-be to use canned reports.  Then with a
regular client app generate table lists where you can create generic
reports.  IOW, sum of the fields of importance on a daily basis,
weekly basis, monthly basis, maybe yearly basis.

=====================

Patrick:

Just wanted to pop in here to tell you that I think you're a genius.
That was a great idea.

I talked with the client, and they were fine with having the data compiled
in Access for pre-set timeframes (daily, weekly, etc.). He said he only
mentioned the variable start and end dates as an idea, not as an absolute..
So this is great.

I'm compiling all the data in Access and throwing it up into SharePoint
tables already calculated, totaled, formatted, etc. Very nice. Much faster
to do, and will allow me to get a lot more done, instead of wrestling with
something that takes forever to do the slightest thing.

At the same time.... I'm glad I went down that other road initially. I
learned a heck of a lot - a lot more than I would have if I had taken this
easier (read: more sane, logical) path initially. So I'm kind of glad I did
that. I now have a much better understanding of how it all works, as wellas
many of the pitfalls you can fall into, which I still may have to avoid. So
it's not all a waste. Just wish somehow I can get those few weeks of my life
back.... ;-)

Anyway, thanks again. Great idea! Game changer.

Neil

Thanks for the compliment, Neil. I'm glad your client is flexible.
Also glad you learned a lot.

 

[a a r o n . k e m p f]

Access reports are obsolete. Right-click IMPORT your Access reports into SQL Server Reporting Services... and then they are available on the web.

 

[a a r o n . k e m p f]

I use SQL debugger all day every day. I just love it.

Of course, I generally debug from the server, not from my desktop.. I'm accessing the SQL Server from a a VPNs, so it's not really an option to open additional ports.