By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,206 Members | 1,020 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,206 IT Pros & Developers. It's quick & easy.

Must use macros! How shall I comply?

P: n/a
Because my work area won't have an Access programmer next year, I've
been asked to rebuild their coded application as a set of modular
tools. The idea is that a non-programmer will be able to modify it as
requirements change.
More generally, in fact, they want a general package of functions that
will anticipate *all* requirements.

They must be called as macros.

I can appreciate that a simple sequence of operations could be
conveniently strung together using a macro.

I had an idea that a macro along the lines "Load Spreadsheet to Table
with Validation Errors" might be useful. This would essentially be
like TransferSpreadsheet with a few extra controls and helps to better
identify errors.

However, my manager wants more "atomic" functions than this.
Validation functions of the type "IsAlpha" for example. He would like
to validate fields within each record before they're presented to the
table where these types and other constraints are defined. These are
to be controlled by macro.

As I see it, this puts the non-programmer in the position of using
macro loops.
I've had difficulty coding these myself: e.g., the end-of-table
condition can't be tested directly. I wrote a (VBA) function
[DoMacroOverTable, below] to accept a macro and a table and to apply
the macro for every record in the table. And, whilst I could make the
table the "current object" and use GotoRecord to change the "current
record", I can't see how (within the called macro) to refer to this
record or to fields within it (short of defining a form and fields for
it).

Any ideas?

Also, so far I have lost the argument on my objections to this
particular strategy. (Redundancy of many of these tests when
declarable as table properties and referential integrity constraints;
better application where applicable within select or update queries
than in macros). If anyone can supply their reasonings (either way!)
it may assist me.

Public Function DoMacroOverTable(sMacro As String, sTableName As
String)

DoCmd.SelectObject acTable, sTableName
DoCmd.GoToRecord , , acFirst

On Error GoTo DoMacroOverTableErr
Do Until False

DoCmd.GoToRecord acActiveDataObject, , acNext

DoCmd.RunMacro (sMacro)

Loop
Exit Function ' never reached!
DoMacroOverTableErr:
If Err = 2105 Then
' expected
Else
MsgBox "Unexpected Error: " & Err.Number & ":" & Err.Description,
vbCritical, "DoMacroOverTable"
Stop
Resume
End If
End Function

(End Message)
Nov 12 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
David, quite seriously, I would leave a job and find employment elsewhere if
some manager insisted that I work in Access like that.

He clearly has no idea about error handling, the validation checks you need
in every form, reusable code, multi-user issues, recordsets, flexible
reporting that accepts builds the WhereConditon at runtime, logging of
errors, type-checking, or anything else that will give you a robust and
reliable application.

If that person wants to tell me *how* I must go about achieving the result
he needs when he has no understanding of the processes, I'm wasting my life
there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"David Powell" <da***@powell-au.net> wrote in message
news:b5**************************@posting.google.c om...
Because my work area won't have an Access programmer next year, I've
been asked to rebuild their coded application as a set of modular
tools. The idea is that a non-programmer will be able to modify it as
requirements change.
More generally, in fact, they want a general package of functions that
will anticipate *all* requirements.

They must be called as macros.

I can appreciate that a simple sequence of operations could be
conveniently strung together using a macro.

I had an idea that a macro along the lines "Load Spreadsheet to Table
with Validation Errors" might be useful. This would essentially be
like TransferSpreadsheet with a few extra controls and helps to better
identify errors.

However, my manager wants more "atomic" functions than this.
Validation functions of the type "IsAlpha" for example. He would like
to validate fields within each record before they're presented to the
table where these types and other constraints are defined. These are
to be controlled by macro.

As I see it, this puts the non-programmer in the position of using
macro loops.
I've had difficulty coding these myself: e.g., the end-of-table
condition can't be tested directly. I wrote a (VBA) function
[DoMacroOverTable, below] to accept a macro and a table and to apply
the macro for every record in the table. And, whilst I could make the
table the "current object" and use GotoRecord to change the "current
record", I can't see how (within the called macro) to refer to this
record or to fields within it (short of defining a form and fields for
it).

Any ideas?

Also, so far I have lost the argument on my objections to this
particular strategy. (Redundancy of many of these tests when
declarable as table properties and referential integrity constraints;
better application where applicable within select or update queries
than in macros). If anyone can supply their reasonings (either way!)
it may assist me.

Public Function DoMacroOverTable(sMacro As String, sTableName As
String)

DoCmd.SelectObject acTable, sTableName
DoCmd.GoToRecord , , acFirst

On Error GoTo DoMacroOverTableErr
Do Until False

DoCmd.GoToRecord acActiveDataObject, , acNext

