CODEWITHSUNDEEP
ms-accessms-access-2016ms-access-data-macro
David Bailey
David Bailey

Reputation: 77

How to update multiple records in same table using .AfterUpdate data macro without error "A data macro resource limit was hit."

I have a table tblItems with a list of inventory items. The table has many columns to describe these items, including columns for SupplierName, SupplierOrderNumber and PredictedArrivalDate.

If I order several new items from a supplier, I will record each item separately in the table with the same supplier name, order number and a predicted arrival date.

I would like to add a data macro, so that if I update the PredictedArrivalDate for one record, the value will be copied to the PredictedArrivalDate column of other records/items with the same SupplierName AND SupplierOrderNumber.

The closest I've got is:

SetLocalVar (MySupplierName, [SupplierName])
SetLocalVar (MySupplierOrderNumber , [SupplierOrderNumber ])
SetLocalVar (MyPredictedArrivalDate, [PredictedArrivalDate])

For Each Record in tblItems
    Where Condition = [SupplierOrderNumber] Like [MySupplierOrderNumber] And [SupplierName] Like [MySupplierName] And [PredictedArrivalDate]<>[MyPredictedArrivalDate]
    Alias OtherRecords

EditRecord
    SetField ([OtherRecords].[PredictedArrivalDate], [MyPredictedArrivalDate])
End EditRecord

However, when I run this, only 5 records update, and the error log reports error -20341:

"A data macro resource limit was hit. This may be caused by a data macro recursively calling itself. The Updated() function may be used to detect which field in a record has been updated to help prevent recursive calls."

How can I get this working?

Upvotes: 1

Views: 1384

Answers (2)

David Bailey
David Bailey

Reputation: 77

This isn't a good solution (it's not a data macro), but it does work as a temporary fix.

I created an update query called "updatePredictedArrivalDate":

PARAMETERS
ItemID Long,
MyPredictedArrivalDate DateTime,
MySupplierName Text ( 255 ),
MySupplierOrderNumber Text ( 255 );

UPDATE tblItems
SET tblItems.PredictedArrivalDate = [MyPredictedArrivalDate]
WHERE (((tblItems.SupplierName) = [MySupplierName])
AND   ((tblItems.SupplierOrderNumber) = [MySupplierOrderNumber])
AND   ((tblItems.ID) <> [ItemID]));

On the PredictedArrivalDate form field .AfterUpdate event, I then added this macro:

IF [PredictedArrivalDate].[OldValue]<>[PredictedArrivalDate] Or [PredictedArrivalDate]<>""

OpenQuery (updatePredictedArrivalDate, Datasheet, Edit, [ID], [PredictedArrivalDate], [SupplierName], [SupplierOrderNumber])

I now have to remember to add this .AfterUpdate event to any other forms I create that amend that particular field.

If anyone has a better solution, please let me know.

Upvotes: 0

Erik A
Erik A

Reputation: 32672

I'm not one for using macro's to do anything, so I'd use VBA and recordsets/an action query to do the updating.

You can call a user-defined function inside a data macro by setting a local var equal to its result.

Access doesn't like data macros triggering themselves (which you are doing, you're using an on update macro and updating fields in the same table on a different record), because there is a risk of accidentally creating endless loops. Looks like you triggered a measure that's made to prevent this. I'd try to avoid that as much as possible.

Note: using user-defined functions inside data macros can cause problems when you're linking to the table from outside of Access (via ODBC for example).

Upvotes: 1

Related Questions