Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Custom Auto Field

    Hi there,

    The goal is to still have my normal primary key that goes up by 1, but I want another field that will look like ABC14-0001 and goes up by one every record. The 14 is the year and I would like it to reset back to ABC15-0001 but in October1st and so on every year.

    I have been looking for days and tried multiple sites, if any one can help me with this I would really appreciate it. I do not care how it is accomplished as long as it works=P.

    Edit: I would like it to show on the form

    Thanks for your time and help.
    Last edited by tarey8569; 05-30-14 at 11:41. Reason: left out important information

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    an outline of what should work. however you may need to think about record locks
    or trap fo a duplicate id ant hen get a new one, attemtp to rewrite the row.

    you shouldn't need an autonumber AND this ABC whatever. the ABC whatever is good enough to uniquely identify a row, which is all an autonumber column does.. whats more the ABC whatever is meaningful, whereas the autonumber has no meaning outside the system (database itself)

    what Id suggest yuou do is add a control to your form containing the abc ID. make it locked, remove the tab stop. in the forms BEFORE INSERT event (this fires after the user has started typing in a control in the new row place soem code to get the ID
    mycontrol.value = getnextid("ABC")

    ..bear in mind in the following you will need to change the DMAX so that it refers to the correct column and table

    you wil need tothink about how you handle errors
    eg null values, two users trying to secure a new ID a the same time. you could design some sophisticated process, or just go ugly early and trap for a duplicate key error (assuimg the ABC ID is made the primary key) and secure a new id and attemtp to write it until it succeeeds

    Code:
    Private Function GetNextID(Prefix As String) As String
    'gets a new ID for this row
    'the number sequence is XXXYY-NNNN, where
    'XXX is the prefix eg ABC
    'YY is the last 2 digits of the current financial year, which runs from 1st oct to 30th Sept
    '-is required
    'nnnn is a sequential number that resets at the start of the financial year
    'the YY bit comes from the financial year(which starts on 1st Oct, so all we need for the prefix is the first 3 characters
    GetNextID = "" 'set up our default return value, consumer code must check for this and take appropriate steps
    Dim TriggerDate As Date 'this stores the current financial year
    TriggerDate = CDate(Year(Date) & "/10/01")
    Dim Suffix As Integer
    'validations
    'well the prefix MUST be length 3 characters
    If Len(Prefix) = 3 Then
        'If IsString(Left(Prefix, 3)) Then 'no longer needed. if the prefix must be alpha then reinstate
        'ok so we have the valid bits
        'if the current system data is after 1st October modify the prefix
        If TriggerDate < Date Then 'we need to add a year to the value
            Prefix = Left(Prefix, 3) & Year(TriggerDate) - 2000 + 1 'this crappy design uses a two digit code for the year
        Else
            Prefix = Left(Prefix, 3) & Year(TriggerDate) - 2000 'this crappy design uses a two digit code for the year
        End If
        GetNextID = Nz(DMax("mycolumn", "mytable", "mycolumn like '" & Prefix & "*'"), "")
        If GetNextID = "" Then 'we didn't find anythign so this must be the first row for this prefix
            Suffix = 0
        Else
            If IsNumeric(Mid(GetNextID, 7, 4)) = False Then 'we have crap data....
            'so what do we do... tricky really. letrs weasel out of this and let tarey8569 decide shat he/she/it want to do
            Else
                Suffix = CInt(Mid(GetNextID, 7, 4))
            End If
        End If
        Suffix = Suffix + 1
        GetNextID = Prefix & "-" & Format(Suffix, "0000")
    Else 'prefix length was not 3 characters... someone is playing silly buggers
    End If
    End Function
    
    Private Function IsString(item As String) As Boolean
    'function no longer needed
    IsString = False 'set up our defualt return state
    Dim iLoop As Integer ' the loop pointer
    Dim NoChars As Integer 'the number of characters int he specified string
    Dim ThisCharacter As String 'the current character to be examined
    NoChars = Len(item)
    If NoChars > 1 Then
       For iLoop = 1 To NoChars
           ThisCharacter = Mid(item, iLoop, 1)
           Select Case Asc(ThisCharacter)
           Case 65 To 90  'then its upper case alpha
               IsString = True
           Case 97 To 122 'then its lower case alpha
               IsString = True
           End Select
       Next iLoop
    End If
    End Function
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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