DoCmd.RunMacro (sMacro)

Loop
Exit Function ' never reached!
DoMacroOverTableErr:
If Err = 2105 Then
' expected
Else
MsgBox "Unexpected Error: " & Err.Number & ":" & Err.Description,
vbCritical, "DoMacroOverTable"
Stop
Resume
End If
End Function

(End Message)

Nov 12 '05 #2

P: n/a
I agree completely with Allen. The lack of error handling alone makes macros
off limits to me (expect for AutoKeys). You will never have a robust, stable
application based entirely on macros. You could certainly build a few
trivial programs using only macros, but nothing that would be used to solve
or assist in any real business problems. And many people, even novices, find
Macros confusing ... well commented VBA code is usually much more easy to
follow than a macro.

Look at it this way - this project is doomed to failure before it even
starts. Guess who will get the blame ... your manager??? I think not. If it
were me, I'd document everything ... have your manager put, in writing, what
exactly he/she wants (i.e. Macros only, etc etc), then add your own comments
regarding the feasibility of this approach. This will likely be a tricky
issue, since managers HATE to have anything in writing that can come back to
haunt them. Save emails, memos, etc etc ... you'll need documentation to
show the higher-ups who authorized this fiasco.

Put another way, your manager is asking you to drive the car without using
the engine ... if it were me, I'd tell them "No".

"David Powell" <da***@powell-au.net> wrote in message
news:b5**************************@posting.google.c om...
Because my work area won't have an Access programmer next year, I've
been asked to rebuild their coded application as a set of modular
tools. The idea is that a non-programmer will be able to modify it as
requirements change.
More generally, in fact, they want a general package of functions that
will anticipate *all* requirements.

They must be called as macros.

I can appreciate that a simple sequence of operations could be
conveniently strung together using a macro.

I had an idea that a macro along the lines "Load Spreadsheet to Table
with Validation Errors" might be useful. This would essentially be
like TransferSpreadsheet with a few extra controls and helps to better
identify errors.

However, my manager wants more "atomic" functions than this.
Validation functions of the type "IsAlpha" for example. He would like
to validate fields within each record before they're presented to the
table where these types and other constraints are defined. These are
to be controlled by macro.

As I see it, this puts the non-programmer in the position of using
macro loops.
I've had difficulty coding these myself: e.g., the end-of-table
condition can't be tested directly. I wrote a (VBA) function
[DoMacroOverTable, below] to accept a macro and a table and to apply
the macro for every record in the table. And, whilst I could make the
table the "current object" and use GotoRecord to change the "current
record", I can't see how (within the called macro) to refer to this
record or to fields within it (short of defining a form and fields for
it).

Any ideas?

Also, so far I have lost the argument on my objections to this
particular strategy. (Redundancy of many of these tests when
declarable as table properties and referential integrity constraints;
better application where applicable within select or update queries
than in macros). If anyone can supply their reasonings (either way!)
it may assist me.

Public Function DoMacroOverTable(sMacro As String, sTableName As
String)

DoCmd.SelectObject acTable, sTableName
DoCmd.GoToRecord , , acFirst

On Error GoTo DoMacroOverTableErr
Do Until False

DoCmd.GoToRecord acActiveDataObject, , acNext

DoCmd.RunMacro (sMacro)

Loop
Exit Function ' never reached!
DoMacroOverTableErr:
If Err = 2105 Then
' expected
Else
MsgBox "Unexpected Error: " & Err.Number & ":" & Err.Description,
vbCritical, "DoMacroOverTable"
Stop
Resume
End If
End Function

(End Message)

Nov 12 '05 #3

P: n/a
As other Posters have said, either don't bother OR if you do, C.Y.A!

If you get the time, learn VBA and you'll soon appreciate the power
and flexibility you are afforded - you'll probably never bother with
macro's again (except possibly Autoexec and/or Sendkeys).

Just my two penneth!

Cheers,

Phil

da***@powell-au.net (David Powell) wrote in message news:<b5**************************@posting.google. com>...
Because my work area won't have an Access programmer next year, I've
been asked to rebuild their coded application as a set of modular
tools. The idea is that a non-programmer will be able to modify it as
requirements change.
More generally, in fact, they want a general package of functions that
will anticipate *all* requirements.

They must be called as macros.

I can appreciate that a simple sequence of operations could be
conveniently strung together using a macro.

I had an idea that a macro along the lines "Load Spreadsheet to Table
with Validation Errors" might be useful. This would essentially be
like TransferSpreadsheet with a few extra controls and helps to better
identify errors.

