Cancel update of single field on MS Access 2007 bound form

Question

I may be overthinking this, but I see all sorts of potential problems....

I have a table that I'll call Master. Among other items, it contains a field called Serial. I thought this would be a static item--i.e., that it would never change (once initially assigned), for any given row in Master. I now find out that it can change, and not only that, I need to store history of those changes.

I can break Serial out into a separate table, where each row will have a MasterId, EffectiveDate and Termination Date (the current Serial for a Master row would be the one (and only one--code-enforced) row with a Null TerminationDate). So far, so good....

Now the problem that I'm asking about: I have a bound form built on a Select from Master that includes Serial.

What I think I want to do, to make this changeability transparent to the user, is to short-circuit any changes that are made to the Serial text box, probably in the text box BeforeUpdate event. I'd then use code to Terminate the current row in the Serial table & insert a new row, which becomes current and is displayed in the text box. I would (I think) need to cancel the update in the form's recordset, without losing any changes the user has made to other fields....

It's at this point that my brain goes out to lunch, since I'm still much more comfortable with unbound forms & Save buttons. Can I do this, as proposed? What tricks & traps do I need to know about? If need be, I can make the Serial field on the form read-only, and require a pop-up to edit it, but I'd rather avoid that if I can.

**EDIT

Hope this helps:

Before

table Master
  Id             AutoNumber  PK
  Serial         Text(20)
  --other fields

Current frmMaster RecordSource:

SELECT Id, Serial, yada, yada FROM Master WHERE blahBlah

After

table Master
  Id             AutoNumber  PK
  --other fields

table Serial
  Id             AutoNumber  PK
  MasterId       Long
  Serial         Text(20)
  EffDt          Date/Time
  TermDt         Date/Time

New frmMaster RecordSource:

SELECT Master.Id, Serial.Serial, yada, yada 
FROM Master LEFT JOIN Serial ON Master.Id = Serial.MasterId 
WHERE Serial.TermDt is Null AND blahBlah

An "edit" on the form should result in:

1. Selected row in Serial gets TermDt filled (Now()). NO CHANGE is to be made to the value of Serial.Serial on this row.
2. A new row in Serial is created, with new Serial, same MasterId and EffDt = Now()
3. New Serial displayed on form, preferably such that it looks to the user as if they'd just edited in place.
4. Any changes made to other fields on the form get saved.

EDIT, again

Okay, so HansUp has given an answer that looks like it covers most of what I need. My remaining stumbling block may not even be an issue (though if that's the case, can someone explain why?).

The use case of adding a Master is pretty straightforward, but in the use case of an update that changes the Serial I'm still confused: if txtSerial is bound to Serial.Serial, and the user changes the contents of txtSerial, won't Access attempt to Update the contents of Serial.Serial to match? How does Adding a Serial row on change (which looks like it should work a treat), prevent the Update of the existing Serial row?

Answer 1

You haven't shown us sample data for the Serial table. I'm curious whether it could look like this:

Id MasterId Serial  date_added
 1        1 foo      4/21/2011 7:00:00 AM
 2        1 bar      4/21/2011 9:00:00 AM
 3        1 foo      4/21/2011 11:00:00 AM
 4        2 asldkjf  4/4/2011 1:00:00 PM

Whenever you need the Serial rows expressed with EffDt and TermDt, you can use a correlated subquery.

SELECT
    s.Id,
    s.MasterId,
    s.Serial,
    s.date_added AS EffDt,
    (SELECT TOP 1 sub.date_added
    FROM Serial As Sub
    WHERE
        sub.MasterId = s.MasterId
        And sub.date_added > s.date_added
    ORDER BY sub.date_added) AS TermDt
FROM Serial AS s
ORDER BY
    s.MasterId,
    s.date_added;

I realize your question is broader. For now, this piece is one I can wrap my head around. But does it fit your requirements?

Update: I used my version of the Serial table. And I created a Master table with autonumber Id, text Serial, plus 2 other text fields, Other_field1 and Other_field2. Then created a simple continuous form bound to "SELECT Id, Serial, Other_field1, Other_field2 FROM Master Order By Id;".

Here is the form's code:

Option Compare Database
Option Explicit
Dim varSerialOldvalue As Variant

Private Sub Form_AfterInsert()
    addSerialRow
End Sub

Private Sub Form_AfterUpdate()
    Dim strSql As String
    If Me.txtSerial.value <> varSerialOldvalue Then
        addSerialRow
    End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    varSerialOldvalue = Me.txtSerial.OldValue
End Sub

Private Sub addSerialRow()
    Dim strSql As String
    Dim strMsg As String

On Error GoTo ErrorHandler

    strSql = "INSERT INTO Serial(MasterId, Serial, date_added)" & _
        vbNewLine & "VALUES(" & Me.txtid & ",'" & _
        Replace(Me.txtSerial, "'", "''") & "', #" & _
        Format(Now(), "yyyy/mm/dd hh:nn:ss") & "#);"

    CurrentDb.Execute strSql, dbFailOnError

ExitHere:
    On Error GoTo 0
    Exit Sub

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.description _
        & ") in procedure addSerialRow" & vbNewLine & _
        "SQL: " & strSql
    MsgBox strMsg
    GoTo ExitHere
End Sub

Is that a useful starting point for what you want to accomplish?

Update2: You said txtSerial is bound to Serial.Serial; but my approach binds txtSerial directly to a Serial field in the Master table ... which is what I thought you originally proposed. What is wrong with that approach now?

Answer 2

Actually, I do not see why you want to prevent the change to the text box? If I am reading this correct, what you are saying is if the text box changes, then you need to save the previous current data and record before changes?

If above is the case, then simply in the after update event of the serial text box, run an append query that takes the current record from the table and copies (appends it somewhere else).

While you have a bound form, the changes to controls on the form are NOT YET committed to the actual underlying table. If your code or a process or a some append query or VBA code looks at or makes a copy of that row from the table, all of the old values will still be intact.

So a bound form does not mean each change in a text box is written to the table, but in fact the whole record is ONLY written to the table when the record is saved, and that is why the forms before update event has a cancel that can prevent the record write from occurring until your rules etc are met.

So you just need one or two lines in the serial box after update event (that event does not fire unless changes are made to the text box) to copy the current record from the table which will have all of the old values, including the old serial number.

Answer 3

If Master doesn't change too often and isn't too large, then when saving, append the entire Master record with a whoChanged record added and a timefield. Then when you want to see changes in Serial, just do a GroupBy on Serial and you can see the times and who changed it.

This is overkill for one field, but in general provides a good way of recording changes.