Move table based macro to form

[Kev86]

I have built a database for work which has about 50 or so users updating different forms at any given time. I have an issue where the database will get the error "Could not update; currently locked" about 5-10 times per week. I have searched around about how to fix this and came across a post on this site here https://www.accessforums.net/showthread.php?t=62089&page=4



In that post it was mentioned about issues having macros linked to tables which I have a few of in my database and believe this is the reason i am getting the "Could not update; currently locked" error. As a resolution it was mentioned about moving those macros from the table and implementing them into the form instead.

I currently have the macros linked to the table as an 'AfterUpdate' event however how do i get these moved off the table and instead linked to a forms 'AfterUpdate' event?

Sorry i am no expert on Access or writing code and most of my work is just from searching online for solutions and trial and error with the database i have built.

[Minty]

Table based macro's are called Data Macro's and are slightly different from normal macro's in the way they operate, and are triggered.
Can you describe what they are doing currently, as we may be able to make a better suggestions based on their specific use?

[Micron]

Open table in datasheet view. Ribbon>Table tab Named Macro (dropdown)> Rename/delete option will raise a dialog box where you can delete.
Suggest you research form events, specifically BeforeUpdate for data validation. AfterUpdate is too late for that purpose.

https://support.microsoft.com/en-us/...7-ce86553682f9

Most responders here don't use macros, so if you're using code you might find it a bit daunting at first. If you know what to ask for, you'll likely find lots of examples you can copy and try out. Methinks you want to be doing this in a play version of your db, not the current production one.

[Kev86]

Thank you both for your responses. So to give more context I have one large table that everyone interacts with at different stages using forms and they see those jobs at certain stages based on the updates that have taken place beforehand. The reason for the macro is to timestamp an update so I can then track my teams performance by working out how quickly we are processing jobs.

Rather than taking the date entered such as Quote Sent I take the date stamp as the start date, this is because a Designer could send a quote last week and forget to update the system and therefore it would impact my teams performance as it would appear we have taken a week to action the updates whereas in reality it was the Designer who forgot to update the system at the time the quote was sent.

So when the Designer updates Quote Sent the macro creates an entry in a new table called TATQuoteSent and references the job number and the date stamp of when the update was made. Then once my team do their updates to confirm the relevant systems have been updated i compare the difference between my teams update and the Designers update to calculate the working days it has taken my team to complete the task.

Yes Micron all the changes i am making are in a test version of the database and not the live production.

Please see below a screenshot of the AfterUpdate macro that is built into my main table. This is just one example but I actually have 17 different If Statements built into the tables AfterUpdate macro to capture different updates such as the one I explained above and some are just for tracking purposes to see who has updated which fields if we need to check that for any reason in the future.



Here is a screenshot of the If Statement that captures the user name of the person making an update...

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.

[Kev86]

I did have some screenshots of the code used in post 4 but they don't seem to be appearing? I see pbaldy has said the post was moderated so does that mean the pictures of the code were removed?

[Minty]

No unfortunately the pasting of images is broken, so you have to save them locally and upload them.
It's a real pain.

[orange]

I posted some info and a sample database on Data Macros here about 5 years ago. I used table data macros and a named data macro. This was my only time using data macros. It was more of a learning exercise and what I learned I posted in the forum. I had been trying to help another member with Martin Green's audit logging approach which dealt with form events. His approach had errors in that it identified the wrong record in deletes and it did not handle subforms. I am not aware of any method to move a table data macro to a form--that would seem to violate the underlying intent of a table data macro. But, as I said, I have limited experience with data macros. Also, we may be talking about different things.

[Kev86]

Here are the two images i tried to paste into Post 4 earlier, hopefully you can see the examples of If statements used in the table macro...

[pbaldy]

I did have some screenshots of the code used in post 4 but they don't seem to be appearing? I see pbaldy has said the post was moderated so does that mean the pictures of the code were removed?

To confirm what Minty said, I didn't delete the images, they just needed to be attached.

[Kev86]

Orange, Minty thank you both for your replies. Orange even having a macro in the form that does the same task but isn't linked to the table would be good. If you read the post i found previously Could Not Update; Currently Locked - Page 3 (accessforums.net) which sounds exact to the issue i am having with mine the suggestion from RuralGuy is to move the data macro to the form and from the posts it sounds like cdscivic does this and it fixes the issue for him. Here is the part of that posts that suggests this...

[orange]

If you get something working without locking up, I suggest you post a version of the database so others can benefit. With my working with data macros, these were table data macros and not related to forms.
Notice that cdscivic never did post a final working version. He also made reference to lookup fields in his table(s).

(in his post 31 "Point of note, moving the lookup field appears to have reduced the frequency of the error but it still occurring daily". )
It isn't clear exactly what cdscicic was using in his table and code. Unfortunately there was no solution posted.

I strongly suggest you post a copy of your database so we can see exactly what resolved the locking issue.

After posting, I now see that your earlier post with the macro code is displaying images. Would still suggest you post a version of your database, perhaps with only enough records to show the the functionality. We don't need all your data.

Did you look at my data macro/database link?

[Kev86]

Orange, thank you. Yes if i get to an answer i will share it on this post for everyone to see for future reference. I see cdscivic mentioned about the lookup fields but at the very end he says that the biggest impact was related to moving the macros away from the table...

In post 45 cdscivic does write... "Tables are for data and nothing else. If you are experiencing record locking like this, remove anything from your table that is more than just housing data. It should not be performing any operations and it escapes me why this is even an option in Access."

I wonder whether i need some VBA code to do the same action as my macro currently does so that i can build this into the forms AfterUpdate event for all relevant forms.

[orange]

What is the structure of your database? You have ~50 concurrent users--so you have a backend and each user has his/her own local front end?

Table and named data macros are from Microsoft. There are various audit logging samples using Form events, but they do not deal with queries, or direct table interaction, and have limitations.

See Allen Browne

[Minty]

As long as your forms capture all the events associated with the data changes then I would say you have nothing to lose in moving the process to the appropriate forms.
The one benefit of a table data macro (similar to a trigger in SQL Server) is that your data manoeuvres work all the time, no matter where the data is changed, added or deleted.

Just like triggers they should be used sparingly though as they are difficult to bug trace and aren't easily "Seen" from the front ends perspective.