Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2010
    Posts
    11

    Unanswered: Create autogenerate ID

    Hi, I must first say that my Access knowledge is very limited. But recently got a job and they need me to make a database in access for leaflets from customers. I got most of it sorted, but the one I am struggling with is generating an ID for each Leaflet, which was requested to be alphanumeric along these lines 'REFA00000'. REF for reference, A for part of the generated ID and same for the numbers. So what they would like to happen when the digits reach A99999 it will go to B00001. Is this possible or will it need to be rethinked?

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    In your table create an autonumber column.

    Have a query that calculates your reference for you:
    Code:
    SELECT "REF" + CHR(65+INT(autonumber_column/99999)) + RIGHT("00000" & (autonumber_column mod 99999), 5)
    FROM myTable
    Use that query in all stuff that requires the reference number.

    EDIT - pasting removed a critical space!
    Last edited by pootle flump; 02-19-10 at 10:05.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2009
    Posts
    340

    Cool

    PF's advice is always good. ...but your post made me laugh as I see this request all the time....particularly having REF in front of each - - what a goofy idea - - might as well put that in a separate field or just use it as part of the label....

    my stock reply to these type requests is : if you use Autonumber it will be free - and then give them a higher quote for their typically harebrained numbering system format... - - and they almost always just go with autonumber.....

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Agreed. If it is to cross reference to paper work then it should be keyed not generated. But they often do want this sort of stuff. I quite like trying to use the inbuilt tools to spoof what they want (as above) rather than use homebuilt stuff. Apart from being more difficult & expensive it will inevitably be serial too.

    The real problem (and I'll bet you've seen this too NTC) is when they start asking about gaps in the numbers....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2010
    Posts
    11
    Thanks for the speedy response, its working like a dream

    Thanks again

  6. #6
    Join Date
    Feb 2010
    Posts
    11
    well i have come across a few problems when I added the query into a few forms, one was a list of the Leaflets and the other was the page where you add a new leaflet. On leaflet list page it would be showing 4 entries, but when i add the query there seems to be the same reference number for the 4 entries, like REFA00001 has 4 entries, REFA00002 has 4 entries etc. But when I remove the query from the form it goes back to normal.
    With add new leaflet form, normally it would be blank for the new data, but since adding the query it would show the first record.
    Sorry but I am still trying to get to grips with Access, only just started this type of database on Tuesday lol

    Thanks in advance

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cpthooker View Post
    I added the query into a few forms
    Please can you describe exactly what you did to achieve this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2010
    Posts
    11
    I went into record source of the form, added the query into it as well as the table with the rest of the information and then placed that onto the form

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please could you copy and paste here exactly what is in the form record source?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2010
    Posts
    11
    I assumed you meant the SQL statement

    SELECT REFGen.Expr1, LeafletList.*
    FROM LeafletList, REFGen
    ORDER BY REFGen.Expr1;

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - you have used an implicit CROSS JOIN, which creates a cartesian product (please look that up : ).

    SQL Joins
    You want to use the INNER JOIN syntax, in this link it is simply called JOIN. Edit your SQL and post back here.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also I shortcutted and didn't alias the column. Please add "AS meaningful_name" at the end of my SELECT clause to replace that awful "Expr1" that Access generated.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2010
    Posts
    11
    thanks I will try that now

  14. #14
    Join Date
    Feb 2010
    Posts
    11
    Right, I managed to convince them that alpha numeric was not needed and a numeric id would be much simplier. I have begun to create the ID but when I create a new leaflet it tells me that it would create a duplicate when finished. I am sure I have done it right but I wanted to make sure with you guys

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    'Validation code, if any, goes here

    If Me.NewRecord Then
    If RecordsetClone.RecordCount = 0 Then
    Me.LeafletID = "00000001"
    Else
    Me.LeafletID = Format(DMax("Val([LeafletID])", "AI2") + 1, "00000000")
    End If
    End If
    End Sub

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh lord no. What I produced is better than that.

    The issue is not actually whether or not it is numeric or alpha or dates or rabbits - it is the choice between:
    1) User input
    2) System generated (using inbuilt procedures)
    3) System generated (developer code)

    3) is a nightmare and is what you have written. What I produced was derived from 2) and is far preferable.

    What you are producing is what NTC was talking about. It is serial and will be buggy and timeconsuming to code.

    Why did what I produce not meet requirements?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •