Set Temp Var from Data Macro

[RMittelman]

Is there any way to set a TempVar from a data macro or Named macro? I want to store settings for my application, but I don't want a settings form. Currently, I use the Settings table and a bound Settings Form to show the settings from the table. Each setting is then assigned to a TempVar. I'm willing to keep the Settings table, but only want a single form, so don't want to include the Settings form.

I thought of using a data macro when a Settings table record changes or a new record is added. If the data macro or named macro could set TempVars or run code, this would work for me. But these options are disabled in the macro designer. The macro designer will only allow setting LocalVars or ReturnVars. Most of my code relies on TempVars, which are retrieved from the Settings table at start-up time.



A data macro would be perfect. I could click a button on my main form which would open the Settings table, and when I changed or added a setting into the table, the associated TempVar could be updated. This allows settings to be changed, and the changes would propagate between sessions since the table would be updated.

I can't seem to figure out how to update the TempVars if my GetSettings function can't be run from data or named macros.

Any suggestions on how to do this? Thanks...

[davegri]

I don't understand why you can't run code from a named macro...

[Welshgasman]

There is a SetTempVar macro action?

[orange]

Dave,

Thanks for the nice example.
I used it to try a mock up as follows:

Created 2 functions
-fncMakeTempVars
-GetSettings

Created 1 Named Macro (based on DaveGri sample)
-macTempVar

RunCode Function Name fncMakeTempVars

Used an existing table _ApplicationObjectList


The only record in the table is:

Id	ObjType	ObjName	ObjLocation	ObjCleanLocation	ObjModLocation	sysDateAdded
1	2	DBObjects	C:\Users\JP\AppData\Local\DbgSaveRaw\Form_DBObject s.txt	C:\Users\JP\AppData\Local\DbgClean\Form_DBObjects. txt	C:\Users\JP\AppData\Local\DbgWithDebug\Form_DBObje cts.txt	07-Sep-22 7:18:14 PM

Here are the functions:

  Option Compare Database
Option Explicit

' ----------------------------------------------------------------
' Procedure Name: fncMakeTempVars
' Purpose: Mockup to get values from a table and assign these to Tempvars
' Procedure Kind: Function
' Procedure Access: Public
' Author: Jack
'(Based on Davegri sample https://www.accessforums.net/showthread.php?t=86612&p=500258#post500258)
' Date: 09-Sep-22
' ----------------------------------------------------------------
Function fncMakeTempVars()
          Dim tmpv1 As TempVars  'dim 2 tempvars
          Dim tmpv2 As TempVars
10        GetSettings            'run a function to get some values
20        TempVars!tmpv1 = Split(GetSettings, "|")(0) 'assign retrieved values to tempvars
30        TempVars!tmpv2 = Split(GetSettings, "|")(1)
40        MsgBox "TempVar1: " & TempVars!tmpv1 & vbCrLf & "TempVar2: " & TempVars!tmpv2
End Function

' ----------------------------------------------------------------
' Procedure Name: GetSettings
' Purpose: To retrieve some values from a table
' Procedure Kind: Function
' Procedure Access: Public
' Author: Jack
' Date: 09-Sep-22
' ----------------------------------------------------------------
Function GetSettings()
          Dim a As String, b As String
10        a = DLookup("ObjLocation", "_ApplicationObjectList")
20        b = DLookup("ObjCleanLocation", "_ApplicationObjectList")
30        GetSettings = a & "|" & b
End Function

The result of running the macro:



May not be what OP needs, but should provide some insight.

[davegri]

@Orange, Nice expansion on basic idea. OP also mentioned updating the table if his code reset any of the extracted tempvar values. That could be done with update queries to reset table field values with the new tempvar values.

[RMittelman]

I don't understand why you can't run code from a named macro...

From the pictures, it looks like you are using the standard macro designer, which does indeed let you run code. The macro designer which opens when you have a table open and activate the Table ribbon is different. Either the "After" events macro buttons or the "Named Macro" button don't offer Set TempVar action, only LocalVar or ReturnVar options. I created a "RunCode" macro in the normal designer, which works fine. But that can't be called from any of the data or named macros. In your example, the macro you created is, I guess, a named macro, but it is not the same as the "Named Macro" available from the Table ribbon.

