Previous Value of a text box

[ted.martin]

I have a form with 108 textbox controls, each a representing 5 minute time slots. I need to know when a textbox value has been changed and what the previous value was so that I can validate the change or undo.

I know I could use the Before and After Update event but to write this code for each of the 108 controls would seem to me to be clumsy.

The On Dirty event tells me a text box has changed and the Active Control can tell me which Control has the focus but how do I get the original value in the text box before it was changed.

Any thoughts would be most welcome.

 

[mdlueck]

In my applications, I have Add / Edit forms as unbound forms. When an Edit form opens, VBA code fetches the record from the DB and populates the form fields.

This could be done in such a way so as to keep a copy in VBA variables of the before state of the record.

And actually, I use this technique in one spot where multiple records are downloaded to the Access FE DB, I capture in memory the check-out state of those records. Then when it comes time to commit the changes back to the DB, I compare the before state with the current state, and only send changes back to the DB.

 

[ted.martin]

Any interesting approach that would work; thanks. Lets see if any other solutions arise.

I am playing around with Locking the textboxes on the Current event to prevent those that I don't want changing being changed. Noy happy with it yet but its a start. Thanks again.

 

[April15Hater]

Not really sure how your procedure is updating the text boxes, but if it's a single procedure, you could put that procedure in a class module, declare the class as a module level variable of the form with events and create a custom event after writing to the text box.

 

[mdlueck]

Well since April15Hater mentioned "class" then I will elaborate with a few additional details of my design...

I use an in-memory custom collection class to maintain the historical snapshot to compare back to. That way the code needs to only keep track of that one collection object, and no matter how many attributes the thing have, those are just attributes of the collection instance.

Here is what I based my Collection classes on. I have made some minor enhancements / clarifications to the author's code examples.

Using Custom Collections in Microsoft Access
http://www.databaseadvisors.com/new...ng custom collections in microsoft access.asp

I name my classes, for example if I am tracking Items on an Order, I create an *Item and *Items class, where the * part is something specific to the type of data being tracked.

 

[ted.martin]

Excellent and thank you for your considerable input on this. You probably won't be proud of me but I resolved my problem by locking all the cells and then unlocking the blank ones.

Simple enough code but does what I need it to do. By way of explanation we have 5 minute time slots from 09:00 to 17:55 = 108 slots in all. Attached is the screen shot. A is an Appointment that cannot be changed here and X is a Blocked Time slot that can be amended. This form is for Blocking Time slots only not managing general appointments.

Note - during the loop I need to trap (say) the time value 1260 ( 5 onto 1255) as this is clearly not a time. This is done via managing error 2465 9field missing)

I will do a separate post in a minute to show you how I used code to get the layout precise. Manually positioning 108 boxes and label would have been a nightmare.

Here is my code.

Private Sub Form_Current()

On Error GoTo Err_Field

Dim I As Integer
Dim F As String

For I = 900 To 1755 Step 5

    If I < 1000 Then
       F = "0" & I
    Else
        F = I
    End If

If Me.Controls(F).Value = "A" Then
    Me.Controls(F).Locked = True
Else
    Me.Controls(F).Locked = False
End If

myNext:

Next I

Exit Sub

Err_Field:

If Err.number = 2465 And Right(F, 2) > 55 Then    ' Trap error when count goes from 60 to 95
    Err.Clear
    Resume myNext
Else
    MsgBox Err.Description, vbCritical, Err.number
End If

End Sub