splitting a form into 2.

[dotolee]

Hi there.

I've been asked to modify a production database. I have a form that needs to be split into two different forms. (for now, i'll call them FORM A and FORM B) However, the back end table that it writes to will NOT change / cannot change. AKA. Both forms will write to the one table.
The goal is to accomplish this change with minimal impact to existing tables and data.

The problem I'm running into is with an audit routine (i got it from this site and it works great!) that keeps track of all changes made to the main table via forms.
Now that I’ve had to split the form in 2, it’s being triggered properly

PROBLEM 1

When I run and save data using FORM B, the audit doesn’t even get called. I think it’s because of the way I’m opening FORM B, but I’m not sure.

Here’s the code to call the audit routine inside by FORM B:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call basLogTrans(Me, "ClientNO", ClientNO)
End Sub

The way we're saving is by using the VCR controls on the bottom of the form. The users click on the "new record" option... which used to trigger the beforeupdate method.

And here’s the code to actually open the FORM B from FORM A.

Private Sub cmdOpenB_Click()
On Error GoTo Err_cmdOpenB_Click
'save current form first.

DoCmd.RunCommand acCmdSaveRecord


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Bform"

stLinkCriteria = "[ClientNo]=" & "'" & Me![ ClientNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpenB_Click:
Exit Sub

Err_cmdOpenB_Click:
MsgBox Err.Description
Resume Exit_cmdOpenB_Click

End Sub


PROBLEM 2

Since i'm only dealing with one backend table and two front end forms, all the default values i have specified for fields that now show up on FORM B are initialized with their default values in the data table when I run and save a record using my FORM A.

Of course when I saved the record in FORM A, these same values (for FORM B controls) are not written out to the audit table, because the audit is driven by controls on the form that’s currently being used. So my audit table and main table don’t match.


Here's the basic audit routine;

For Each MyCtrl In frm.Controls
If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then

Call basAddHist(Hist, frm, mykey.Name, MyCtrl)
End If
End If
Next MyCtrl

My question is, what’s the best way to force a match between the audit and the main data table? I guess when I save form A, I could write a routine to manually write out entries to the audit for all controls on Form B. Is this the best way?
I'm trying to isolate all changes to new forms and new routines vs. editing existing objects to minimize bugs.
Thanks.
I apologize if this is unclear - if you need any more details, please let me know.

 

[dotolee]

I guess one way to do this is to check for the default values in the audit routine:
For Each MyCtrl In frm.Controls
If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) _
Or (MyCtrl.value=100) Then

Call basAddHist(Hist, frm, mykey.Name, MyCtrl)
End If
End If
Next MyCtrl

but it will still look like the user tampered with the record twice... when in reality they've just created one new record.
If you have any suggestions / comments, i'd appreciate it.