[RMittelman]

Dave,

Thanks for the nice example.
I used it to try a mock up as follows:

Created 2 functions
-fncMakeTempVars
-GetSettings

Created 1 Named Macro (based on DaveGri sample)
-macTempVar

RunCode Function Name fncMakeTempVars

Used an existing table _ApplicationObjectList


The only record in the table is:

Id	ObjType	ObjName	ObjLocation	ObjCleanLocation	ObjModLocation	sysDateAdded
1	2	DBObjects	C:\Users\JP\AppData\Local\DbgSaveRaw\Form_DBObject s.txt	C:\Users\JP\AppData\Local\DbgClean\Form_DBObjects. txt	C:\Users\JP\AppData\Local\DbgWithDebug\Form_DBObje cts.txt	07-Sep-22 7:18:14 PM

Here are the functions:

  Option Compare Database
Option Explicit

' ----------------------------------------------------------------
' Procedure Name: fncMakeTempVars
' Purpose: Mockup to get values from a table and assign these to Tempvars
' Procedure Kind: Function
' Procedure Access: Public
' Author: Jack
'(Based on Davegri sample https://www.accessforums.net/showthread.php?t=86612&p=500258#post500258)
' Date: 09-Sep-22
' ----------------------------------------------------------------
Function fncMakeTempVars()
          Dim tmpv1 As TempVars  'dim 2 tempvars
          Dim tmpv2 As TempVars
10        GetSettings            'run a function to get some values
20        TempVars!tmpv1 = Split(GetSettings, "|")(0) 'assign retrieved values to tempvars
30        TempVars!tmpv2 = Split(GetSettings, "|")(1)
40        MsgBox "TempVar1: " & TempVars!tmpv1 & vbCrLf & "TempVar2: " & TempVars!tmpv2
End Function

' ----------------------------------------------------------------
' Procedure Name: GetSettings
' Purpose: To retrieve some values from a table
' Procedure Kind: Function
' Procedure Access: Public
' Author: Jack
' Date: 09-Sep-22
' ----------------------------------------------------------------
Function GetSettings()
          Dim a As String, b As String
10        a = DLookup("ObjLocation", "_ApplicationObjectList")
20        b = DLookup("ObjCleanLocation", "_ApplicationObjectList")
30        GetSettings = a & "|" & b
End Function

The result of running the macro:



May not be what OP needs, but should provide some insight.

Thanks Orange, but as I mentioned to davegri, this is not the "Named" macro as it appears in the Table ribbon when you have a table open. That one is severly limited in what you can do. Makes sense, as I believe data macros were created to mimic triggers in a true relational database, and wouldn't be expected to do what full macros can do.

[RMittelman]

@Orange, Nice expansion on basic idea. OP also mentioned updating the table if his code reset any of the extracted tempvar values. That could be done with update queries to reset table field values with the new tempvar values.

Hi davegri,
Actually you have it backwards. What I was asking about was running code after the user opens the table and makes inserts or updates to any of the settings. My GetSettings function clears TempVars then re-creates them based on table data. I was hoping when I change the table data, it would trigger resetting the TempVars, which data macros don't seem to be able to do.

On all my other databases, this works fine, because I open up a pop-up modal continuous form which is bound to the Settings table. When the form closes, my code reruns the GetSettings function.

For this application, I want all code to be in a single form, so if needed I can simply copy the form into other databases and use them there.

Since this particular database only has 4 settings, I simplified things and removed the Settings table and form entirely, and set the 4 TempVars in my GetSettings function.

I've posted my sample application in this thread:
https://www.accessforums.net/showthr...307#post500307

Scroll down to the bottom and take the second file attached, "AccessCodeCompare2.zip".

Thanks to all for your help!

[orange]

RMittelman,

I did an audit log demo database with data macros and named data macro in this thread/post.
Again, it may not be what you need, but may offer some insight on use of a named Data macro.
That is the only dabbling I have done with named and data macros.