How to create an After Update macro in FE. Tables are linked. BE is different.

S

SachAccess

Active member
Local time
Today, 22:26
Joined
Nov 22, 2021
Messages
391
Hi,
I am trying to create a log table. Either in my BE for FE.
I have somewhat working code in my BE. There are few issues in it but I will come back to it later.

My idea is somewhat like below.
I have a BE and a FE.
All the tables are linked from BE to FE.
Let us see there are 10 tables in my BE.
All are linked in my FE.
TblSummary is one of the table.
BookingDate is one of the field in TblSummary.
Am a user, I changed one record from BookingDate.
Changed to 28-Oct-2022 from 27-Oct-2022.
I am writing a macro + VBA code which will enter details of this update in TblLog
So TblLog has serial number, date, user name, table name, field value, previous value and current value.

In ideal working condition, code will update TblLog as below
serial number = 1, date = 28-10-2022, user name = myname, table name = TblSummary, field value = BookingDate, previous value = 28-Oct-2022 and current value = 27-Oct-2022.

My code is almost working in BE. But it is only working in BE. If I do any change in FE then this code is not getting triggered.
I have assigned an After Update macro in BE for TblSummary.
In my FE, assigning macro is disabled for linked tables, at least that is my assumption.
If we cannot assign macro to linked table in FE then how do resolve my issue.
What is alternate way to assign macro for the linked tables.
Or how do I trigger my After Update macro in BE based on the changes done in FE.
Can anyone please help me in this.

Please see below code just as a reference. Have changed few names to dummy names while posting.
Code is still in progress.

Code: 
Option Compare Database
Option Explicit
Public NTLoginID As String
Public Function VBAFunction(OldValue As String, NewValue As String) As String
        Debug.Print "Old: " & Chr(34) & OldValue & Chr(34);
        Debug.Print vbTab;
        Debug.Print "New: " & Chr(34) & NewValue & Chr(34)
        
        Dim TblLog As DAO.Recordset
        Dim MyMax As Long
        
        Set TblLog = CurrentDb.OpenRecordset("SELECT * FROM [Tbl_Log]")
        TblLog.AddNew
        On Error Resume Next
            MyMax = DMax("Serial Number", "Tbl_Log") + 1
        On Error GoTo 0
        
        If MyMax = 0 Then MyMax = 1
        TblLog![Serial Number] = MyMax
        TblLog![User Name] = Trim(UCase(Environ("UserName")))
        TblLog![Actioned Date] = Now
        TblLog![Table Name] = "Tbl_Summary"
        TblLog![Field Name] = "Booked"
        TblLog![Previous Value] = OldValue
        TblLog![Current Value] = NewValue
        
        TblLog.Update
        TblLog.Close
        Set TblLog = Nothing
        'DoCmd.Close
End Function
Function WelcomeCode()
    NTLoginID = Trim(UCase(Environ("UserName")))
    Call MakeEntryInLogTable
End Function
 
Macros must be defined in the database where the table is located so that means they go in the BE. WHY? Because they are run by the database engine which is ACE NOT VBA.
 
Hi,

I tried searching various website for the solution. I was not able to find it.
Am I correct in thinking that I cannot write After Update macro in BE based on the changes done in FE.

I am trying to create a log for the changes done in tables.
Changes would be done from FE.
Tables are linked from BE.
How do I solve this problem.
Can anyone please help me understand this.
 
Thanks for the help @arnelgp, is there a way that I can achieve this. Is there any workaround.
I am kind of stuck at this point. I have a macro which is working in BE but I want to work it with FE.

For FE, After Updated in disabled for Linked Tables.
How can I proceed in this scenario.
Could you please help if possible.
Have a nice day ahead. :)

PS - I have not tried it yet. Just asking, can I link a VBA code to a Form button.
As soon as any changes are done through form, this VBA code will get triggered.
Is it possible. Will it work that way.
 
Do you have a sample database that you can post? Doesn't have to be real data, but sufficient to highlight the issue and to invoke you data macro.
 
Hi @jdraw thanks for the help. Apologies am not allowed to upload anything. Upload is blocked.
Have a nice day ahead. :)
 
Am I correct in thinking that I cannot write After Update macro in BE based on the changes done in FE.
Click to expand...

That is not a true statement. I have no idea what you are trying to do in the macro, you will need to post it. Whatever you are trying to do CANNOT refer to objects in the FE. The Data macros always deal with data in tables and only use SQL functions.
 
Sach,
I have mocked up a Frontend and Backend (O365 64 bit). There is a data macro on TblSummary that will create a record in TblLog when the BookingDate in TblSummary is changed. You can see the data macro when looking at TblSummary and selecting AfterUpdate in data macros in the BE.
I have added a few simple records.
The FE and BE databases are in the zip. Also is a txt file that overviews what exists and has been done. There is also a gif file that quickly jumps through various parts of the 2 databases and some sample running of the query and display of tblSummary and tblLog records..
It is quick and dirty, but I think it shows you can use data macro with FE and BE. And you can show records on TblLog. It should be enough to get familiar with data macro to create a record when a field value changes in a specified table.
It's been 5 years since I did any dabbling in data macros. This is to demo the data macro--the databases, form, query and module have no other intent.
Good luck.