However, my manager wants more "atomic" functions than this.
Validation functions of the type "IsAlpha" for example. He would like
to validate fields within each record before they're presented to the
table where these types and other constraints are defined. These are
to be controlled by macro.

As I see it, this puts the non-programmer in the position of using
macro loops.
I've had difficulty coding these myself: e.g., the end-of-table
condition can't be tested directly. I wrote a (VBA) function
[DoMacroOverTable, below] to accept a macro and a table and to apply
the macro for every record in the table. And, whilst I could make the
table the "current object" and use GotoRecord to change the "current
record", I can't see how (within the called macro) to refer to this
record or to fields within it (short of defining a form and fields for
it).

Any ideas?

Also, so far I have lost the argument on my objections to this
particular strategy. (Redundancy of many of these tests when
declarable as table properties and referential integrity constraints;
better application where applicable within select or update queries
than in macros). If anyone can supply their reasonings (either way!)
it may assist me.

Public Function DoMacroOverTable(sMacro As String, sTableName As
String)

DoCmd.SelectObject acTable, sTableName
DoCmd.GoToRecord , , acFirst

On Error GoTo DoMacroOverTableErr
Do Until False

DoCmd.GoToRecord acActiveDataObject, , acNext

DoCmd.RunMacro (sMacro)

Loop
Exit Function ' never reached!
DoMacroOverTableErr:
If Err = 2105 Then
' expected
Else
MsgBox "Unexpected Error: " & Err.Number & ":" & Err.Description,
vbCritical, "DoMacroOverTable"
Stop
Resume
End If
End Function

(End Message)

Nov 12 '05 #4

P: n/a
TC

"David Powell" <da***@powell-au.net> wrote in message
news:b5**************************@posting.google.c om...
Because my work area won't have an Access programmer next year, I've
been asked to rebuild their coded application as a set of modular
tools. The idea is that a non-programmer will be able to modify it as
requirements change.
It is certainly possible to build a set of modular tools, to let a
non-technical person assemble them in various ways for useful purposes.
There is nothing conceptually wrong with that. >BUT<, it takes substantial
technical expertise! For example, Microsoft Access contains modular tools
that you can assemble in various ways for useful purposes. For example,
forms & reports. But imagine how difficult it would be, to write the actual
form & report "engines" inside Microsoft Access! The simplicitly of the
tools themselves, belies the >complexity< of what they do "behind the
scenes". An average commercial programmer would be very hard pressed to
write the form & report "engines" inside Microsoft Access, IMO.

So the "modular tools" requirement >sounds< simple, but the execution of
that requirement would be very difficult.

More generally, in fact, they want a general package of functions that
will anticipate *all* requirements.
Sure. And I will win the lottery tomorrow.

They must be called as macros.
No professional Access developer uses macros. So now, you have a problem
with your boss! You can't tell him (outright) that macros are hopelessly
inappropriate for the task at hand. But also, you can't afford to accept the
task of doing what he wants, using macros: because you will not succeed! So
maybe "play dumb", tell him you're not sure how they work, & could he show
you how to use a macro to open a sreadsheet (for example). Chances are, he
won't have a clue. Then maybe you could turn the conversation to other
(proper) ways of doing this, eg. using VBA.

HTH,
TC

I can appreciate that a simple sequence of operations could be
conveniently strung together using a macro.

I had an idea that a macro along the lines "Load Spreadsheet to Table
with Validation Errors" might be useful. This would essentially be
like TransferSpreadsheet with a few extra controls and helps to better
identify errors.

However, my manager wants more "atomic" functions than this.
Validation functions of the type "IsAlpha" for example. He would like
to validate fields within each record before they're presented to the
table where these types and other constraints are defined. These are
to be controlled by macro.

As I see it, this puts the non-programmer in the position of using
macro loops.
I've had difficulty coding these myself: e.g., the end-of-table
condition can't be tested directly. I wrote a (VBA) function
[DoMacroOverTable, below] to accept a macro and a table and to apply
the macro for every record in the table. And, whilst I could make the
table the "current object" and use GotoRecord to change the "current
record", I can't see how (within the called macro) to refer to this
record or to fields within it (short of defining a form and fields for
it).

Any ideas?

Also, so far I have lost the argument on my objections to this
particular strategy. (Redundancy of many of these tests when
declarable as table properties and referential integrity constraints;
better application where applicable within select or update queries
than in macros). If anyone can supply their reasonings (either way!)
it may assist me.

Public Function DoMacroOverTable(sMacro As String, sTableName As
String)

DoCmd.SelectObject acTable, sTableName
DoCmd.GoToRecord , , acFirst

On Error GoTo DoMacroOverTableErr
Do Until False

