Data Macros

[Mark L]

I've never used these before, but am considering using them to create a single field to be the Primary Key, combining (and formatting) other fields, rather than using an autonumber field as the PK. For example, here is a sample situation of 4 tables in which each table is the parent of the table immediately below it:

Order
OrderLine
ProductionLot

It is the ProductionLot table that is most affected here. Previously, the Order table would have a PK of OrderNo; the OrderLine table would have a PK of OrderLineID (autonumber) with fields OrderNo and LineNo; and the ProductionLot table would also have an autonumber PK and fields for OrderLineID and LotNo.

Now I'm thinking I could create the OrderLine PK to be a combination of the OrderNo and LineNo, and actually format it so it might look like: 01234-001.
The ProductionLot PK field would be a combination of the OrderLine and LotNo: so it might look like 01234-001-1.

This latter ProductionLot PK field as displayed is what the client uses all the time. It is printed on labels, work orders, production reports, etc. So rather than having to figure out how to format and display it each time I need to produce it, it seems easier to just have the system do it.

Let's take the disk usage off the table. I'm not talking about hundreds of thousands of records where a 4-byte autonumber would use less space than an 11-charter string; this is more like only thousands of records. So I don't think it is a consideration.

Anyone have experience doing this? Any thoughts on pros/cons?

[HansV]

I don't think you'd need data macros for that. You can keep the separate fields OrderNo, LineNo and LotNo. In the OrderLine table, define a calculated field with formula:

[OrderNo] & "-" & [LineNo]

or if you want to include formatting:

Format([OrderNo], "00000") & "-" & Format([LineNo], "000")

and an analogous calculated field in ProductionLot.

[Mark L]

It didn't seem like a calculated field would accept the format function.

But more importantly, that doesn't really buy me anything. Having it as the PK makes it useful.

[HansV]

Sorry, I hadn't actually tried Format in a calculated field, but you're correct - it doesn't work.

I don't see how this can be done using data macros; perhaps someone else has an idea.

[Mark L]

The format function will work within the data macro, and I've tested it by setting-up a couple of tables. It seems to work fine. I'm just wondering if there is something I'm missing that might prove to be a problem in the long run.

I did find one potential problem. In both my test tables, I added fields for [ChangedBy] and [ChangedOnDateTime]. Both populated by data macros on the BeforeChange event. The macro to populate the ChangedBy field was a user-defined function getUserID(); for the ChangedOnDateTime field, I just used Now(). Made it very easy to date/time stamp each record when changed.

However, when I changed any field value (like the Quantity field) in a record in the parent table, I noticed that the ChangedByDateTime in the child records also changed! I'm theorizing that because i had a data macro to calculate the PK in that BeforeChange event, it recalcs it every time and cascades the updated field (even though the value didn't actually change) to the child records.

However, I was able to solve this problem by using an IF action in the data macro so the PK only recalcs if the value would change, and therefore the Cascade Update didn't trigger.

I'm starting to like these things!

[HansV]

Thanks for the update.