Re: I need a routine to generate a unique number for a key

From: Adrian Jansen (qqv_at_noqqwhere.com)
Date: 05/13/04 

Date: Fri, 14 May 2004 08:54:56 +1000

I use this routine to generate a variety of numbers used for orders,
invoices, etc

You need a table called tblSystemValues with the following fields:
ID - primary key
Type - Human readable description
Prefix - any text you want added to the front of the number
Format - string like "0000" for a 4 digit numeric
Number - the next number to be grabbed whenever you call the routine
you can use this to set also the starting number for the sequence

Public Function GetNextID(ID As Long) As String
'Pass the ID from call
'1 = Quote
'2 = Sales order
'3 = Purchase order
'4 = Debtors Invoice
'5 = Creditors Invoice

'returns the next quote/order/invoice number with correct prefix as a string
'prefix, format and current numbers held in the tblSystemValues table

Dim mySQL As String

Dim Getnum As Long
Dim Prefix As String

Dim DigFormat As String

mySQL = "SELECT tblSystemValues.ID, tblSystemValues.Type,
tblSystemValues.Prefix, tblSystemValues.Format, tblSystemValues.Number"
mySQL = mySQL & " FROM tblSystemValues "
mySQL = mySQL & " WHERE (((tblSystemValues.ID)= " & ID & "));"

    With CurrentDb.OpenRecordset(mySQL, , dbDenyRead) 'lock recordset for
reading while updating
    'this ensures two users cant get the same number

       If IsNull(!Prefix) Then
       Prefix = ""
       Else

        Prefix = !Prefix 'set the quote prefix
       End If

        DigFormat = Nz(!Format, "") 'set the format

            .Edit
                !Number = !Number + 1 'update to next number
            .Update
            Getnum = !Number 'and get it

        .Close

    End With

    GetNextID = Prefix & Format(Getnum, DigFormat)

End Function 

-- 
Regards,
Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
"Roddy" <anonymous@discussions.microsoft.com> wrote in message
news:c50b01c4389f$53cff670$a101280a@phx.gbl...
> Search access help or Miscrosoft Site for GUID
> This will give you a unique numbe
> >-----Original Message-----
> >Due to reasons too complex to explain I need a routine to
> >generate a unique number for a key.
> >
> >I cannot use the autonumber Key feature for this since I
> MUST know the number before I add records. I must also be
> able to jump sequences in certain circumstances.
> >
> >I envision a table with one row 'next_number' which will
> >be accessed with exclusive lock and updated before lock is
> >released.
> >Does anyone have reliable working code so I dont have to
> >reinvent the wheel?
> >Also what happens if two users hit this routine at the
> >same time, do I need a short 'wait and retry' loop.
> >I'm using ADO access in Access 2000. This is a multi-user
> >system.
> >
> >
> >.
> >

Relevant Pages

  • Re: serial numbering
    ... Add the macro and a button to a custom toolbar to call it, to the invoice ... Dim SettingsFile As String ...
    (microsoft.public.word.docmanagement)
  • Re: help please! reading text re post
    ... Dim Buffer As String ... get stuff like invoice date, ... Declare 3 integer variable (e.g. posi As Integer, t As Integer, ...
    (microsoft.public.vb.general.discussion)
  • Re: serial numbering
    ... Dim SettingsFile As String ... Dim iCount As String ... The next macro will allow you to reset the invoice number to the next number ...
    (microsoft.public.word.docmanagement)
  • How to Interrogate SQL Server Tables for Specific Values - Heres how to do it in MS Access
    ... I am looking for similar routine for SQL Server. ... Dim db As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field ... Dim t As String, strQuery As String ... On Error GoTo 0 ...
    (comp.databases.ms-sqlserver)
  • Re: I need to change all data in two fields
    ... the complet routine I'm doing. ... Dim stLinkCriteria As String ... DAO or DoCmd.RunQuery to run this sql statement. ...
    (microsoft.public.access.queries)