• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Can a confirmatory dialogue be launched from Data Macros in Access 2010

Can you launch a dialogue to seek confirmation of whether to allow an edit to go ahead from a data macro? E.g. I create a before change data macro and I wish to get the user to confirm they really want this edit to go ahead.  My guess is no, but am I wrong?

Other than putting a before update event handler in every bit of the UI where it is used (which would be a pain) is there another workaround?

Martin
0
mnewmana
Asked:
mnewmana
  • 7
  • 6
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Perhaps, but this will probably involve the use of "TempVars" and Decision (If-Then-Else) logic.
Both of these things are somewhat awkward to do in Macros.

To do this in VBA code is simple:

Dim bytAnswer As Byte
    If MsgBox("Allow Edit?", vbQuestion + vbYesNo) = vbNo Then
        MsgBox "Edit Cancelled"
        'Your code to prevent/cancel the edit (Cancel=True if needed...)
        Exit Sub
    End If
   
    MsgBox "Edit Proceeding"
    'Your Code to allow the edit

Does this help?

JeffCoachman
0
 
mnewmanaAuthor Commented:
Well if I can do nothing else I will do it in the before update event of all the forms that reference the field I am interested in using VBA but using a data macro means it is a property of the table and not of the GUI which is much more sensible.  

What I couldn't see was any way in a macro to launch a message box.  And it kind of makes sense that you shouldn't be able to launch a GUI object from a data macro as for linked tables any data macro has to be in the linked, not the linking, accdb and that ODBC linked databases respect data macros so it is hard to see how you could launch a GUI object from data macros in that situation.

I seem to recall in the dim and distant past in Access 2003 or maybe even Access 97 I did something exceptionally clever with DAO that achieves what I want to but all the references to this kind of thing on the web say what I want to do was impossible before Access 2010 (but doesn’t actually say I can do what I want in Access 2010).  And I am probably not as bright as I was a decade ago so can’t work it out again form scratch!
0
 
Jeffrey CoachmanMIS LiasonCommented:
Well, I Huffed and I Puffed, and came up with this.

It seem to do what you are asking
untitled.JPG
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Jeffrey CoachmanMIS LiasonCommented:
It's on the button on the report.
You must open the report in "Report View" specifically in order to activate the button
Database241.accdb
0
 
mnewmanaAuthor Commented:
AH - thanks:-)

I am doing this on a remote machine and having toouble reaching it at the moment so can't immediately check...
0
 
mnewmanaAuthor Commented:
As far as I can see there is only a very restricted range of actions available in a Data Macro(just 10)  nothing like the range available (somewhere around 100)  in an "ordinary" macro and Message box is just not there for Data Macros.
0
 
Jeffrey CoachmanMIS LiasonCommented:
It is not clear what you mean by "Data Macro" and "Ordinary Macro".

But in a macro you can limit or expand the number of "Actions" available by clicking the "Show All Actions" button in the macro designer screen.
0
 
mnewmanaAuthor Commented:
As I understand it (and as defined say here http://blogs.office.com/b/microsoft-access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx, here http://blogs.office.com/b/microsoft-access/archive/2010/10/15/power-tip-leveraging-data-macros-in-access-2010.aspx and here http://msdn.microsoft.com/en-us/library/ff973807.aspx) data macros are ones created from the "Create Data Macro" button in the Table Tools|Design ribbon as opposed to an "ordinary" one built using the Macro button in the Create ribbon.  The first link I reference above sort of (but not quite) confirms I can't do what I want to do - and to be honest considering what else before event data macros can do I don’t think I can see how it could be made to work but I thought it was worth checking.   Data Macro Before Event Actions
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK, that macro option is new in 2010.

Also I am not 100% sure these new "Table events" map exactly to the form events.

You can make a "Ordinary Macro" and do this on these same events on the form, (The way this has always been done, if using macros).

Why the need for a "Data Macro" specifically?
0
 
mnewmanaAuthor Commented:
With a data macro you can have event handlers for tables so whenever a piece of data is changed the event is fired regardless of what form makes the change (or indeed a remote ODBC connection).  So rather than writing loads of  on change event handlers for loads of forms you can write a single data macro once for a databse tablewhich maynot be "addressed" just by loads of forms but even loads of different applications..

Like a triggerin SQLServer or Oracle or MYSQL
0
 
Jeffrey CoachmanMIS LiasonCommented:
<event handlers for tables so whenever a piece of data is changed the event is fired regardless of what form makes the change >

Makes sense...
;-)

I never used SQL all that much, but I do know that (Table) "Triggers" were something that people were always wanting in Access...

Jeff
0
 
mnewmanaAuthor Commented:
Thanks for your thoughts on this.  I am going to close this question now as I am more and more convinced I can't quite do what I wanted to - and I can see why I can't.
0
 
mnewmanaAuthor Commented:
I am giving partially's here as I think the answer was "not psosible" and I and the expert were actually at cross purposes for a a while but the assistance given was nothing but well intentioned and helpful.  Ny question, on reflection, was impossible to answer.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now