Avatar of SteveL13
SteveL13
Flag for United States of America asked on

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

Microsoft Access

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)

8/22/2022 - Mon
Jim Dettman (EE MVE)

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.
SteveL13

ASKER
I want #1.
PatHartman

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 is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.