Also: I just want to update this to say that the "help" files around Data Macros and samples are few and far between. The macro editor has been the focus of many hostile comments --all well deserved. I found it best too have a copy of the table structures in Notepad++ or some other window because once you're in the macro editor it's impossible to see your table designs in Access.
 

Attachments

Last edited:
jdraw said:
Sach,
I have mocked up a Frontend and Backend (O365 64 bit). There is a data macro on TblSummary that will create a record in TblLog when the BookingDate in TblSummary is changed. You can see the data macro when looking at TblSummary and selecting AfterUpdate in data macros in the BE.
I have added a few simple records.
The FE and BE databases are in the zip. Also is a txt file that overviews what exists and has been done. There is also a gif file that quickly jumps through various parts of the 2 databases and some sample running of the query and display of tblSummary and tblLog records..
It is quick and dirty, but I think it shows you can use data macro with FE and BE. And you can show records on TblLog. It should be enough to get familiar with data macro to create a record when a field value changes in a specified table.
It's been 5 years since I did any dabbling in data macros. This is to demo the data macro--the databases, form, query and module have no other intent.
Good luck.

Also: I just want to update this to say that the "help" files around Data Macros and samples are few and far between. The macro editor has been the focus of many hostile comments --all well deserved. I found it best too have a copy of the table structures in Notepad++ or some other window because once you're in the macro editor it's impossible to see your table designs in Access.
Click to expand...
Hi @jdraw thanks a lot for the help and help files. Please give me some time to revert. I will download it on personal computer and study and will revert with updates. Not allowed to download on business machine. Have a nice day ahead. :)
 
Pat Hartman said:
That is not a true statement. I have no idea what you are trying to do in the macro, you will need to post it. Whatever you are trying to do CANNOT refer to objects in the FE. The Data macros always deal with data in tables and only use SQL functions.
Click to expand...
Hi @Pat Hartman thanks a lot for the help. Please give me some time to revert. Will post in detail what am trying to achieve.
Have a nice day ahead. :)
 
Hi @Pat Hartman please see below details if you get time. Thanks.

I have two MS Access files
One is Front End and another is Back End
All the tables from the BE are linked in FE.
FE does not have any local table.
All my queries, forms and reports are maintained in FE.
User will access the FE, will do some changes in the tables by using forms.
These changes are correctly reflected in BE tables.
No issues till this point.

Please see below example for your reference.
FE and BE has a table called Tbl_Names.
Tbl_Names has a field Moderator Names.
We have a Form in FE.
With this Form we can edit the existing Moderator Names from Tbl_Names.
Suppose a user changes a name from to ‘Pat’ to ‘Super Moderator Pat’ by using the Form in FE.
This change is reflected correctly in FE and BE as well.

Now I am trying to create log table, ideally this table should be in the FE only.
This log table should provide me below details.
Username, table name = Tbl_Names, Field Name = Moderator Names, Old Value = Pat, New Value = ‘Super Moderator Pat’
I am able to perform this Macro + VBA in BE. However am not able to do the same in FE.
FE does not allow After Update macro to linked tables (my guess)
And changes done in the FE, thought reflected in BE are not captured by Macro written in BE.
So am trying to write a Macro (plus VBA) in FE which will create a log for me with linked tables.

Please let me know if you need more details from me.
Uploading is not allowed but please let me know if you need any details about the tables.
Thanks.
 
put all on same folder and Relink the FE to the BE.
open table tbl_Names and edit the Moderator.
see tbl_Log for the result.

see the AfterUpdate macro on Design view of tbl_Names.
 

Attachments

Hi @arnelgp thanks for the help. Please give me some time to revert.
Have a nice day ahead. :)

PS - I just realized, my FE and BE are in the same folder. Still, I will re-check.
 
"Now I am trying to create log table, ideally this table should be in the FE only."

Tables should be in the BE. Why do you want the log in FE?
 
jdraw said:
"Now I am trying to create log table, ideally this table should be in the FE only."

Tables should be in the BE. Why do you want the log in FE?
Click to expand...
Thanks for the help. I agree, my mistake, it should be in BE only. Will be linked to FE (for viewing purpose only).
 
You must log in or register to reply here.

Similar threads

L
Consuming an API to retrieve data and store it in a Microsoft Access table.
Replies
3
Views
234
lbcurrier
L
K
How to link to an Excel named range using vba
Replies
9
Views
540
Isaac
Isaac
B
VBA problem relating to SQL linked table
Replies
6
Views
923
CJ_London
C
S
How to create calculated field in MS Access table
Replies
11
Views
1,401
SachAccess
S
P
Solved Table is Not Visible in Target DB after DoCmd.TransferDatabase
Replies
6
Views
1,123
Pac-Man
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom