MS Access 2016 - Call Public Function into a Data Macro

Anonymous
2025-01-02T12:10:35+00:00

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?

Microsoft 365 and Office | Access | For business | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments
{count} votes
Answer accepted by question author
  1. Anonymous
    2025-01-02T12:57:02+00:00

    Hi Paula,

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

    Value = MyEnviron("UserName")
    
    1 person found this answer helpful.
    0 comments No comments

9 additional answers

Sort by: Most helpful
  1. George Hepworth 22,220 Reputation points Volunteer Moderator
    2025-01-02T12:41:27+00:00

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

    0 comments No comments
  2. George Hepworth 22,220 Reputation points Volunteer Moderator
    2025-01-02T12:58:08+00:00

    See my reply illustrating how it's done.

    0 comments No comments
  3. Anonymous
    2025-01-02T13:06:58+00:00

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

    0 comments No comments
  4. George Hepworth 22,220 Reputation points Volunteer Moderator
    2025-01-02T13:17:24+00:00

    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?

    0 comments No comments