DoCmd.GoToRecord acActiveDataObject, , acNext

DoCmd.RunMacro (sMacro)

Loop
Exit Function ' never reached!
DoMacroOverTableErr:
If Err = 2105 Then
' expected
Else
MsgBox "Unexpected Error: " & Err.Number & ":" & Err.Description,
vbCritical, "DoMacroOverTable"
Stop
Resume
End If
End Function

(End Message)

Nov 12 '05 #5

P: n/a
fp
What are you supposed to build this in?

Anyway, here is my suggestion.

I always try to comply with requests from those paying. While I also try to
convince the unwashed of the folly of their request I find that it is nearly
impossible to convince the guy with the money that he is wrong, so I work on
getting his vision complete as best as is possible.

If you really do not want to do the work then give your notice and leave.

In the end you have to be happy with your life and what you do. I always
give my best even under the worst of situations. You may want to consider
this a personal challenge and work on making at a roaring success.

Good luck.

--
******************************
Fred Parker
Lynn Consulting Group, L.L.C.
http://www.lynnconsultinggroup.com
******************************
Nov 12 '05 #6

P: n/a
RE/
More generally, in fact, they want a general package of functions that
will anticipate *all* requirements.


I'd dust off my resume and start looking.
--
PeteCresswell
Nov 12 '05 #7

P: n/a
da***@powell-au.net (David Powell) wrote in message news:<b5**************************@posting.google. com>...
Because my work area won't have an Access programmer next year, I've
been asked to rebuild their coded application as a set of modular
tools. The idea is that a non-programmer will be able to modify it as
requirements change.
More generally, in fact, they want a general package of functions that
will anticipate *all* requirements.

They must be called as macros.

I can appreciate that a simple sequence of operations could be
conveniently strung together using a macro.

I had an idea that a macro along the lines "Load Spreadsheet to Table
with Validation Errors" might be useful. This would essentially be
like TransferSpreadsheet with a few extra controls and helps to better
identify errors.

However, my manager wants more "atomic" functions than this.
Validation functions of the type "IsAlpha" for example. He would like
to validate fields within each record before they're presented to the
table where these types and other constraints are defined. These are
to be controlled by macro.

As I see it, this puts the non-programmer in the position of using
macro loops.
I've had difficulty coding these myself: e.g., the end-of-table
condition can't be tested directly. I wrote a (VBA) function
[DoMacroOverTable, below] to accept a macro and a table and to apply
the macro for every record in the table. And, whilst I could make the
table the "current object" and use GotoRecord to change the "current
record", I can't see how (within the called macro) to refer to this
record or to fields within it (short of defining a form and fields for
it).

Any ideas?

Also, so far I have lost the argument on my objections to this
particular strategy. (Redundancy of many of these tests when
declarable as table properties and referential integrity constraints;
better application where applicable within select or update queries
than in macros). If anyone can supply their reasonings (either way!)
it may assist me.

Public Function DoMacroOverTable(sMacro As String, sTableName As
String)

DoCmd.SelectObject acTable, sTableName
DoCmd.GoToRecord , , acFirst

On Error GoTo DoMacroOverTableErr
Do Until False

DoCmd.GoToRecord acActiveDataObject, , acNext

DoCmd.RunMacro (sMacro)

Loop
Exit Function ' never reached!
DoMacroOverTableErr:
If Err = 2105 Then
' expected
Else
MsgBox "Unexpected Error: " & Err.Number & ":" & Err.Description,
vbCritical, "DoMacroOverTable"
Stop
Resume
End If
End Function

(End Message)

Others have said it, but this sounds like a "Mission Impossible". It
may be challenging, but this sounds very much like a setup doomed not
only to failure, but disastrous failure. I'd make sure my CV was up
to snuff in this situation - it woudl appear you have a manager who
"knows just enough to be truly dangerous". Makes you wonder what else
is coming down the pike...

IMHO, setting up any program that can be user-modified as you describe
is a non-trivial exercise, and it is most definetly not going to
happen with macros in Access!
Nov 12 '05 #8

P: n/a
Ask him if he likes Alexander Pope... and then read him the lines...

"A *little* knowledge is a dangerous thing,
Drink *deep*, or taste not the Pierian spring..."

hopefully he'll get the message without getting insulted...
Nov 12 '05 #9

P: n/a

Thanks to all for these posts.

I hope I didn't caricature my manager's viewpoint.
Anticipating "all requirements" is of course impossible.
They weren't his exact words; but my interpetation of what it means when
no constraints are being placed on the data that may come in or on the
outputs that may be required.

