Copy & update multiple tables from within forms?

[twgonder]

I’m still getting up to speed on the behind the scenes, black-box of Access.
I’m working on my template for forms. I want to make sure I’m doing things correctly in relation to code and variables behind the form. All of the questions deal with finding and updating/copying records while in a form.

There are two tables:
tblCustomer
tblCustomerNote
The reason for a separate table for the notes is: we don’t want to slow down network traffic with large blocks of long text unless they are needed.

There are two forms:


frmCustomer
frmCustomerNote

A record for a customer in each table has the same auto number primary key. Each form has a field called UpdtStmp which contains a date type on when the record was updated. There are more “update data fields”, but we will limit it to just the date for now.
The only way to trigger frmCustomerNote is with a command button on frmCustomer.
Each form has a class module procedure for before update which sets the UpdtStmp for the appropriate source table.
The logic is more complicated, but let’s just say for now that each form class module uses the code:
UpdtStmp = Now()

Since the logic is the same for the two form class modules, it makes sense to move the code to a module named modForm and then call a modForm procedure for setting the field data from the class procedure.
Now the first question, is the correct code now something like:
Forms(obFrmNm)!UpdtStmp = Now()
in modForm, or is there a better or more appropriate way?

It now gets a bit more complicated. Suppose the business rule is such that if someone (I can’t just say woman anymore) gets married and changes their last name, we start a new record (for the name change) on tblCustomer and mark the first record inactive. In some cases, we may want to copy but not inactivate the first record, say in the case where we copy a sibling and 90% of the tblCustomer data stays the same. So, we don’t automatically inactivate upon a copy, that is a separate action from copy in both cases. That’s left to the user to perform as appropriate.

I’m adding a “copy” command button to copy the active record in frmCustomer and create a new record with the same data and then move focus to the new record. Is there a way to do that? It would be nice to pick up the entire current record and copy it. I understand there is a Dynaset of data underneath the form, but I’m not sure how to properly use it if appropriate in this case. Is there simple code to do this or do we use an SQL update within the VBA procedure? It would be nice if the logic can be added to the modForm so it can be standardized across all forms that might copy.

Also, there’s always more, if the customer has a record in tblCustomerNote, we also want to copy that to the new auto number id in tblCustomerNote. The frmCustomerNote shouldn’t be open when the “copy” command button is pressed because it’s modal and can’t be open when the “copy” button is pressed on frmCustomer so the note record shouldn’t be dirty either.

And finally, if the first record for the name changing customer is marked inactive, it would be nice to mark the associated note record in tblCustomerNote inactive at the same time if it exists. There are other one-many and many-many records based on customer besides tblCustomerNote. So in the class module, we probably want a way to call the copy logic in modForm several times for all the table inactivates as well as copies.

Any design suggestions for all this? Feel free to tackle any single issue in a separate reply post for legibility.

[Bob Fitz]

A record for a customer in each table has the same auto number primary key.

How is this done?
I think it would be better to have one table.

[June7]

So you have a 1-to-1 relationship between Customer and CustomerNote? Only one Note per customer?

Not sure I would create a new customer record for a name change. Change the name in existing record and make a note of the event. That's basically what we do.

[twgonder]

So you have a 1-to-1 relationship between Customer and CustomerNote? Only one Note per customer?

Yes, only one big fat note field. Some of the other buttons support one-to-many (other names) and many-to-many (Relations).

Not sure I would create a new customer record for a name change. Change the name in existing record and make a note of the event. That's basically what we do.

It's a business rule that I must support. They want to know what was done under each name for legal reasons.

[CJ_London]

Principle is a data audit trail. Usual way is to have a single table to store

tableName
pk
fieldname
new value
date time
username

Can include old value as well if you want but that would be in the previous record

can also include an event type - create/amend
and a reason field as well

just include those fields that have changed, or all fields if you prefer

this is one area where data macros are worth investigating since this will capture changes done directly to the table or via a query.

with regards your note field I don’t see the benefit of 1-1. An empty note field takes up no space and if you don’t need it for a specific action, don’t include it in your query

[June7]

Yes, code can certainly copy data from one record to another. One way:

CurrentDb.Execute "INSERT INTO table(field1, field2, field3) SELECT field1, field2, field3 FROM table WHERE ID =" & Me.tbxID

Another utilizes recordset objects.

This is a fairly common topic.

If you want to explore the audit trail approach, review http://allenbrowne.com/AppAudit.html

[CJ_London]

another method is to have a history table - but in my experience is less effective if you want to know what has changed - either view by eye or extensive code to compare values with current and previous. I don't recommend using the history table for day to day use - if only because you can't maintain a PK

