CODEWITHSUNDEEP
sqlms-accessms-access-2013
Ponzaro
Ponzaro

Reputation: 149

Access - Reference to a form element in a data macro

I have a simple data macro in Access 2013. It is a Before Change data macro.

It is a SetField macro. In the "Value" field I need to reference to the value that it is in a textbox in a form, how can I do? I tried to write [Forms]![Form_Name][Textbox_name], as you can see in the image below (in italian), but it doesn't work.

enter image description here

This is the error that I receive:

enter image description here

In english it can be translated like "unable to find identifier"

Upvotes: 1

Views: 2077

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 48999

You cannot reference Access or screen/form objects in a data macro. A data macro is the same idea as an SQL store procedure. The store produce runs at the database engine level. In fact you can open the database with vb.net, or even FoxPro and the data macros WILL STILL RUN.

So data macros are independent of the application. Data macros ONLY have local variables, and they are the same idea and concept of store procedures in SQL server. So this is data engine level code only.

The data engine and store procedure (data macros) thus cannot reach out and grab information from the “host” application like FoxPro, vb.net or MS-Access that just happens to have some form open. The data macro does NOT know or see application (vb.net, c++, MS-Access) that is using the ACE database engine.

The suggested workarounds are thus:

Don’t reference the forms value, but ONLY reference the table column value (this assumes that the column is in the SAME table). You can also lookup values from other tables. So ALL code in a data macro is TABLE level only.

If you must pass or use a forms! value in such code, then why not place your code in the forms before update event as opposed to using a data macro? This also means you can use VBA code. You thus don’t need nor want an engine level stored procedure routine as you are writing.

You can also pass the value from the form to a data macro, but means you WILL NOT be using table events to trigger and call such code (you use rundatamacro and pass parameters).

So you can call a data macro from the Access form, but such code will NOT be triggered nor called by the table level events.

So store procedure code cannot “reach out” to the application that happens to be using the Access database engine.

Upvotes: 2

Related Questions