Basically, I think the philosophical question is "How well can Access be
used in a non-'turnkey' fashion?" I wrote the "coded application". It
was rapidly sidelined by changing requirements (or the ascendancy of
other competing priorities from the customer group). And they can't
afford to retain a programmer.

My thought was that the new person should be trained to understand what
Access can do to assist a non-programmer in data design, loading,
validation, storage, and reporting. I'd write specific notes to
channel/augment what might be learned on a standard training course or
from a book. And maybe I could generalise and decouple a few of the
tools in my application for some tasks that I thought were likely to
recur. But the bulk of the work would be educational; the
"deliverables" text-based.

I've heard it said that, for the newcomer, Access is mis-named. It's
accessible for the coder/data analyst, but not for the non-programmer.
But given that the new person is smart, I think they could - given time
- perform most of the steps my program does in a sequential fashion.
The question is whether she can do all this reliably, with sufficient
control and with short enough turnaround, whilst adapting her
Access-assisted manual procedures to user changes. And it's conceivable
that she can .. she's younger and more retentive than I.

But I've no experience at such estimations - I need to couch even this
approach to the task with caveats!
So I take all your counsel on board with thanks.

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #10

P: n/a
David Powell <da***@powell-au.net> wrote in message news:<3f*********************@news.frii.net>...
Thanks to all for these posts.

I hope I didn't caricature my manager's viewpoint.
Anticipating "all requirements" is of course impossible.
They weren't his exact words; but my interpetation of what it means when
no constraints are being placed on the data that may come in or on the
outputs that may be required.

Basically, I think the philosophical question is "How well can Access be
used in a non-'turnkey' fashion?" I wrote the "coded application". It
was rapidly sidelined by changing requirements (or the ascendancy of
other competing priorities from the customer group). And they can't
afford to retain a programmer.

My thought was that the new person should be trained to understand what
Access can do to assist a non-programmer in data design, loading,
validation, storage, and reporting. I'd write specific notes to
channel/augment what might be learned on a standard training course or
from a book. And maybe I could generalise and decouple a few of the
tools in my application for some tasks that I thought were likely to
recur. But the bulk of the work would be educational; the
"deliverables" text-based.

I've heard it said that, for the newcomer, Access is mis-named. It's
accessible for the coder/data analyst, but not for the non-programmer.
But given that the new person is smart, I think they could - given time
- perform most of the steps my program does in a sequential fashion.
The question is whether she can do all this reliably, with sufficient
control and with short enough turnaround, whilst adapting her
Access-assisted manual procedures to user changes. And it's conceivable
that she can .. she's younger and more retentive than I.

But I've no experience at such estimations - I need to couch even this
approach to the task with caveats!
So I take all your counsel on board with thanks.

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


David,

That is an interesting scenario you describe. My main reservation
with it is that you (or perhaps "one" might be a better choice of
words) are asking users to become developers. Yes, a certain amount
of expertise in Access might be useful, but what frequently happens is
that the software deveoplment winds up taking an inordinate amount of
the user's time.

This is one of those things that is a real hidden cost in any
organisation, particularly if people don't have tools to do their
jobs. I am continually amazed by the creativity that users show - one
of the reasons I enjoy working on user-started projects is for the
"now why the h*** didn't I think of that?" factor. However, if you
are paying someone to be an accountant, shouldn't he or she be working
with the books, rather than developing software? Another real issue
that pops up is what happens when that user leaves - most often, the
knowledge and expertise goes out the door with them. Very few people
are disciplines enough to create usable "policies and procedures" type
documentation.

I apologize if I have misread your posting, and I do not mean any of
the above as criticism. It's just that I have seen many situations
such as you describe, and rarely have I seen anyone happy with the
results, FWIW.

JCN
Nov 12 '05 #11

P: n/a
RE/
It
was rapidly sidelined by changing requirements (or the ascendancy of
other competing priorities from the customer group). And they can't
afford to retain a programmer.


From here it sounds like somebody wants it both ways: changing an application to
meet new requirements, but not spending any money to do it.

Maybe I'm missing out on some new product but from what I've seen I don't think
software has reached that level of user-friendliness yet....
--
PeteCresswell
Nov 12 '05 #12

P: n/a
da***@powell-au.net (David Powell) wrote in
<3f*********************@news.frii.net>:
I've heard it said that, for the newcomer, Access is mis-named.
It's accessible for the coder/data analyst, but not for the
non-programmer.


Well, if it's a newcomer who doesn't understand the basics of what
a database is, how data structures work and what the tool does,
yes, it would be a problem.

But for someone who knows those things, Access is incredibly easy
to use, even for the person who is coming to it for the first time.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.