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
135 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 34

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

 

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

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.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now