?
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
Medium Priority
?
152 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 40

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 52

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 52

Accepted Solution

by:
Gustav Brock earned 2000 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 52

Expert Comment

by:Gustav Brock
ID: 40593998
You are welcome!

/gustav
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

612 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