Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    18

    creating a unique id for each record

    can anybody help ??

    what I need to do is to create a unique record id for each record. I realise that using autonumber should be avoided ... does anybody know of some vba code I could use to do the following

    read a table called customer id, which contains the last id used into a variable which I can increase by 1 if the user chooses to save the record.

    I want to then store the lastid on the record cocerned and then update the customerid table.

    0any help with this would be appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    In my sample database I put this in frmInv's InvNum control's DefaultValue property so that when one starts a new record it automatically sets the invoice number to be one higher than the previous high:

    =Nz(DMax("[InvNum]","tblInv"))+1
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Nov 2005
    Posts
    18
    had a quick look....
    its basically what I had in mind...
    do you know of some sample code which shows me how to

    open lastid_table in current database

    readvalue from lastid_table

    increase lastid_table + 1

    write away to lastid_table , [lastid] field

    sorry for the pseduo !!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    Quote Originally Posted by sprescott
    ......I realise that using autonumber should be avoided ...
    Maybe I'm missing something, but I don't agree with this

    Agree some application requirements may require a sequential value or some other formatting of a number (such as order number/ GRN etc) but in most cases an autonumber should be avoided?

    I would suggest that there is a rare requirement to create a different means of getting a system generated number, but there are very few and far between (possibly once or twice in an app, in one or two apps).

    Surely the whole point of the system generated number is to simply create a method of identifying uniques rows in a table, and it should have no intrinsic meaning.

    Effectively the link points to a special case where you are trying to create a uniques key which has a specific meaning.

    Personally I would contend that these special cases should be avoided - and usually can be. Providign you can demonstarte (prove) what happened tothe missing numbers than most accountants / auditors are quite happy - often this means notallowing deletions, but include some form of flag that indictaes if the row is valid.

  6. #6
    Join Date
    Jun 2005
    Posts
    102
    Quote Originally Posted by Teddy
    I'd like to see a sample of this done, do you have any sources Ted. I understand things better when I see them and see how they work.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ADO Sample:

    I use this ...
    Code:
    Function GetNextInvControlID() As Long
        On Error GoTo Err_GNICID
        
        Dim NextInvControlID As Long
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        NextInvControlID = -1
        
        SQLString = "SELECT [Next InvControlID] FROM BillingSettings;"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            NextInvControlID = TrgRecSet.Fields(0).Value
            TrgRecSet.Fields(0).Value = NextInvControlID + 1
            TrgRecSet.Update
        End If
        TrgRecSet.Close
        
        Set TrgRecSet = Nothing
        
        GetNextInvControlID = NextInvControlID
    Exit_GNICID:
        Exit Function
        
    Err_GNICID:
        
        MsgBox Err.Number & ": " & Err.Description
        GetNextInvControlID = 0
        Resume Exit_GNICID
    End Function
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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