Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Gilbert, AZ
    Posts
    6

    Unanswered: Autonumber Question

    I am currently building a Access database and one of the fields is for a control number, i am needing it to add more than just the number, eg. AN-00001, AN-00002 etc.. I have not found anyway to do this as of yet, I can set the number to the specified value I need, just cant get the format I need. If anyone knows how I would do this I would GREATLY appreciate it.

    V/R,

    Settite

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    theres know way of doing it with an auto number (that i know of at least but you can do a con for a form level creation

    first create a table with one field set to match the 'AN-0001' pattern
    and set the 1st and only record in that table to 'AN-0001'

    then on the add form put this code:

    Private Sub Form_AfterInsert()
    DoCmd.RunSQL "UPDATE [Key] SET [Key].[Key] = Left([key],3) & Right('000' & (Val(Right([key],4))+1),4);"
    End Sub

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("key")
    rs.MoveFirst
    key = rs("key").Value
    rs.Close
    End Sub

    i've also attached a database to demostrate the method
    Attached Files Attached Files
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Jan 2003
    Location
    Gilbert, AZ
    Posts
    6

    thank you

    I thank you greatly for your help.

    V/R,

    Settite

Posting Permissions

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