Create an autonumber with First Character as Prefix (e.g. A000001) 2

[rodrunner79]

Hello everyone,

I have not been in this site for quite some time now. It seems that I only come here when I need something and always leave with a smile on my face because everyone is just very helpful and very professional. I am somewhat still intimidated to post a reply even if I think I have the answer to someone's question because someone always has a better answer; which I don't mean any harm by it, it just shows how intelligent members are in this site.

Anyway, again, I came here to post an issue that's been puzzling me and need help getting it resolve. I tried searching the older threads and other forums and can't seem to find the correct link to help me.

Here's my dilemna: I have a table that has a primary key that is 23 chars in length, the primary (unique) key is generated by concatenating several fields together, including a Julian Day function. This method creates a unique value for each record permanently, there will never be a time were a record is duplicated. Well, the problem is, I've been ask to change the field lenght from 23 chars to 6 chars max.

If I use Access autonumbering method, I will be limited to 999,999 records because that's the number of permutations that a 6 digit field can produce. Therefore, I need some type of routine that will generate an auto-value with one character prefix (e.g. A000001). To accomplish this, I created a sample database with 3 tables. First table (tblAutoNoPrefix) contains 3 fields: the first field called "ID: corresponds to the second field called "Prefix", and the third field "DefVal" indicates whether that record is the default value which will be use in a DLookup function.

The second table (tblTransactions_Permanent) will be use to append the data from the third table (tblTransactions_Temp). That's where the problem lies, I need a autonumbering scheme that look up the default value on the first table and assigns a sequential number to it by adding 1 to the last number use. But I don't know how to do it.

With that said, I am glad to be here as always and any help is appreciated.

-=True wisdom comes from knowing you know nothing.=-

 

[alvechurchdata]

As I understand it you're using a character prefix to extend the number of unique values but I'm not sure where the six-digit limitation comes from. If it's a matter of storage space then you could use a long as the ID and get 2,000,000,000 values and only use 4 bytes.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[rodrunner79]

First, thank you for your response.

I am aware of Access data types, especially Long Integer as I've used it countless times. The limitation is not on my side but rather at the party I am sending the information to. I am currently sending a 23-chars field along with other fields as a Purchase Order to a vendor. I expect the vendor to return the same unique id along with the shipping and invoice information attached to the records.

This will enable me to compare the original order against the what their sending back (shipping information). Without the unique ID sent back, I am unable to do this. Currently, the vendor only has 1 field available for mapping, which is limited to 6 characters. That's why I have to I have to produce a new unique id using combination of letters and numbers.

I apologize for neglecting to mention this previously.

Hope this made more sense.

-=True wisdom comes from knowing you know nothing.=-

 

[alvechurchdata]

Sorry about the misunderstanding.

You should be able to modify some code that I use to generate autonumbers. I keep a table (named "Key") which just holds the names of the tables to be autonumbered and the next number to be used for each table. When I need a new sequential number the GetNewID function locks this small table and finds the row for the table I'm working with. It then gets the NextID value and increments the value in the Key table ready for next time. Here's the code:

 Function  GetNewID(strTable As String) As Long
' Description..: Gets a new Primary Key
' Accepts......: The name of the table
' Returns......: A unique ID number

Dim rst As DAO.Recordset
Dim lngID As Long

'-- Open the recordset with Pessimistic locking
'-- gdbs is a public reference to this database

Set rst = gdbs.OpenRecordset(Name:="Key",
    Type:=dbOpenDynaset)
rst.LockEdits = True
rst.MoveFirst
rst.FindFirst "TableName = '" & strTable & "'"
If rst.NoMatch = True Then
  '-- Error handling goes in here.
Else
   '-- Get the next counter then update the table.
   rst.Edit
   lngID = rst!NextID
   rst!NextID = lngID + 1
   rst.Update
End If

'-- Close and shut the table
rst.Close
Set rst = Nothing

GetNewID = lngID

End Function

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[rodrunner79]

Hi Geoff,

Again, thanks for your replies. I took your suggestion and used your code. I did some modifications to it or more like enhancement.

With the modifications, your function will now add a letter prefix before the numbers when inserting the autonumber to a record. I also formatted the value of lngID to text so the result would look like this 00001, instead of just 1.

