Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2019, 08:12 PM   #1
Kundan
Newly Registered User
 
Join Date: Mar 2019
Posts: 13
Thanks: 3
Thanked 0 Times in 0 Posts
Kundan is on a distinguished road
Auto Numbering

Can I make Access produce auto numbers of the following type:

I-17088
I-17089
I-17090
I-17091
I-17092
I-17093
I-17095
I-17096
I-17097
I-17098
I-17099

The prefix is I- and the number should have 5 digits.

Kundan is offline   Reply With Quote
Old 08-15-2019, 08:54 PM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,595
Thanks: 59
Thanked 2,435 Times in 2,335 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Auto Numbering

you need autonumber field.

on query:

this will start from I-0001:

Select autonumberfield, "I-" & Format(DCount("1","yourTable","autonumberField<=" & [autonumberfield]), "00000") As sequence From yourTable;

this will start from I-17088:

Select autonumberfield, "I-" & Format(DCount("1","yourTable","autonumberField<=" & [autonumberfield]) + 17087, "00000") As sequence From yourTable;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-15-2019, 11:16 PM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,822
Thanks: 107
Thanked 2,660 Times in 2,434 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Auto Numbering

In the table design, set the format for the autonumber field to something like "I-17"000.
The numbers stored will be displayed as I-17001,I-17002 etc though will actually be 1,2...

Bear in mind that once you reach I-17999 the next record will be shown as I-171000 not I-18000

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-15-2019, 11:38 PM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,850
Thanks: 411
Thanked 691 Times in 670 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Auto Numbering

If the prefix is always the same why have it in the number?, why not concatenate it with the number.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 08-16-2019, 12:03 AM   #5
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,349
Thanks: 20
Thanked 892 Times in 877 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: Auto Numbering

Hmm, maybe this will help...
https://regina-whipp.com/blog/?p=704
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 08-20-2019, 06:46 AM   #6
Zedster
Newly Registered User
 
Join Date: Jul 2019
Location: UK Warwickshire
Posts: 32
Thanks: 12
Thanked 0 Times in 0 Posts
Zedster is on a distinguished road
Re: Auto Numbering

I do this sort of thing quite a lot. I created a function to do this.

Code:
Public Function GetNextIndex(strPrefix As String, strTargetTable As String, strFieldName As String) As String


    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim strNumber As String
    Dim strNewNumber As String
    Dim lngMaxNumber As Long
    Dim intPrefixLength As Integer
        
    strSQL = "SELECT " & strFieldName & " FROM " & strTargetTable & " ORDER BY " & strFieldName
    Debug.Print strSQL
    intPrefixLength = Len(strPrefix)
    lngMaxNumber = 0
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    With rs
        .MoveFirst
        Do While Not .EOF
             If Left(.Fields(strFieldName), intPrefixLength) = strPrefix Then
                strNumber = .Fields(strFieldName)
                strNumber = Right(strNumber, Len(strNumber) - intPrefixLength)
                If lngMaxNumber < CLng(strNumber) Then
                    lngMaxNumber = CLng(strNumber)
                End If
             End If
            .MoveNext
        Loop
    End With
    
    lngMaxNumber = lngMaxNumber + 1
    strNewNumber = CStr(lngMaxNumber)
    
    'Now add extra "0" to front
    
    Do While Len(strNewNumber) < Len(strNumber)
        strNewNumber = "0" & strNewNumber
    Loop

    'now add prefix back
    strNewNumber = strPrefix & strNewNumber
    GetNextIndex = strNewNumber

End Function

Last edited by Uncle Gizmo; 08-20-2019 at 02:53 PM. Reason: Added Code Tags -
Zedster is offline   Reply With Quote
Old 08-20-2019, 10:00 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Auto Numbering

I would not use a format to concatenate the "I-". That will only confuse people. They will never be clear as to when to enter I-11111 or 11111

If I were to use a prefix, I would keep it in a separate field and print the two controls out close enough so that they look like 1 value.

What is the point of the prefix if it never changes?

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-20-2019, 02:28 PM   #8
usama_hawas
Newly Registered User
 
Join Date: Aug 2019
Posts: 10
Thanks: 2
Thanked 0 Times in 0 Posts
usama_hawas is on a distinguished road
Re: Auto Numbering

use vba and create function
then call it

usama_hawas is offline   Reply With Quote
Reply

Tags
auto numbering

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto numbering Keiath General 3 01-13-2012 10:08 AM
auto numbering ferhanz Tables 1 10-12-2009 01:49 AM
Auto Numbering opostal Tables 6 06-29-2009 09:47 AM
Auto numbering.....again Red Devil Tables 4 08-03-2007 07:28 AM
Auto numbering.... Red Devil Tables 2 08-02-2007 02:52 AM




All times are GMT -8. The time now is 12:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World