How to update two date field and co- relate two date field.

Question

How to update two date field and co- relate two date field.

I am an Advocate/ lawyer and I am new to Microsoft Access 2010 professional version and my key field of the database is as below of my cases.
Field Name    Data Type
Olddate         (Date/Time)  (dd/mm/yyyy)
courtName     (Text)
CaseNo         (Text)
Petitioner       (Text)
Respondent   (Text)
CaseStatus     (Text)
NextDate       (Date/Time)  (dd/mm/yyyy)

Now the Question is that I want to change/update only two field in Microsoft Access 2010 Database first one is Manually Change NextDate Field and second is Automatic Olddate Field.
Whenever I get Next date of the particular case/Record the record will update by date and the old date should be next date. for example.

OldDate     CourtName CaseNo Petitioner Respondent   CaseStatus   NextDate
26/11/2012    CJM        40/2012   STATE  VS     MARK          CHARGE        10/12/2012

ON THE DATE 10/12/2012 IF I GET NEXT DATE 20/12/2012 THE UPDATED RECORD OF THIS PARTICULAR SHOULD BE

OldDate    CourtName   CaseNo Petitioner Respondent CaseStatus     NextDate
10/12/2012    CJM           40/2012  STATE  VS  MARK         CHARGE          20/12/2012

ON THE DATE 20/12/2012 AGAIN IF I GET NEXT DATE 03/01/2013 THE UPDATED RECORD OF THIS PARTICULAR SHOULD BE

OldDate  CourtName     CaseNo   Petitioner  Respondent CaseStatus   NextDate
20/12/2012    CJM           40/2012    STATE  VS        MARK   CHARGE        03/01/2013

Please help me to change OldDate Field and NextDate Field. Old Date field should change Automatic using any formula and NextDate Field by user who change the next Date.

Answer 1

You can do this in a form based on the table.

With the form open in design view, select the text box bound to NextDate. I'll assume that this text box is named the same as the field: NextDate.

Activate the Event tab of the Property Sheet.

Click in the Before Update property.

Select [Event Procedure] from the dropdown menu in this property.

Click the builder dots ... to the right of the dropdown arrow. This activates the Visual Basic Editor.

You'll see code like this:

Private Sub NextDate_BeforeUpdate(Cancel As Integer)
    
End Sub

Make the code look like this, for example by copying the code between the Sub ... and End Sub lines from this post, and pasting it between the lines in the Visual Basic Editor:

Private Sub NextDate_BeforeUpdate(Cancel As Integer)
    If MsgBox("You are about to update NextDate." & vbCrLf & _
            "The previous value will be stored in OldDate." & vbCrLf & _
            "Click OK to continue, or Cancel to keep the old value.", _
            vbQuestion + vbOKCancel) = vbOK Then
        Me.OldDate = Me.NextDate.OldValue
    Else
        Me.NextDate.Undo
        Cancel = True
    End If
End Sub

Close the Visual Basic Editor.

Switch to form view to test what happens when you change the date in NextDate.

---

Regards, Hans Vogelaar

Answer 2

As said before:

If you do not want to keep any further history, on the form where you update the NextDate create an after update event on the NextDate field like:

Private Sub NextDate_AfterUpdate()
If Me.NextDate <> Me.NextDate.OldValue Then
    Me.OldDate = Me.NextDate.OldValue
End If
End Sub

In case you want full history, you should remove OldDate en NextDate from this table and create a new table (CaseDate) with 2 fields: CaseNo and CaseDate (or whatever name you want to use). Then you can store as many dates with one case as you want.

---

Groeten, Peter http://access.xps350.com/