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.