Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    GDubbs780 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    10

    Help with autonumber

    New to access ... need help.



    I want to create a special quote number in a form, I want the number to autogenerate.

    The number is alphanumeric in the form "Q02-01-11"

    1)It always starts with Q.
    2)The "02" after Q is always the current month (in this case, February).
    3)The 01 is sequential (i.e. the next entry would be Q02-02-11 ...)
    4)The last number is always the current year (in this case, 2011).
    5)The sequetential number restarts at 01 for every new month.

    Any help would be great.

    My hope is that this number autogenerates in a form and automatically adjusts itself according to month year.

    Can this be done?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    I believe this link should help: http://www.baldyweb.com/CustomAutonumber.htm
    (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
    GDubbs780 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    10
    Ok....I see that it will work.

    But really, i have no idea where to put that in or how to enter it so it works for my situation....

    really new to access, please help.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    That really depends on your system. Is it multi-user? Do you want the user to see the value *before* the record is saved?
    (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
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    I found this kind of interesting, Allan, so here is what I think:

    on new records, use the vba behind the form:
    Code:
    "Q" & cstr(month(date())) & "-" & 
    
    DMAX("field", "table") & "-" & cstr(datepart("yyyy", date()))
    you don't have to use dmax. That's the part that's up to you. You have to get a MAX + 1 from the part of the custom number that relates to the next entry number.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    All true Adam but in what event "behind the form" do you put the code? It still needs answers to my questions to give a reasonably accurate and complete answer.
    (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
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    okie doke. Seeing that you're older than me, I'm 100% sure you know what you're talking about. Let us wait to see what this person brings back for us.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    Okay, we'll both wait.
    (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"

  9. #9
    GDubbs780 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    10
    Thanks guys.

    Yes, it is multi-user and my preference would be to see the number before the record is saved.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    Ahh Haa! They both complicate the issue. A value is not visible to other users until the record is saved but in this case the record is not saved until your form is complete. If you did not need the user to see the value while creating the record then the window of conflict between getting the old value and reserving the new value is quite small but still there. Since you want the user to see the new value then the window of conflict pretty much beging when the form goes to a new record and closes when you try and save the record. If another user was quicker at completing the form then your "new" number will be taken by the time you try and save the record. If you "take" the new value3 right away and then decide not to actually complete the new record, you will have gaps in the sequence. That is what an AutoNumber does. What is your pleasure?
    (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"

  11. #11
    GDubbs780 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    10
    Well, for my purposes it is more important to keep the window of conflict to a minimum. Seeing the number while completing the rest of the form is not important.

    ...maybe autonumber is not the best option because any gaps in the sequence pose major problems elsewhere ... maybe sequential without the autonumber is better although i dont know if that is possible or not.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    With your design requirements you would not be using an AutoNumber. You would be using the DMax() function on a field in the table. All of the issues are solvable with enough effort. The only issue with showing the user the value at the beginning is when you actually try to save it the value may need to be changed because of another user. If the current user records the value for some reason (on paper say) then when they check back they will be surprised because it is different.
    (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"

  13. #13
    GDubbs780 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    10
    Ok guys ... remember. i know absolutely nothing about programming ....

    This is what I have entered so far ... nothing happens:

    Private Sub Quote_Number_AfterUpdate()
    '"Q" & cstr(month(date())) & "-" & DMAX("Quote Number", "QuoteList Table") & "-" & cstr(datepart("yyyy", date()))
    End Sub


    Any suggestions?

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,899
    First you need to pick an event in which to put your code. I would suggest the OnDirty event since you do not want to Dirty the record unless some other value is being changed. Then you are going to want to check for an existing value first and not execute if it has one. Psuedo Code!!
    Code:
    If Len(Me.[Quote Number] & "") = 0 Then
       Me.[Quote Number] = TheOldOldValue +1
    End If
    If your incrementing value is buried within a text string rather that in a separate field then you will need to use a function like InStr() to extract the value.
    (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"

  15. #15
    GDubbs780 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    10
    Quote Originally Posted by RuralGuy View Post
    First you need to pick an event in which to put your code. I would suggest the OnDirty event since you do not want to Dirty the record unless some other value is being changed. Then you are going to want to check for an existing value first and not execute if it has one. Psuedo Code!!
    Code:
    If Len(Me.[Quote Number] & "") = 0 Then
       Me.[Quote Number] = TheOldOldValue +1
    End If
    If your incrementing value is buried within a text string rather that in a separate field then you will need to use a function like InStr() to extract the value.

    ...thanks. but really, i know NOTHING about writing code. This is the ONLY part of the database I'm building I can't figure out....

    A list exits in the form:

    Q02-01-11
    Q02-02-11
    Q02-03-11
    Q02-04-11
    Q02-05-11
    ...etc.

    I just want the database to automatically generate the next number in the sequence. It would be great, if I could bury this in a "on click" button where the user clicks a button, the code runs and puts the Quote number in the proper location.

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

Similar Threads

  1. duplicate autonumber
    By rayc in forum Access
    Replies: 5
    Last Post: 06-19-2013, 07:50 AM
  2. Filter on Autonumber
    By jgelpi16 in forum Programming
    Replies: 3
    Last Post: 01-24-2011, 10:46 AM
  3. Autonumber with Text
    By jgelpi16 in forum Access
    Replies: 6
    Last Post: 01-21-2011, 02:36 PM
  4. Controlling autonumber
    By Patience in forum Access
    Replies: 3
    Last Post: 06-22-2010, 04:11 AM
  5. AUTONUMBER
    By J A F F A in forum Database Design
    Replies: 1
    Last Post: 10-03-2007, 10:30 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
  •  
Tech Forums: Microsoft Office Forums