1

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?

David Bailey
  • 77
  • 1
  • 8
  • It sounds like your tables could use some refactoring. Why not split the columns which are order specific in tblItems into a new `OrderParts` table or the like? – Brad Jul 07 '17 at 16:52
  • I've tried splitting the table, but then I was just applying the same FOR loop to the new table, and running into the same error again. I need a way to trigger this FOR loop from outside the table, but on an .AfterUpdate event. – David Bailey Jul 10 '17 at 13:26
  • This function boils down to doing a find-and-replace, but based upon a query and accepting some initial parameters. – David Bailey Jul 10 '17 at 13:40
  • If it's split into a parent table though you don't need any loop/trigger because the data only exist in one record. – Brad Jul 10 '17 at 13:54
  • OK. I get where you're going with this. It's something I'll have to try when I get some time. I did find a form-based macro solution that works and gives me a temporary fix, which I'll post here soon. – David Bailey Jul 24 '17 at 16:10

2 Answers2

1

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).

Erik A
  • 31,639
  • 12
  • 42
  • 67
0

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.

David Bailey
  • 77
  • 1
  • 8