By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 855 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

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

P: 4
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
Feb 6 '12 #1
Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,485
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 :-)
Feb 6 '12 #2

NeoPa
Expert Mod 15k+
P: 31,485
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.
Feb 6 '12 #3

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. =DLast("UsageDate","Daily Magnet Usage Table") 
where "UsageDate" = field name and "Daily Magnet Usage Table" = table name.

Thanks again!
Michelle
Feb 6 '12 #4

NeoPa
Expert Mod 15k+
P: 31,485
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.
Feb 7 '12 #5

100+
P: 759
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.
Feb 7 '12 #6

NeoPa
Expert Mod 15k+
P: 31,485
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).
Feb 7 '12 #7

100+
P: 759
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.
Feb 7 '12 #8

NeoPa
Expert Mod 15k+
P: 31,485
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.
Feb 7 '12 #9

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Detail_Click()
  4.  
  5. End Sub
  6.  
  7. Private Sub UsageDate_BeforeUpdate(Cancel As Integer)
  8. Dim PKmax
  9. Dim LastDate
  10. PKmax = DMax("ID", "Daily Magnet Usage Table")
  11. LastDate = DLookup("UsageDate", "Daily Magnet Usage Table", "ID = PKmax")
  12.  
  13. End Sub
ID is my PK.
I put
Expand|Select|Wrap|Line Numbers
  1.  ="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
Expand|Select|Wrap|Line Numbers
  1. =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
Feb 8 '12 #10

NeoPa
Expert Mod 15k+
P: 31,485
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 8 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.