SachAccess
Active member
- Local time
- Today, 23:06
- Joined
- Nov 22, 2021
- Messages
- 389
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.
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