For example, when adding a new record, the autonumber will generate A00001 and next record would be A00002. In addition, I created another table that contains prefix A-Z. This will be use when the number 99999 has been reached and the prefix letter will change to the next letter. For example, A99999, next records will be B00000, B00001, and so on.

Here's the code:

Private Function fLastPKPrefix()
    ' Looks up maximum value of the field Prefix where the letter has been used.
    fLastPKPrefix = Nz(DMax("[Prefix]", "tblPKPrefix", "[Used]=" & True), 0)
End Function
Private Function fCurPKPrefix()
    ' Looks up minimum value of the field Prefix where the letter has not been used.
    fCurPKPrefix = Nz(DMin("[Prefix]", "tblPKPrefix", "[Used]=" & False), 0)
End Function
Function GetNewPKID(strTable As String) As String
    ' Description..: Gets a new Primary Key
    ' Accepts......: The name of the table
    ' Returns......: A unique ID number

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim lngNewID As Long
    
    Dim db1 As DAO.Database
    Dim rs1 As DAO.Recordset
    
    '-- Open the recordset with Pessimistic locking
    '-- gdbs is a public reference to this database
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblPKTable", dbOpenDynaset)
    
    rs.LockEdits = True
    rs.MoveFirst
    rs.FindFirst "TableName = '" & strTable & "'"
    If rs.NoMatch = True Then
    MsgBox "Contact your System Administrator immediately.", vbCritical, "MMIDS Error #: " & Err.Number
    Else
   '-- Get the next counter then update the table.
    rs.Edit
    lngNewID = rs!NextID
    rs!NextID = lngNewID + 1
    rs.Update
    End If

    '-- Close and shut the table
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    If Right(Format(lngNewID, "00000"), 5) = "00000" Then
        
    Set db1 = CurrentDb
    Set rs1 = db1.OpenRecordset("tblPKPrefix", dbOpenDynaset)
    rs1.FindFirst "Prefix = '" & fCurPKPrefix() & "'"
    rs1.Edit
    rs1!Used = True
    rs1.Update
    Set rs1 = Nothing
    Set db1 = Nothing

    End If
    
    GetNewPKID = fCurPKPrefix & Right(Format(lngNewID + 1, "00000"), 5)

    
End Function

The code works perfect, but there's a problem: it will only work if you're adding one record, such as in a form.

If you call the function in a query that appends data, it will assign the same NextID to all the records returned by the query. There has to be some way to correct this without using a Loop. I was thinking of declaring the records in the table/query to be appended as a collection and use the For Each...Next methods. But I've never done it before. Do you think it would work? If so, how do I write it.

-=True wisdom comes from knowing you know nothing.=-

 

[alvechurchdata]

Are you calling the query with some SQL similar to:

Select GetNewID("myTable") as ID, * ...

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

Add a dummy parameter to your called function, eg:

Function GetNewPKID(strTable As String, x As Variant) As String

And then use any field in the calling SQL, eg:
SELECT GetNewPKID("myTable", [any field])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rodrunner79]

Geoff: No, I'm calling the function in an action query. It looks like this:

INSERT INTO tblFtpOrdPrep ( UnqId, ReqId, ItmCd, 
                          ReqQty, UoM, LineAmt, AltUnqID )
SELECT [ReqID] & [DlvToLoc] & fJulianDay() & Format(Date(),"YY") 
	& Left([ItmCd],8) AS UnqID, lnkOlrRequest.ReqID, 
	lnkOlrRequest.ItmCd, lnkOlrRequest.ReqQty, 
	lnkOlrRequest.UOM, lnkOlrRequest.LineAmt, 
	GetNewPKID("OMAXID",[ItmCd]) AS AlternateID
FROM (lnkOlrRequest LEFT JOIN tblNaddInfo ON 
	lnkOlrRequest.DlvToLoc = tblNaddInfo.NaddId) 
	LEFT JOIN OMAX_CATALOG ON lnkOlrRequest.ItmCd = OMAX_CATALOG.[HMMS CODE];

PHV: Excellent! It works!
If you don't mind me asking, how does the dummy variable work? I'm not complaining, just wondering.

Thank you very much to the both of you. I praise you both for your fine work and dedication to help others.

I love this site.

-=True wisdom comes from knowing you know nothing.=-

 

[PHV]

how does the dummy variable work
The JetSQL optimizer evaluates a function without variable parameter only once, despite the number of rows concerned.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886