1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 Auto incremente a field

Discussion in 'Business Applications' started by SlowHnds, Feb 18, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    As a part of my data base I want to issue receipts for a charitable income tax donation.

    I want the field to increment by 1.

    I have it set to show the format year in the field ie. 2011000 but what I want it to do is assign the number consecutively by the year.

    So that when the year changes... 2011 to 2012 it changes the number to 2012xxxx amd increases the number by 1.

    the final 4 digits should start over from 0 each January 1.

    Attached in 2007 format and 2003 conversion for convenience.
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
  3. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    I downloaded the zip from post # 26.

    It doesn't work for me. I an entered dates and all the way across nothing changes. Is something special required to make it run? Save?

    I can't see any procedure code that would update the field so something is off. The autoexec macro is there. There is 3 queries. 1 table 1 form The event procedure is in the first field on the form but leaving it doesn't do anything.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Did you download the one from Post #17?
    When you enter a date and tab in to the next field it Increments and displays the next ActualFWCNumber
     
  5. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    I'll give it a try and get back, I have to go to a safety course today and won't be home until later, 8 hours or so.
     
  6. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    try again to attach the right file.

    I gave it a quick try didn't increment.

    Possible error with 2007 running 2003?

    Have to run course starts in 7 mins.
     

    Attached Files:

  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Did you set security to allow VBA?
     
  8. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    Course cancelled.

    I've been trying this for going on 5 hours now and I'm no farther ahead.

    Attached as 2003.

    Contains the query. Contains the VBA code with the fields renamed. But somehow I've gone astray.

    Is not updating. So I don't know close to going back to Excel and number it at the end of the year.

    And yes I do find VBA code intimidating as I don't have a clue what half of it means or what it does.
     

    Attached Files:

  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You appear to be trying to set the "DonateDate_FLD" field with the number added, which you can't do because it is a Date/Time Field and formatted to Long Date, not that the format makes any difference.
    You should be using the ReceiptNbr2_FLD field to hold new information.
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You had one of the VBA lines correct, but the one that sets the first value wasn't correct. Also the Query was not correct. I have fixed it.
     

    Attached Files:

  11. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    I was working on this last night for HOURS I did eventually get the query to eventually work but I didn't get the VBA code to work at all.

    In an effort to understand this could you clarify the code pieces, what they do / mean??

    If rs.RecordCount <> 0 And Left(rs![MaxOfReceiptNbr2_FLD], 2) = Format(Me.DonateDate_FLD, "YY"

    so if the record count is greater or less than zero but what does the rest of the statement do?

    and well basically all the lines of code.

    Other than for my own understanding this is solved.

    My next question will be how to pull one database into another.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The query only displays one record, which is the last one, so Left(rs![MaxOfReceiptNbr2_FLD], 2) means
    take the lefthandside (Left) of the last record (rs![MaxOfReceiptNbr2_FLD]) for 2 characters i.e 10 for 2010 and 11 for 2011 etc and then compare that to the Year part of the DonateDate_FLD field which is currently 11 for 2011.
    So if the recordcount is not zero AND the year parts of the date also match carry on with the rest of the code down to the Else statement, which has another option to reset the count back to 0001 for a new year.
     
  13. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    Ok, makes sense kinda sort of

    and I was able to modify it to show the receipt numbers as I needed them YYYY with 3 leading zeros

    It took me awhile but I managed it.

    I still feel that you did all the work. VBA is a totally foreign language to me.

    Thanks for all your help again OBP
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Quite a lot of VBA is actually in English, but a lot of it not, if you need any more help with it just let me know.
    Well done on modifying it to suit your needs.
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/981568

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice