SetField Macro

[kamran60]

Is there a way I can set the value in SetField data macro to a fixed field in an existing form?

For example,

SetField
Name= User
Value= Forms!LoginLog!User

But this gives me an error saying that 'Forms!LoginLog!User' identifier could not be found?

 

[bob fitz]

Change the name of the control called "User". That is a reserved word in Access. See: http://support.microsoft.com/kb/286335

Is the form called "LoginLog" open when the macro runs?

 

[kamran60]

I changed the User to something else but unfortunately that did not work. The form does not open when the macro runs. Actually, this data macro is inside my AutoExec macro.

 

[bob fitz]

I changed the User to something else but unfortunately that did not work. The form does not open when the macro runs. Actually, this data macro is inside my AutoExec macro.

You can't set reference to a control on form that is not even open. It has no value.

 

[kamran60]

This is my new code;

OpenForm
Name= LoginLog
Data Mode= Read Only
Windows= Dialog

CreateRecord in tblLoginLog

SetField
Name= CurrentUser
Value= Forms!LoginLog!CurrentUser


Unfortunately I am getting the same error that "Forms!LoginLog!CurrentUser" identifier could not be found. Please can you elaborate how can I use the control value when the form is open? Many Thanks

 

[AccessJunkie]

Hi,

Is there a way I can set the value in SetField data macro to a fixed field in an existing form?

Not directly because this goes against the fundamental nature of data macros in Access 2010 and Access 2013.

Data macros are designed to attach to table events and are supposed to fire whenever data is inserted, updated, or deleted. They are supposed to fire no matter how that data is added/changed/deleted. For example, through UI interaction, through changing the data in table datasheets, query datasheets, through other macros, through VBA code, etc.

By that very nature, there is a very deliberate separation from the data layer and the UI layer. The data layer needs to be able to successfully run all actions and arguments with no knowledge of the UI layer in much the same way as SQL Triggers within SQL Server.

Let me explain with your example. You want to grab a value from a form and use that in a data macro to save a record using SetField. What happens if the form is not open? You might think, well I'll just add a macro or VBA code to make sure the form is open. So what are you going to do in the following scenarios?
- You edit the data directly in a table datasheet
- You edit the data directly in a query datasheet based on the table
- You link to that table from a different database and edit the value
- If this is a published web database, the data is not in SharePoint lists. What if you edit the data using a SharePoint form and Access isn't even opened?

See my point?

Data macros and the logic contained within them need to be independent.

In order to do your scenario, you need to *push down* any values you want into a named data macro (it cannot be a table event data macro) using parameters. You create a named data macro, define the parameters, and use the parameters in the SetField arguments of the CreateRecord action. You then need to create a UI macro on the form that grabs the value of the form control and "pushes" it into the parameter using the RunDataMacro action.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out (coming soon)
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

 

[kamran60]

Thank you alot AccessJunkie. Got it to work!