Solved

If new start of database use Date() but if entering a new record after a record has been added using previous record date

Posted on 2015-02-05
9
136 Views
Last Modified: 2015-02-06
I got advise from an expert a while back and I thought the solution was working but I now know it isn't.  Probably because I didn't explain the topic well.

What I want is for a for a date field on a form to automatically populate with today's date IF it is the first new record entered in the form when the database is opened.  But if I have already entered a record using the form and am adding another new record then I want the date to fill with the previous records date.

The code I had received before for an add record command button was:

Dim dtmMaxDateStamp As Date
Dim dtmPreviousDate As Date

    If Me.NewRecord Then
        If DCount("InvTranxID", "tblInventoryDetails") = 0 Then
            Me.txtTranxDate = Now()
        Else
            Me.txtProductID.Requery
            dtmMaxDateStamp = DMax("TranxDate", "tblInventoryDetails")
            dtmPreviousDate = DLookup("TranxDate", "tblInventoryDetails", "TranxDate= " & "#" & dtmMaxDateStamp & "#")
            Me.txtTranxDate = dtmPreviousDate
        End If
    End If

Open in new window

0
Comment
Question by:SteveL13
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 40592684
Give this a try:

Dim lngPreviousID As Long

    If Me.NewRecord Then
            Me.txtProductID.Requery
            lngPreviousID= NZ(DMax("InvTranxID", "tblInventoryDetails"), -1)
            Me.txtTranxDate = NZ(DLookup("TranxDate", "tblInventoryDetails", "InvTranxID= " & lngPreviousID), Now())
    End If

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 40592691
In the above, I'm assuming that InvTranxID is an auto number field.  If it is NOT an autonumber, replace both occurrences in the code above with your autonumber PK field name, if you have one.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 40592897
I don't understand the code you were given.  It is counting records without using any criteria.  So if the table is empty, it returns the current date AND time.  Otherwise it gets the max date value  which would always be the original value since only when the table is empty would you actually assign a new date/time.  Then looks for the record it just found?

I'm trying to understand your requirement.  I don't see how the second date would ever be different from the first unless you opened the database and simply left it open across midnight so the date that was current when it opened would be different from what is current now.

Also, you say "date", you don't say "date/time" so using Now() would only cause a problem since it includes time of day.  You should be using Date() if all you really want is the date.

Please explain what problem you are trying to solve.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40593029
> I don't see how the second date would ever be different from the first unless you opened
> the database and simply left it open across midnight so the date that was current when it opened
> would be different from what is current now.

True.
The requirement doesn't make sense.

/gustav
0
 

Author Comment

by:SteveL13
ID: 40593724
Mbizup:  Yes, InvTranxID is an autonumber key field.  But your suggestion didn't work.  When I add a new transaction after just strating the database the file seems to do a lookup and put the last date entered in the field on the form.

Just to restate what I want to have happen...

What I want is for a for a date field on a form to automatically populate with today's date IF it is the first new record entered in the form when the database is opened regardless of whether or not previous records exist for that particular part.  But if I have already entered a record using the form and am adding another new record then I want the date to fill with the previous records date.

I'm sorry if I'm not explaining it well enough.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40593737
Then you want to set the DefaultValue (edit: As a string expression):

On form Open:
    Me!txtDate.DefaultValue = Format(Date, "\#yyyy\/mm\/dd\#")

On AfterUpdate of record:
    Me!txtDate.DefaultValue = Format(Nz(Me!txtDate.Value, Date), "\#yyyy\/mm\/dd\#")

/gustav
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40593823
So your explanation still doesn't make sense.  If you wanted to use a previous records date for a particular part number and use today's date if there are no records yet for that part, then you'd use something like this:
If Me.NewRecord Then
    Me.txtTranxDate = Nz(DMax("TranxDate", "tblInventoryDetails", "PartNum='" & Me.txtPartNumber & "'"), Date)
End If

Open in new window

But the way you explain it is you want to use today's date whether or not there were previous records for the part and then use the previous date for subsequent records.  If this was the case, the other records would use today's date as well.  This doesn't make sense to me.  Perhaps you can explain it a bit more.

Ron
0
 

Author Closing Comment

by:SteveL13
ID: 40593994
Worked perfectly.  Thank you.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40593998
You are welcome!

/gustav
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question