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
134 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 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

19 Experts available now in Live!

Get 1:1 Help Now