Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
137 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

791 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