Results 1 to 4 of 4
  1. #1
    thegrimmerdiscovery is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    30

    Sequential Numbering

    Disclaimer


    I’ve been reading related posts and websites for supportwhenever time has allowed during the last week. I’ve seen much talk of DMAX+1and using autonumber, and not using autonumber, and SQL, all of which has nothelped. Basically, if you’re kind enoughto reply imagine you’re doing so in lovely big colourful crayons in an outrageouslyoutsized font and with the sort of language that benefits the psychologicallysubnormal.Essentially, if you’rereplying and it feels like you’re expounding the bleeding obvious - then you’reprobably pitching it just right.

    What I’m trying toachieve:

    To automatically populate a form field called RefNo in atable called tblMain (yes, that’s right) with the next lowest number availablefrom a range which begins at 1700 and ends at 1799. As you might imagine frommy disclaimer, this field in this table sits within the most basic databaseknown to man.

    However, sequences of these numbers, such as 1700:1710 and1780:1790 are unavailable (ie already allocated), as are isolated others suchas 1728, 1750 and 1777.

    The end goal of this is therefore that when I populate thenext form, its RefNo field would automatically populate with: 1711 (as 1700 to1710 are already in use).

    There is little risk of two people entering data at the sametime so please discount that from your thinking with whatever help you canoffer up. If this can’t be done by a novice like me. Would it be possible tocreate a second table with a list of these RefNos, maybe, and somehow transposethe next lowest one into my main table? Just a thought. No idea if that’sfeasible.

    Thanks in advance.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would put a button on your form (maybe label it 'get next ID' with some code attached:


    Code:
    for i = 1700 to 1799
        if dcount("*", "tblMain", "[RefNo] = " & i) = 0  then   'this is if refno is a number value
        'if dcount("*", "tblMain", "[RefNo] = '" & i  & "'") = 0 then  'this is if refno is a text value
            [Refno] = i
            exit sub
        Endif
    next i

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    another way which might be faster would be something like

    Code:
    function nextrefno()
    dim rs as dao.recordset
    dim sqlstr as string
    
        sqlstr="SELECT min(A.refno+1) FROM tblMain A LEFT JOIN tblMain B ON A.refno+1=B.refno WHERE B.refno is null and A.refno between 1700 and 1799"
        set rs=currentdb.openrecordset(sqlstr)
        if not rs.eof then nextrefno=rs.fields(0) else msgbox "run out of numbers"
    
    end function
    put this code in a module then to assign in your form

    me.refno=nextrefno()

  4. #4
    thegrimmerdiscovery is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    30
    Thanks both - it's education for me. Appreciated

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sequential Numbering using a query
    By Gina Maylone in forum Access
    Replies: 3
    Last Post: 06-19-2019, 11:33 AM
  2. Sequential Numbering based on Criteria
    By BNeff in forum Access
    Replies: 3
    Last Post: 05-17-2019, 02:55 PM
  3. sequential numbering with criteria
    By Harmoesh in forum Queries
    Replies: 1
    Last Post: 07-04-2018, 05:16 PM
  4. Sequential Numbering in a Form
    By Falafa in forum Forms
    Replies: 6
    Last Post: 10-05-2012, 08:20 AM
  5. Help with Sequential Numbering
    By orion in forum Programming
    Replies: 3
    Last Post: 07-06-2009, 01:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums