Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58

    incrementing date on new record in a form

    Please help: I am not an experienced Access user. I have searched the internet for an answer but so far have not found anything that works for something which I think must be simple.
    I have a form for recording daily fishing catches. As there are several fields to fill in I would like the [Catch Date] field to default to the next date - i.e. date of previous record + 1. I haven't found a way of doing it with macros or with expressions, and have tried using the DMax event procedure in both the Form and the Fields properties but either nothing happens at all or I get error messages.


    I would be very grateful for suggestions.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    One method is to set the DefaultValue property of the date textbox: http://access.mvps.org/access/forms/frm0012.htm

    In your case, use the BeforeUpdate event of textbox or AfterUpdate event of form.

    Me.textboxname.DefaultValue = Me.textboxname + 1

    Default unit when calculating with date value is days so probably don't need DateAdd function to add the 1 day increment.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Thanks June. I tried this yesterday and again today but I get the message "Method or data member not found". Here is the code I used:

    Private Sub Catch_Date_BeforeUpdate(Cancel As Integer)
    Me.Catch_Date.Default Value = Me.Catch_Date + 1
    End Sub

    The error message highlights the second reference to Catch_Date.
    Have I got the syntax wrong somewhere?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Remove the space in Default Value so it is DefaultValue.

    I am testing this now because never done it before. The code is constructing the date value but for some reason the DefaultValue property isn't taking it properly.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Quote Originally Posted by June7 View Post
    Remove the space in Default Value so it is DefaultValue.

    I am testing this now because never done it before. The code is constructing the date value but for some reason the DefaultValue property isn't taking it properly.
    I have just tried it - and nothing happens! Tearing my hair out as it seems like it would be very simple...

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This works for me.

    Me.Catch_Date.DefaultValue = "#" & Me.Catch_Date + 1 & "#"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    My apologies: something is happening - it is putting in a random date of 30/12/1899...as if it doesn't know which date to add on to

  8. #8
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Okay, this is what is happening: firstly it moved the date forward on the first record (to 24/01/2013)and in the Default Value of the Properties it read as the correct date. However, it is portraying that date on the form as 30/12/1899. Secondly it is remaining at 24/01/2013 for all subsequent records...

  9. #9
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    HOORAY! Yes, it works! Thank you so much June.

    I have another rather more complicated, but similar issue on the same form. (But June, you have done your bit so please don't feel obliged to answer)

    A fishing vessel sets 1, 2, 3 or (rarely) 4 fishing lines a day. Each is numbered sequentially. So, if 2 lines are set on Monday they might be 5000 and 5001 and if 3 lines are set the next day they will be numbered 5002,5003,5004 (etc.)
    I have 3 fields: [Line Number 1], [Line Number 2] and [Line Number 3]. I have set an AfterUpdate event on [Line Number 1] so that Line Numbers 2 and 3 automatically increment their number by 1 (and so that if there are, for example, only 2 lines set that day [Line Number 3] stays blank).
    What I don't know how to do is to set an event so that Line Number 1 increments by 1 from the last Line Number in the previous record. I expect it to be a DMax event but I am not sure how to make the statement refer to the highest number of the previous record whether that is stored in [Line number 1], [Line Number 2] or [Line Number 3].
    I am grateful for any pointers and apologize for my profound lack of knowledge....

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Multiple similar-name fields indicates a non-normalized structure. That complicates your numbering scheme and will require some creative design to manage this. How will the code know how many lines were set? User inputs a number and then have code automatically assign sequence ID?

    Possibly use a 'counter' unbound textbox in the form header section to hold the next available ID. Also, code in form Open event to set the initial counter value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Quote Originally Posted by June7 View Post
    Multiple similar-name fields indicates a non-normalized structure. That complicates your numbering scheme and will require some creative design to manage this. How will the code know how many lines were set? User inputs a number and then have code automatically assign sequence ID?

    Possibly use a 'counter' unbound textbox in the form header section to hold the next available ID. Also, code in form Open event to set the initial counter value.
    Thank you June7: obviously I have to think about it a bit more if there is no clear solution. Maybe I should just accept typing in the first number in each record!

  12. #12
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    The clear solution is to Normalise as suggested by June.

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with the others to Normalize your tables. I am curious as to why line number is so important to you. Could you please tell us why line number is important?

    You might want to post a jpg of your tables and relationships so that we have some familiarity with your database.
    Good luck with your project.

  14. #14
    cfljanet is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    58
    Quote Originally Posted by orange View Post
    I agree with the others to Normalize your tables. I am curious as to why line number is so important to you. Could you please tell us why line number is important?

    You might want to post a jpg of your tables and relationships so that we have some familiarity with your database.
    Good luck with your project.
    Thank you Orange. It is a very good question that you ask and in actual fact it is not that important. It is declared to us every day by the vessel in the Daily Catch Report and I merely wanted to reflect what gets reported to us - but it is not critical data. I will simply delete those fields as I have about 15 years worth of data that doesn't include the line numbers anyway and it has never been an issue. Long story! Thank you all for your help though.

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm not suggesting that you delete anything. Readers only know what you tell us, and some times very little is said. Some comments raise curiosity and sometimes the answers are important to general understanding.
    Most issues with database arise from unnormalized or inconsistent data structures. So Normalization is very important. But at this point, I wouldn't suggest deleting records or fields.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-10-2012, 05:14 PM
  2. Replies: 4
    Last Post: 07-27-2011, 09:25 AM
  3. Replies: 1
    Last Post: 07-27-2011, 09:19 AM
  4. Incrementing Numbers
    By anastazia1117 in forum Access
    Replies: 3
    Last Post: 07-13-2011, 03:44 PM
  5. Incrementing a value in a form
    By erbuchan in forum Access
    Replies: 10
    Last Post: 01-26-2011, 12:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums