Results 1 to 10 of 10

Data Entry Form: auto-fill data for a new record based on the data entered in the previous record

  1. #1
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24

    Data Entry Form: auto-fill data for a new record based on the data entered in the previous record

    Hello, I am developing a payroll and record keeping database for my office and I am learning Access as I go.

    I have designed a data entry form where where the user will enter information from an employee's time-sheet. I have a 'DateWorked' field and generally we are entering 50-70 records for everyday. To eliminate the repetitive task of re-entering the same date for all 50-70 records I want to design the form to auto-fill the 'DateWorked' field based on the last entry. This way the user will only have to enter/change the 'DateWorked' date when they begin entering records for the next day.

    I have come across this solution: http://access.mvps.org/access/forms/frm0012.htm by Dev Ashish but I have not had any luck with it and feel like I need someone to explain it more thoroughly (like as if I were a 5 year old).



    Thanks!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    Every control on your form has a "Default" value property. Dev's code simply sets this value to the current value so when it gets to a field with no value yet it will fill it. You know you are reinventing the wheel, right?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    Thanks for responding! I, unfortunately, don't have a working solution yet. I am also not sure how what I am trying to do is 'reinventing the wheel' - is there a more simple/direct way to get this done? If Dev's code is the way to get this done, I must be doing something wrong.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    What *exactly* are you doing. Do a Copy and Paste into a post.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    I have this set as a data entry form, in 'DateWorked' control's Default Value I have the following:

    const cQuote="""" 'Thats two quotes
    me!DateWorked.DefaultValue = cQuote & me!DateWorked.Value & cQuote

    I've played around with it and get various syntax errors and invalid string errors depending on what I change. When I do get past those hurdles, I have still been getting the #name? error on my form. I realize it is naive of me to just copy and paste and hope that it works but I'm learning as I go.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    No problem with learning. Do you have all of this:

    const cQuote="""" 'Thats two quotes
    me!DateWorked.DefaultValue = cQuote & me!DateWorked.Value & cQuote

    in 'DateWorked' control's Default Value?

    If so, it will not work. Put is in the AfterUpdate event of the Control and see what happens.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    Okay, I have it AfterUpdate now, no syntax error or #name? error, which is nice. but it still isnt doing what I would like and I am now getting the following error:

    "Microsoft Access cannot find the object 'const cQuote="""" 'Thats two quotes .... if "..." is a new macro group, make sure you have saved it and that you have typed its name correctly."

    I have verified that there are not any typing errors and it has been saved.

  8. #8
    ExtremeNovice is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    24
    Problem resolved. I was missing a super basic step (one so basic that it probably didn't have to be explained). But, for those who come across this with the same issue, the following code does work... but you have to enter it as code (duh, right?).

    in the control's AfterUpdate event click on the ellipse box (looks like [...]) and then select code builder and enter your code there, save & exit.

    const cQuote="""" 'Thats two quotes

    me!DateWorked.DefaultValue = cQuote & me!DateWorked.Value & cQuote
    Last edited by ExtremeNovice; 03-01-2017 at 10:14 AM.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    It sounds like you are good to go here. Do you want to use the thread tools at the top of the thread to mark this thread as Solved?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  10. #10
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,904
    Quote Originally Posted by ExtremeNovice View Post

    ...the following code does work... but you have to enter it as code (duh, right?)...
    Yes...code does have to be entered as code! You can't place it in the AfterUpdate event's box in the Properties Pane, which I'm guessing, from the error your received, is what you attempted!

    Lots of newbies make this mistake!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-05-2015, 07:34 AM
  2. Replies: 3
    Last Post: 02-25-2015, 04:48 PM
  3. Replies: 2
    Last Post: 12-18-2012, 11:41 AM
  4. Replies: 12
    Last Post: 12-06-2012, 05:36 PM
  5. Replies: 1
    Last Post: 12-21-2011, 02:11 PM

Posting Permissions

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