Creating your own Record ID number

[darbid]

Hello Guys,

The table has an ID number column which is an autonumber and the primary key. The form will be unbound. The database serves multiple users.

I have a column for a project number which follows someones elses logic (and another database) - being the year - a letter and then unique number with 5 digits - eg for this year - 2008X14578.

As people are familiar with this kind of ID I want to be able to give my users the ability to generate a similar ID number as well with the push of a button. Obviously the YEAR and the LETTER is not a problem.

The question is how to get the 5 digit number?

I thought maybe taking the next autonumber or somthing like that. If that is the best option is there a function to get this number or must I go to the last record and read this column value.

 

[WayneRyan]

darbid,

This is a pretty common topic; you can search here for "DMax" and "DMax + 1".

I'd suggest using an AutoNumber as your primary key.

Then for YOUR purposes, maintain three fields:

[TheYear] - Default = DatePart("yyyy",Date)
[TheLetter] - Whereever you get that from
[TheSequence] - Sequence # you'll assign

Then use your form's BeforeInsert event:

[TheSequence] = Nz(DMax("[TheSequence]", "YourTable", "[TheYear] = DatePart("yyyy",Date) And [TheLetter] = 'X'), 0) + 1

That'll give you sequence numbers starting 1, 2, 3 ...

When you use it:

[TheYear] & [TheLetter] & Format([TheSequence], "00000")

hth,
Wayne

 

[dkinley]

I'd suggest using an AutoNumber as your primary key.

Agreed. Use an autonumber as a primary key - it will save you headaches down the road. For the combination/hybrid number you want, use it for the human interface for hard copy filing/tracking etc.

-dK

 

[darbid]

Thanks to both of you for your comments especially for the DMax hint.

So for anyone interested...... for me this works

I have put this in a click event for a button on my form.
Me.[LuT Aktenzeichen] is the box where I want my ID number to appear.
VorgangID is a column name (Primary key) from a table called tbl_LuT Vorgang

Me.[LuT Aktenzeichen] = Format(Date, "YYYY") & "T" & Format((DMax("[VorgangID]", "tbl_LuT Vorgang") + 1), "00000")

given we are in 2008 and the last VorgangID was 435

This will produce right now "2008T00436"

Thanks guys

 

[dkinley]

Awesome! Thank you very much for posting your final solution.

-dK

 

[xerophreak]

Did this method also save the "ID" you generated into your table?

 

[darbid]

Did this method also save the "ID" you generated into your table?

If I understand your question properly, "Me.[LuT Aktenzeichen]" is a bound control and thus it is saved automatically into the table.

 

[xerophreak]

I'm not really all that familiar with Access, but I need to create a report ID that is separate from the unique ID. I just got thrown into this project and I've been able to wing my way through most of it. I can get it to display the correct value via

"=Format(Date(),"yymm") & Format((DMax("[ID]", "Calls") + 1), "00000")"

but I can't get it to save that value into a a field in the table.

 

[darbid]

I'm not really all that familiar with Access

I am not that experienced either. Welcome to beginners world.

but I can't get it to save that value into a a field in the table.

I assume that you have some form already. Follow what I wrote above and create a button and then on the click event of the button put your code there. At the same time create a new text box and bind this box to the field in your table that you want the ID to be kept.

 

[xerophreak]

I'm working on a database for a group in a Police Department, I would rather make this as easy as possible. Is there any way to do this automatically so that the user doesn't have to click anything?

 

[darbid]

yep, but you may run into other problems when it happens automatically,
for example forget creating the button just add the code to the load event of the form. Then every time they open the form a new number will be there.

 

[rapsr59]

HI!

Here's what I've got. It is called by another procedure that sends the last record number to it.

Public Function fncNextRecordID(strRecordNr As String) As String
    'This function returns the next string record number
    ' strRecordNr is sent by the calling procedure
    Dim intThisYear As Integer          'This years value
    Dim intYearSent As Integer          'Year value sent by calling procedure
    Dim strAlphaCharacter As String     'Holds the alphabetic character
    Dim strNumericSuffix As String      'Holds the alpha suffix characters
    Dim lngSuffix As Long               'Hold the numeric value of the suffix
    Const lngSuffixMax = 99999 'Maximum value of numeric suffix per alpha character
 
    'This code computes the next record number for a string that uses...
    ' 1. The four digits of the current year as a prefix
    ' 2. Followed by an alphabetic character (A thru Z) that will reset
    ' to A at the beginning of a year
    ' 3. Followed by a suffix comprised of a 5 digit number that will also
    ' reset at the beginning of year
    ' The code will allow for 2,600,000 unique record numbers
 
    '------------------------------------------------
    intYearSent = Val(Left(strRecordNr, 4)) 'Get the numeric value of year prefix
    intThisYear = Year(Date)                'Get the numeric value of this year
 
    'Compare if new year
    If intThisYear > intYearSent Then
        'This happens only once a year
        'Record Nr sent was from last year so return as first record this year
        fncNextRecordID = Right(str(Year(Date)), 4) & "A00000" 'YYYYA00000
    Else
        'Record Nr sent is this years so lets compute the next record nr.
        strAlphaCharacter = Mid(strRecordNr, 5, 1) 'Extract Alpha Character
        strNumericSuffix = Right(strRecordNr, 5)   'Extract Numeric Portion Of ID
        lngSuffix = Val(strNumericSuffix)          'Get the value of the suffix
        lngSuffix = lngSuffix + 1                  'Increment by one
        If lngSuffix > lngSuffixMax Then           'Check if over 99999
            strNumericSuffix = "00000"             'Reset to 00000
 
            'Increment alpha character to the next letter in alphabet
            strAlphaCharacter = Chr(Asc(strAlphaCharacter) + 1)
        Else
            strNumericSuffix = str(lngSuffix)      'Load the string
 
            'Remove the preceeding space -> Chr(32) <- from the number
            strNumericSuffix = Right(strNumericSuffix, Len(strNumericSuffix) - 1)
 
            'Pad with preceeding O's
            Select Case Len(strNumericSuffix)
                Case 1 'Pad with four preceeding zeros - 0000x
                    strNumericSuffix = "0000" & strNumericSuffix
                Case 2 'Pad with Three preceeding zeros - 000xx
                    strNumericSuffix = "000" & strNumericSuffix
                Case 3 'Pad with Two preceeding zeros - 00xxx
                    strNumericSuffix = "00" & strNumericSuffix
                Case 4 'Pad with One preceeding zero - 0xxxx
                    strNumericSuffix = "0" & strNumericSuffix
            End Select
        End If
        'Send the record number back to the calling procedure
        fncNextRecordID = Right(str(intYearSent), 4) & strAlphaCharacter & strNumericSuffix
    End If
 
End Function

Since your form does not have a record source you can use the following code in the forms Before Insert event and the form will compute the next record number when the uses begins to enter a record.

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblX", dbOpenSnapshot)
    With rst
        'Go the last record so the new Record Number can be computed
        ' using the previous record number for calculations
        .MoveLast
        'Compute and display the next record Nr
        Me.XControl = fncNextRecordID(!RecordNR)
    End With
 
    Set rst = Nothing
    Set dbs = Nothing
 
End Sub

Hope this helps you!


Richard