Solved

Aссess 2010 database macro

Posted on 2015-02-11
7
149 Views
Last Modified: 2016-02-10
I need to create a macro, that would start after a new record is added to a table. How do I do it?
Also, after the macro is executed, all records from the table should be deleted. Is that possible?
0
Comment
Question by:German Mikulski
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 85
ID: 40602997
2010 includes Data Macros, which can be set to fire AfterInsert, so you could create one that performs whatever task it is you need. I'm not sure you can delete all records from the table in that AfterInsert macro, however.

You could do this on a Form, of course. Using a combination of Form events, you could determine when a New Record is added, and then run your code, and then delete all records in the table.
0
 

Author Comment

by:German Mikulski
ID: 40603181
I have found an After Insert macro in Table Tools, but the list of offered actions there is very limited and does not contain "OpenQuery" - the one which is needed.
In terms of deleting all records, form option will not work, as data is added from a web form directly into the table.
0
 
LVL 85
ID: 40605379
Is this a Web Database?

If so, then you're quite limited in what you can do. Access 2013 moved to Web Apps, which have more functionality, but I don't know if you'll ever be able to do what you want in a Web Database or Web App. Web Databases/Apps are fine for simple CRUD (Create-Read-Update-Delete) actions, but when you get into more complex needs, you often find limitations which prevent you from doing what you want.

You can create a "hybrid" app, where the data resides on Sharepoint/Azure, but your application is a standard, desktop Access database.

If not, then you might be able to create another Macro, and use RunDataMacro action in the AfterInsert table macro.
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:German Mikulski
ID: 40605494
Not, it is just an ordinary .accbd database. I created a separate macro, that would run the needed query. But RunDataMacro shows only data macros, so my macro wasn't displayed. What else can I do?
0
 
LVL 85
ID: 40605550
I'm not sure. I don't really use macros, and haven't for quite some time. From my undestanding, however, Table-based macros (i.e. DataMacros) can only execute other DataMacros. Here's what MSDN has to say about DataMacros:
Data macros are a new feature of Access 2010 which enable you to add logic to events that occur in tables, such as adding, updating, or deleting data. They are similar to "triggers" in Microsoft SQL Server. This article shows you how to create and debug data macros.
From Here: https://support.office.microsoft.com/en-in/article/Create-a-data-macro-b1b94bca-4f17-47ad-a66d-f296ef834200

In other words - I don't think you'll be able to do what you're after, unless you can add code to the web page to handle those actions.
0
 

Author Comment

by:German Mikulski
ID: 40605962
But isn't it possible to write sone kind of vba code that woyld trigger these actions?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40625396
If you're doing this from a Form, then you could very likely use VBA to manage this - but that's the only other option if you cannot find a solution using Data Macros (and I don't think you will).

The Form's After Update event, and Before Update event, could be used to do this perhaps. Here's what I'd do:

In the General Declarations section of the form, add this variable:

Dim fIsNew As Boolean

In the Form's Current Event, add this:

fIsNew = Me.NewRecord

Now in the Form's AfterUpdate event, check the value of fIsNew, and take action based on that:

If fIsNew Then
  ' run your macros or vba code

  fIsNew = False
End If
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question