Using Triggers in Access 2016 (1 Viewer)

[Tezal]

Hi there,

I am trying out Access (2016) Triggers for the first time. Our email business rules agent system inserts records into Access tables depending on email content, and I am trying to get these inserts trigger a vba function within a module in the database (with parameters).

I have used the "After Insert" data macro, and this is working fine. But while it works great when inserting records manually in the table, happening when I leave the row to another row, the trigger is not fired when an outside application inserts a record. Probably since there is no "After" presence, and therefore no "After Insert" process that would fire the trigger. There is no "On Save" trigger or something like it.

Anyone have any idea how to tackle this?

Thanks!

Tezal

 

[theDBguy]

Hi. Welcome to AWF! My guess is the trigger is firing but if the VBA routine is not in the BE, maybe the trigger is erroring out.

 

[Tezal]

Thanks for your response, but eehhhh, what is BE ?

 

[June7]

FE = Frontend
BE = Backend

Doesn't matter where VBA is located, neither DataMacros nor form Update and Insert events will trigger with programmatic input of records.

 

[Tezal]

Thanks June7, thats very clear. Too bad, I'll have to look elsewhere for a solution.

 

[jdraw]

Tezal,

Are you seeing any records in table USysApplicationLog?

 

[CJ_London]

and I am trying to get these inserts trigger a vba function within a module in the database (with parameters).

Agree with June's comments but perhaps the app that is inserting the records can call a function or macro? All depends on the nature of the insert (single or multiple records) and what the macro is required to do and whether the inserting app can open access

perhaps the app can open access using a commandline switch - see this link

https://www.utteraccess.com/wiki/Command-Line_Switches

Otherwise, consider using sql server express

 

[Tezal]

Hi, thanks for responding, I was a few days off.

The USysApplicationLog table creates the following error when trying:
"The function 'TestMe' is not valid for expressions used in data macros."
Context SetLocalVar Module1, TestMe()

I used a simple test function to be sure it is not due to the function:

Public Function TestMe() As String
Dim Test1 As String
Test1 = "tested"
End Function

I wish I could use MS SQL, but I can't. Th external application can execute vbs, and in the past I used this to execute a standalone vbscript file that in turn executed the vba function within Access. However, env things have changed, and now I really want to be able to call the function directly, preferrably through triggers.

 

[CJ_London]

not sure how you are creating your data macro, but there are limitations to what you can do - and executing a vba function is not something you can do. So far as I can see, you can't even run a named macro (which in any event still can't execute a vba function).

Consider using sql server express as a BE instead

 

[Tezal]

When triggered by manually doing an insert in a datasheet, a data macro "after insert" executes a vba function just fine, you can even bring along parameters.

The only issue here is you don't seem to be able to do it from an external application, programmatically, like June7 said.

Again, I can't use MS SQL (incl. Express).

 

[CJ_London]

I must admit I don't use macros but you say

When triggered by manually doing an insert in a datasheet, a data macro "after insert" executes a vba function just fine, you can even bring along parameters.

The usual way a macro executes a vba function is to use the action 'runcode'.

I don't see runcode as an action available for data macro's - so how are you running it manually?

 

[June7]

@CJ_London, some actions such as SetLocalVar have expression argument that can call a VBA function. If action can call a function as part of conditional.

However, only time I ever used a macro was for AutoKeys to disable Ctrl+p.

 

[CJ_London]

fair enough.

 

[theDBguy]

Hi, thanks for responding, I was a few days off.

The USysApplicationLog table creates the following error when trying:
"The function 'TestMe' is not valid for expressions used in data macros."
Context SetLocalVar Module1, TestMe()

I used a simple test function to be sure it is not due to the function:

Public Function TestMe() As String
Dim Test1 As String
Test1 = "tested"
End Function

I wish I could use MS SQL, but I can't. Th external application can execute vbs, and in the past I used this to execute a standalone vbscript file that in turn executed the vba function within Access. However, env things have changed, and now I really want to be able to call the function directly, preferrably through triggers.

Hi All. Sorry for the delay, I just got back from travel. So, as I was saying, this confirms that the data macro is firing but getting an error, correct? Which means, the external program was able to trigger the data macro, right? If so, you haven't clarified where the VBA function is located.

 

[June7]

Okay, I have to retract earlier statement. Just did another test of programmatically adding record (CurrentDb.Execute) and I do find DataMacro is triggered. Don't know why it wasn't working before.

No error, function called in DataMacro executes.

Would be nice to see DataMacro code. Exactly what action is used?

Confirmed form events do not trigger with programmatic record insert.

 

[Tezal]

Hi,

this is what I used:

Data Macro "After Insert"

Command > SetLocalVar , expression: ="=TestMe()"
(I read a post saying it is nececssary to use 2 "=", including the one already there. tried both though)

Function in Module
Module1, Public Function TestMe()


@June7, I can imagine a vba sql insert command working, but that is still not an external application, right?

 

[June7]

No external app involved in my testing.

 

[jdraw]

Tezal,

Here is a link to info on using data macros for audit logging. This was part of my "dabbling" with data macros. There is a sample database that you could review to see if any of it is relevant to your situation. Specific info in post 14 thru 21.
Good luck.

 

[CJ_London]

good link from jdraw. Makes me think that the problem might not be with the macro not being triggered but that the macro cannot actually do what is asked of it (i.e. display the message) since Access is not actually open. Perhaps change the macro to do something else such as insert a record?

 

[Tezal]

Thanks.

The content of the vba function triggered is not the problem, it works fine when triggered through manual inserting a record. Only when trying to do this with an external application the error occurs, even when a test function contains no code at all.
I'm starting to think it has to do with permissions (window user/uac level) or such.

The ultimate thing I am trying to do here is not even the insert itself, that was just to bridge and trigger the trigger
What I am really trying to do is to get an external application directly trigger a vba function in the Access database, while passing two parameters.
This function does a lot: copy a table, creating and populating an excel file, and email it.