Have the last date submitted be the default value for a new entry

[mfarley]

Hello there,
I'm new to access and I have a simple question. I'm writing a form in MS Access 2007 with a date field, I'd like the field to be populated by the last date entered instead of today's date. I saw that a similar question had be posted to your forum
Have access recall the last date entered in a form
and implemented the solution:
"In your Date Field Properties, under the Data Tab, set the
Default Value as follows: =DLast("[DateFieldName]","Table Name")"
The gave me a random date and not the last date entered. What am I doing wrong?

I thank you in advance for your help.

Best regards,
Michelle

[NeoPa]

No problem with the post indeed Michelle. In fact a fine job for a newcomer.

You're not even failing in the code, as the suggestion posted in the linked thread is not quite correct (for your requirement at least. I may check on it later to see if it holds for the thread it's in). What you need there is to use DMax() instead of DLast().

Let us know if that sorts out the problem for you :-)

[NeoPa]

It seems I may have been mistaken in my understanding of your request. I had assumed (probably presumtuously) that you wanted the maximum of all previously entered dates. If, as seems more likely, you want the most recently entered data - IE that entered into the system most recently - then DLast() may work for you (indeed it should in most cases). Can you give an example of behaviour that appears to be incorrect? It may be that DLast() will only work as you'd expect when processing through the records based on when the record was entered. I believe it actually uses whatever is the defined PK of the table in normal circumstances, which would work correctly if you have this set as an AutoNumber field, but apparently randomly if another field (or fields) is used as the PK.

Please fill in the details for us in order that we can be of more help.

[mfarley]

Hi NeoPa,
Thank you so much for your help with this (and such a fast answer). DMax() works well for this application since most frequently the date will be the maximum value. I implemented it and it works great.
For my own education though - I'd like to figure out why DLast() did not work. For this table I do have the PK as autonumber. The date is not the PK. By using DLast() I'm getting the date for record 31 even though there are 116 records, where 116 is the most recently entered record. Perhaps my syntax is wrong?
Here is what I wrote which works perfectly for DMax:

=DLast("UsageDate","Daily Magnet Usage Table")

where "UsageDate" = field name and "Daily Magnet Usage Table" = table name.

Thanks again!
Michelle

[NeoPa]

This is interesting. I looked up DLast() in Context-Sensitive Help and it started with the following :
You can use the DFirst and DLast functions to return a random record from a particular field in a table or query when you simply need any value from that field.

Clearly, the domain from which the first or last record is taken is not even defined as far as the order goes. Hence, the first and last records of this recordset are undefined.

PS. Please read [CODE] Tags Must be Used for when posting code.

[Mihail]

Another approach can be to maintain a table (if you use FE-BE database this table can be a local one in FE) where you can store the date value every time when this is entered.
This way you can use it between working sessions.
You can store a lot of other variables in this table.
Of course you need also to code a mechanism to extract a certain value when you need.

[NeoPa]

While this is certainly true Mihail, I see nothing to recommend the idea. Consider the benefits of such an approach, if you can, as there is none. It also relies on the project to maintain the data consistently with the data in the table. I suspect you're starting to see that the suggested approach is not Normalised (The database would maintain the same data in two different positions and would therefore be responsible for maintaining that link consistently).

[Mihail]

Not what I mean.
I say "table" but I think to a place where to store the last entered value for date (and other values if is needed).
No relationship between this table and the database.
Only a storage place.

[NeoPa]

Mihail:
No relationship between this table and the database.

I'm afraid that's simply not true. The data cannot be remotely useful unless this data exactly matches the specific data from the main table, and as such, it become denormalised. I see no benefit associated with extracting this data at a separate point in the process and maintaining it separately. It isn't necessary (as the information is already available in a straightforward manner) and simply causes exactly the extra overheads that Normalisation is designed to avoid.

I know you think I've misunderstood you Mihail, but it seems clear to me that I didn't and don't. Possibly you misunderstand my points maybe.

[mfarley]

Ok - I'm pretty sure I know how to do this but I'm getting lost on the last step. Bear with me - I just started using Access on Monday. I wrote this little code:

Option Compare Database

Private Sub Detail_Click()

End Sub

Private Sub UsageDate_BeforeUpdate(Cancel As Integer)
Dim PKmax
Dim LastDate
PKmax = DMax("ID", "Daily Magnet Usage Table")
LastDate = DLookup("UsageDate", "Daily Magnet Usage Table", "ID = PKmax")

End Sub

ID is my PK.
I put

 ="LastDate"

as the default value in the property editor of my UsageDate field. This gave an error. I'm obviously not quite understanding how code is implemented in access. Do you know what I'm doing wrong?

Thanks!

PS I tried doing

=DLookUp("UsageDate","Daily Magnet Usage Table","ID =(DMax("ID","Daily Magnet Usage Table"))")

as default value in the property editor but the DLookUp function didn't like me embedding DMax

[NeoPa]

Your code is fundamentally ok Michelle, but line #11 assigns the value to a local variable, instead of to the DefaultValue property of your control. I don't know what your control is named, so I'll just assume txtLastDate for my example :

Me.txtLastDate.DefaultValue = DLookup("[UsageDate]", "[Daily Magnet Usage Table]", "ID = " & PKmax)

Notice other changes too. Brackets around names, especially when they have embedded spaces. Also the forming of the filter to use the Value of the variable rather than it's name.