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.

Ms Access - Auto Number Start

Discussion in 'Business Applications' started by flippernuggen, Jul 28, 2006.

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

    flippernuggen Thread Starter

    Joined:
    Jul 28, 2006
    Messages:
    5
    I am trying ti get an auto number to start at 05-0001. I read a previous post asking a similar question and the answer was to put the number into format. However when I put this number it gets all goofy. The 05 needs to be for the year. I can start with 06 and just archinve the old spreadsheet, however I still have the same issue. And once 07 comes how to I adapt to that??

    Rik:confused:
     
  2. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    I have written a previous post for this where the Number is updated by VBA rather than an Autonumber.
    A formatted Autonumber can't respond to changes in the Year, whereas VBA can.
    See this Post on the VBAX Forum
    http://www.vbaexpress.com/forum/showthread.php?t=8560
    if that doesn't meet your requirements let me know.
     
  3. flippernuggen

    flippernuggen Thread Starter

    Joined:
    Jul 28, 2006
    Messages:
    5
    I found the post but I have 2 questions for you. 1. what exactly would I adjust to make it in the format 05-0001 vice the 2005-etc. 2. Where is the tab On Current I can not find it on my EVENT tab anywhere.

    Rik

    P.S. is there any way to auto create an email using Outlook Express when a new Request is submitted.
     
  4. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    The On Current event procedure is on a Form's Properties.

    The code below has been adjusted to suit your 06-0001 requirement, it only needed the 4 in the brackets changed to a 2.

    Private Sub Form_Current()
    Dim recordyear As String, zeroes As String, rs As Object
    zeroes = "0000"
    If Me.NewRecord Then
    Set rs = Me.Recordset.Clone
    rs.MoveLast
    recordyear = rs.requestId.Value
    rs.Close
    If Left(recordyear, 2) <> Right(Date, 2) Then
    Me.requestId = Right(Date, 2) & "-0001"
    Exit Sub
    Else
    recordyear = Str((Right(recordyear, 2)) + 1)
    recordyear = Right(recordyear, (Len(recordyear) - 1))
    If Len(recordyear) < Len(zeroes) Then
    recordyear = Left(zeroes, (Len(zeroes) - Len(recordyear))) & recordyear

    End If
    Me.requestId = Right(Date, 2) & "-" & recordyear
    End If

    End If
    End Sub

    Did you download the database from the post to see it working?
    Did you want to reset the count each year as that user did?
     
  5. flippernuggen

    flippernuggen Thread Starter

    Joined:
    Jul 28, 2006
    Messages:
    5
    I tried to dload it but I had issues it would not open it kept saying it was not on my CPU for some reason. Yes I would like the count to restart each year. I really appreciate all your help. I can not find the On Current tab anywhere all I have under events is, Before Update, After Update, On Dirty, On Undo, On Change, On Enter, On Exit, On Got Focus, On Lost Focus, On Click, On Dbl Click, On Mouse Down, On Mouse Move, On Mouse Up, On Key Down, On Key Up, On Key Press. That is all that is under the Event Tab, Is it poss that I do not have a setting somewhere else correct to make the ON Current Appear?? Would it help if I was to Email you he Form???

    Rik
     
  6. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Rik, I have privately mailed you my email address and included instructions if you want to post it on this thread.
     
  7. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Rik, I have created your code.
     
  8. flippernuggen

    flippernuggen Thread Starter

    Joined:
    Jul 28, 2006
    Messages:
    5
    did you email it to me or are you going to post it on here?? I really appreciate all your help on this.

    Rik
     
  9. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    I was waiting to make sure that I had the right field, I will email it to you.
     
  10. flippernuggen

    flippernuggen Thread Starter

    Joined:
    Jul 28, 2006
    Messages:
    5
    cool thanks a million again for your help
     
  11. 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/487306

  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