MS Access 2016 - Call Public Function into a Data Macro

Question

I have a data macro for an audit table which works great except for tracking the system user. In VBA I can use Eviron("UserName") but I need to do this via macro. Here is the macro:

If Updated("[Oasis_Barcode]") Then

Create a Record in Audit

Set Field

Name Audit.TableName

Value = “[Files]”

Set Field

Name Audit.RecordID

Value = [Files].[ID]

Set Field

Name Audit.ChangeBy

Value = ??? Cannot use Environ

Set Field

Name Audit.FieldName

Value = “[Oasis_Barcode]”

Set Field

Name Audit.OldValue

Value = [Old].[Oasis_Barcode]

Set Field

Name Audit.NewValue

Value = [Files].[Oasis_Barcode]

Set Field

Name Audit.ChangeDate

Value = Now()

End If

i have created a public function as a module:

Public Function MyEnviron(username As String) As String

MyEnviron = VBA.Environ(username)

End Function

but I am not that familiar with using functions and dont know how to call this into a macro - can anyone give me any pointers?

Answer 1

Hi Paula,

Try to invoke the VBA function directly within your Data Macro like so:

Value = MyEnviron("UserName")

Answer 2

Can you show us that part of the DM?

I know it works in the Northwind DM illustrated above, although we're updating a field in the current table, not writing a record to a different table.

Did you check the Application Log Table?

Answer 3

Unfortunately doesnt work for either suggestions. Nothing recorded in audit table and no error message either

Answer 4

See my reply illustrating how it's done.

Answer 5

Here's a way you should be able to do it. This is a Data Macro from Northwind Developers version.