If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Access > creating a unique id for each record

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
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.
Reply With Quote
  #2 (permalink)  
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:

J. Paul Schmidt, Freelance Web and Database Developer
Access Database Sample, Web Database Sample, ASP Design Tips
Reply With Quote
  #3 (permalink)  
Purveyor of Discontent
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? ***
Reply With Quote
  #4 (permalink)  
Registered User
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 !!
Reply With Quote
  #5 (permalink)  
Jaded Developer
Join Date: Nov 2004
Location: out on a limb
Posts: 12,296
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.
Reply With Quote
  #6 (permalink)  
Registered User
Join Date: Jun 2005
Posts: 102
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.
Reply With Quote
  #7 (permalink)  
Grand Poobah
Join Date: Sep 2003
Location: MI
Posts: 3,713
ADO Sample:

I use this ...
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
        NextInvControlID = TrgRecSet.Fields(0).Value
        TrgRecSet.Fields(0).Value = NextInvControlID + 1
    End If
    Set TrgRecSet = Nothing
    GetNextInvControlID = NextInvControlID
    Exit Function
    MsgBox Err.Number & ": " & Err.Description
    GetNextInvControlID = 0
    Resume Exit_GNICID
End Function
Back to Access ... ADO is not the way to go for speed ...
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On