It's really about using the best method for the job in hand

[twgonder]

Principle is a data audit trail. Usual way is to have a single table to store

Similar, but in this case I want to copy the entire record from customer into the same table with without the old autonumber and with considerations for multiuser. It's not for audit trail purposes. As I said it's for legal purposes. Companies often buy the assets of another (including the name), and not the obligations (liabilities). Both continue to exist, one is just inactive (for purchases) until the legal process is completed. In other words, the new continues to buy product while the old settles its debts. (I'm not picking on you {you've been a great help and long-suffering CJ L}, but I can't help but laugh when other "developers" in other forums mock my ideas and it's obvious they haven't done one lap around the block yet.)

Can include old value as well if you want but that would be in the previous record

I am working on this audit trail now in pretty much the same manner.
Which brings up a question for other situations, that I've been wondering about. Where can one find the original old value from when the record was entered? Sometimes I need it for business intelligence validation logic. (I was about to make this a new thread.)

with regards your note field I don’t see the benefit of 1-1. An empty note field takes up no space and if you don’t need it for a specific action, don’t include it in your query

There's lots of queries I might do against the tblCustomer with an "*" that have no need for the potentially large note field. Why clog up the network?

[CJ_London]

Similar, but in this case I want to copy the entire record from customer into the same table with without the old autonumber and with considerations for multiuser.

but I can't help but laugh when other "developers" in other forums mock my ideas and it's obvious they haven't done one lap around the block yet.

per post #7 - tried it, experienced all the complications and the performance hit, multiuser or not. Key case in point, client (a financial institution) had a legal requirement to maintain a record all changes. had around 110,000 'primary records' which with changes built up to around 700,000 records. Performance dropped off primarily due to the requirement to get the current record. Changed it to maintaining the data in a history table (which included the current 'view') but users worked off the 'current' table and performance back to instant. Generation of invoices, copy statements etc referenced the history table.

But it's your app, you do what you think is best

Where can one find the original old value from when the record was entered?

when originally entered/created, there is no old value, it will be null/zls/whatever. When amending data, the oldvalue is a property of the control and/or field

me.controlname.oldvalue

[twgonder]

per post #7 - tried it, experienced all the complications and the performance hit, multiuser or not. Key case in point, client (a financial institution) had a legal requirement to maintain a record all changes. had around 110,000 'primary records' which with changes built up to around 700,000 records. Performance dropped off primarily due to the requirement to get the current record. Changed it to maintaining the data in a history table (which included the current 'view') but users worked off the 'current' table and performance back to instant. Generation of invoices, copy statements etc referenced the history table.

I must not be explaining well. I'm not trying to reconstruct the tbl_Customer as it was, rather just create a new (copied) record in tbl_Customer for the new entity.

In my original post, read the paragraph that begins with this: "It now gets a bit more complicated"

[June7]

Did you read post #6?

[CJ_London]

I must not be explaining well. I'm not trying to reconstruct the tbl_Customer as it was, rather just create a new (copied) record in tbl_Customer for the new entity.

I understand perfectly well. That is pretty much what I was describing with my client. But what do I know. You go ahead do it your way.

[June7]

I can't imagine there would be so many name changes to cause a significant performance hit from table bloat.

[CJ_London]

lots of small changes - married/divorced/separated/name change/death/account closed/trustee/administrator/tax domicile/preferred communication method, typo corrections to name a few. Complication was you couldn't link to to the PK as that changed, you had use a separate indexed/duplicates OK field to link to child tables, but they in turn could change (people moved) and many things would be date specific. It just meant the queries became more complex. With small data volumes used for testing, not a problem, it only became apparent once the data volumes grew. Changing to one line of code in the form beforeupdate event to copy the data to a separate history table made everything much simpler and faster and solves the problems TWG is anticipating in his original post. My clients requirement may not be the same as TWG's and/or perhaps data volumes are lower so perhaps the method is not appropriate for him.

As far as TWG's comment 'It's a business rule that I must support. They want to know what was done under each name for legal reasons.' is concerned, it dictates a requirement, but not the how that requirement should be met.

[twgonder]

Did you read post #6?

Yes I did, I see two problems:
1) One has to list all the fields to insert, a big pain when there are dozens of fields to insert and you can't use * (I'm not sure what Access would do trying to insert the autonumber from the old record)
2) if the table changes, one has to go fix a bunch of hard coded lines.

This was why I was wondering if there is a way to insert/append the underlying form dynaset. However, this won't work for all the other related table records that have to be copied too, so your approach has some merit for all these tables.