Unexplainable problem where an Update query unintentionally triggers before update table macro

[rafpac]

Hi All,



Hope the year has been good to you. I have an interesting situation and not sure why this is happening.

I have a table with 2 columns impacted by a macro. The first column is a status column and the second column is a date column.

The before update macro is setup that when the status field (which is a data validation drop down, either "open" or "closed") is set to closed, then the date column will equal Now(), else Null.

Then I created an update query on access, which takes 2 columns called "Major" and "Minor" from another table and updates the "Major" and "Minor" columns in the first table when run.

So the macro is dealing with the "Status" and "Date" columns while the Query impacts the "Major" and "Minor" columns.

Now what's intriguing is that when the update query is run, for some reason the macro seems to be triggered as well, then takes all the records with the Status as "Closed" and changes their date columns value to Now() when the update query is run?

This is strange as the update query should not be impacting the before update macro.

Does anyone know why this maybe and how I can resolve this problem? Thanks.

Kind Regards,

Raf

[June7]

Are you talking about Data Macro? I don't see a Before Update data macro. Do you mean Before Change?

I just did a test and this is apparently normal behavior. UPDATE query is editing record and that triggers Before Change macro.

I never use macros but I would expect same issue with form's VBA Before Update event.

[rafpac]

Are you talking about Data Macro? I don't see a Before Update data macro. Do you mean Before Change?

I just did a test and this is apparently normal behavior. UPDATE query is editing record and that triggers Before Change macro.

I never use macros but I would expect same issue with form's VBA Before Update event.

Thanks for looking into this. Apologies I indeed meant Before Change Macro. So if this is normal behavior that all update queries will automatically trigger before change macros, any alternatives to macros for me to auto populate the date field when the status field is changed to Closed?

[Minty]

The Data macro operates for all data changes - it pays no attention to any form related events, it's completely independent.
That's the point of them, to capture changes to data made anywhere, by anything.

You have to decide to either only use the form events for this or rely on the Data Macro to do the job, not both, as inevitably there will be a conflict.

[June7]

Use a form for user interaction. Use Status combobox AfterUpdate event (macro or vba) to modify other field data.

Users should not interact directly with tables and queries.

[rafpac]

Use a form for user interaction. Use Status combobox AfterUpdate event (macro or vba) to modify other field data.

Users should not interact directly with tables and queries.

Hi June7,

Thanks again. So to clarify the users are indeed using a form. The issue I am having has nothing to do with user interactions. The problem here is I am indeed currently running a change macro that populates a field with a date when the status of another field is changed to closed. But I also have an update query which is used to bulk import relevant data into the same table from another table monthly. But as you have pointed out that the update query will also trigger the macro.

So I have to either scrap the update query or scrap the macro. So I am asking if there is another way outside of Macros to populate date when the status is changed? If not then is there a way to bulk update 2 fields in this table using data from another without an update query?

Let me know if you have any solutions for this. Thanks.

Kind Regards,

Raf

[June7]

I already suggested using control AfterUpdate event instead of data macro.

Already established that it doesn't matter how field data is changed, will trigger table BeforeChange event.

[orange]

Raf,
Can you post a copy of your database or some mockup that highlights the issue?
Or tables and relationships?
Perhaps a sample showing the tables and what causes the status to change and result.
I need more context.

[rafpac]

Ah I see what you mean. AfterUpdate event instead of a BeforeChange event. But anyways your advice did help me out immensely. Because I deleted the Before Change Macro from the table and implemented a VBA script on the form end to enter Now() into the date field when the user presses a button to close the record. And voila...I can use the Update query on the table end without triggering anything on the form end. Thanks so much!

[rafpac]

Hi Orange,

The status changes to close by clicking a button on the front end form by the user. Then a VBA script on the form side changes the table data to Closed. So to solve my problem I just added another line on the form side to enter the Now function into the table date field. Now the update query on the table side doesn't trigger anything.

Anyways shout out to all you guys for helping!