Trying to make date in new record default to the last date entered in previous record

I have a datasheet view form that show the records in DESC order because I want to see the last record at the top.

When I enter a new record that has a date field (short date format), I want the next record to default to whatever date I entered in the current record when I click on any field in the next record. Does this make any sense?

I've tried these suggestions from previous Expert's help but none are working. (all in the after update event of the date field)

    Me!txtReportingDate.DefaultValue = Me!txtReportingDate

Open in new window

    With Me!txtReportingDate
        If Not IsNull(.Value) Then
            Me!txtReportingDate.DefaultValue = "#" & Format(.Value, "yyyy\/mm\/dd") & "#"
        End If
    End With

Open in new window

Dim dtmMaxDateStamp As Date
Dim dtmPreviousDate As Date

             dtmMaxDateStamp = DMax("txtReportingDate", "tblHoursWorked")
             dtmPreviousDate = DLookup("txtReportingDate", "tblHoursWorked", "txtReportingDate= " & "#" & dtmMaxDateStamp & "#")
             Me.txtReportingDate = dtmPreviousDate

Open in new window

What you want to do then is carry forward the PK of the last record saved (grab it in the BeforeUpdate event).

Then use the following code:

Function AutoFillNewRecord(frm As Form, strCriteria As String)

   ' Picked up from MSKB Q210236
   ' Needs control on form called AutoFillNewRecordFields, which
   ' is a semi-colon delimited list of fields to fill.
   
   Dim rs As DAO.Recordset
   Dim ctl As Control
   Dim strFillFields As String
   Dim intFillAllFields As Integer
   
   On Error Resume Next
   
   ' Exit if not on the new record.
   If Not frm.NewRecord Then Exit Function
   
   ' Jump to the correct record (to autofill form).
   Set rs = frm.RecordsetClone
   
   If strCriteria = "Last" Then
     rs.MoveLast
   Else
     If strCriteria = "Prev" Then
       rs.MovePrevious
     Else
       rs.FindFirst strCriteria
       If rs.NoMatch Then Exit Function
     End If
   End If
   
   ' Exit if you cannot move to the last record (no records).
   If Err <> 0 Then Exit Function
   
   ' Get the list of fields to autofill.
   strFillFields = ";" & frm![txtAutoFillNewRecordFields] & ";"
   
   ' If there is no criteria field, then set flag indicating ALL
   ' fields should be autofilled.
   intFillAllFields = Err <> 0
   
   frm.Painting = False
   
   ' Visit each field on the form.
   For Each ctl In frm
      ' Fill the field if ALL fields are to be filled OR if the
      ' ...ControlSource field can be found in the strFillFields list.
      If intFillAllFields Or InStr(strFillFields, ";" & (ctl.Name) & ";") > 0 Then
         ctl = rs(ctl.ControlSource)
      End If
   Next
   
   frm.Painting = True
   
End Function

Open in new window

Which is based on:

How to fill a record with data from a previous record automatically in Access 2000 and 2002 and Office Access 2003
https://support.microsoft.com/en-us/kb/210236

There's a minor tweak to that code in that you can use the last row or pass in a criteria to find the record.

Call this from the OnCurrent event.

Jim.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 60

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented: 2015-09-29

So to be clear, there are two ways this is typically done:

1. You want the last date *entered* by a user in a new record as a default.

2. You want the date from the previous record according to some sequence (i.e. looking at the records in desc sequence according to a date/time).

Not quite the same.

The code you posted is trying to do #2.

Jim.

SteveL13Author Commented: 2015-09-29

I want #1.

LVL 44

PatHartmanCommented: 2015-09-29

To find the date entered in the "previous" record, you would need to find that record. If you have an autonumber primary key, you could find the max value of the autonumber and get the date from that record. Of course this presumes that records are entered in some predictable order. If the most recently entered record is one that is out of sync, then all bets are off.

You would not use the default property for this purpose. Put the Lookup code in the BeforeInsert event. That event fires as soon as someone starts typing in the record. I added a where clause because I think you may need it. If not, just ignore it.

Me.txtReportingDate = DMax("ReportingDate", "tblHoursWorked", EmployeeID = & Me.txtEmployeeID)

PS - based on your sample code, it looks like you actually want Jim's option 2 so that is what my code sample does.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Trying to make date in new record default to the last date entered in previous record

Who is Participating?