Sequential Order Number with Alphanumeric Value (1 Viewer)

manix

Registered User.
Local time
Today, 12:10
Joined
Nov 29, 2006
Messages
100
Dear All,

I know that there may have been many posts on this and I have searched for a total of about 2 hours in these forums before posting, so please don't shoot me down for asking this.

I simply need to know how I can create a unique, alphanumeric order number. I was going to use Autonumber, but from what I have read on these forums, that is not the way to go, as Autonumber is simply to identify the record.

What I need is an number like so: ST000. It needs to start at ST142 and increase by one each time. Obviously I would like to avoid gaps from records being deleted or not completed etc.....

I really have no clue how to do this, and would appreciate it if anyone can point me to a post that would help, or offer me the solution.

Thanks,
 

Dennisk

AWF VIP
Local time
Today, 12:10
Joined
Jul 22, 2004
Messages
1,649
Hi Manix,

I posted code last week to do this very thing.
What you require is table that has one column which contains the next number to be allocated.
Then you require a function that will return the next number (you can set up any strings in this function.
The function would be called as the default value for the key column.
If the new record is cancelled you would lose that number and create gaps -

Here is the function

Option Compare Database
Option Explicit

Declare Sub Sleep Lib "kernel32" (ByVal dwMS As Long)


Public Function GetNextAutoNumber() As String
' Function to Emulate Autonumber Generation
' Generates err 3262 if table is locked
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblAutoNumber", dbOpenTable, dbDenyRead)

rst.Edit
rst!AutoNumber = rst!AutoNumber + 1

GetNextAutoNumber = Right$("0000" & rst!AutoNumber, LEN_OF_CASENOTE_NUMBER)

rst.Update

Clean_Exit:
rst.Close
db.Close
Exit Function

Error_Handler:
If Err = TABLE_LOCKED Then
' Pause and try again
Sleep 20
Resume ' try to open the table again
Else
MsgBox "Error " & Err & " " & Err.Description
Resume Clean_Exit
End If

End Function
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:10
Joined
Feb 28, 2001
Messages
27,001
Obviously I would like to avoid gaps from records being deleted or not completed etc.....

Not so obviously, this is impossible to do completely. A "not completed" case can be an issue. So can a deleted record. This is because, according to Omar Khyam, "the moving hand writes and, having writ, moves on." (Or, according to the Old Programmer, "the moving finger gooses ....")

If you allow DELETIONS of records from the past, you will have gaps where a deletion occurred. This is why some systems NEVER delete certain classes of records.

If two users attempt to enter records at the same time, unless you are VERY careful, one of them will win and the other will get the next record number - or will get the shaft - because of key and locking considerations. Then, if the winner backs out, the loser has the next number, leaving a gap. Again, a possible source for gaps.

If you REALLY HONESTLY can't live with gaps, I strongly suggest that you give me time to invest in some stock in a company that makes antacids, because you will be taking them a lot. Gaps in numbering systems that are automagically generated are a fact of life. As my government supervisor used to say, "Get over it."

Seriously, re-think your design to see WHY you think you need monotonicity and continuity as properties of your numbering system. I've never seen a system yet that truly needed ID numbers to match that criterion. Now, I can understand a time-tag ordering requirement, but not ID numbers. They are at best arbitrary.
 

neileg

AWF VIP
Local time
Today, 12:10
Joined
Dec 4, 2002
Messages
5,975
I agree with the Doc Man. However, you can reduce the gap issues significantly if you allocate the sequential number as the last stage in creating the record. Abandoned records usually get abandoned before they are complete.
 

manix

Registered User.
Local time
Today, 12:10
Joined
Nov 29, 2006
Messages
100
Thanks guys for taking the time to reply to my thread. I understand now that gaps may appear and to be honest it is not a major issue. I would like everything to be driven off the database it self (for example the order is printed from the database as a report rather than the user manually taking the number and recording it on a manual order!) So gaps in the numbering will be ok. No records will be deleted by users, that for sure.

Anyway I am just using an autonumber, which I will use an appended query to start at the number I want. It works and I think it will continue to work with minimal interference. I am still green with this at the moment (sometimes RED :mad: ) so I am sure you will all correct me if I am wrong.

If you REALLY HONESTLY can't live with gaps, I strongly suggest that you give me time to invest in some stock in a company that makes antacids, because you will be taking them a lot. Gaps in numbering systems that are automagically generated are a fact of life.

Never taken antacids and don't intend to start! SO sorry, you will have to find another investment opportunity :)

Thanks again for taking the time to reply!
 

manix

Registered User.
Local time
Today, 12:10
Joined
Nov 29, 2006
Messages
100
HI All,

I have used the code Dennisk kindly gave me (amended slightly), but I cannot get the full number to appear in my required field.

The table that contains the Autonumber, goes up everytime I enter new data in the other table, but the number that appears in the required table just goes 0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0 and so on...........

Basically the autonumber field in the autonumber table is incrementing by one each time I enter data into the required record that I want to use the autonumber, BUT it is not showing up in the record as the required number. For example: Autonumber field is currently at 2314, but the record just shows 4. Is this a formatting option in the code? Sorry I am stuck!

The code I am using is:

Code:
Option Compare Database
Option Explicit

Declare Sub Sleep Lib "kernel32" (ByVal dwMS As Long)


Public Function GetNextAutoNumber() As String
' Function to Emulate Autonumber Generation
' Generates err 3262 if table is locked

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblAutoNumber", dbOpenTable, dbDenyRead)

rst.Edit
rst!Autonumber = rst!Autonumber + 1

GetNextAutoNumber = Right$("0000" & rst!Autonumber, PROJECT)

rst.Update

Clean_Exit:
rst.Close
db.Close
Exit Function

End Function
 
R

Rich

Guest
Enter the first nummber manually into the table first, or write code to enable a start number to be entered
 

manix

Registered User.
Local time
Today, 12:10
Joined
Nov 29, 2006
Messages
100
Enter the first nummber manually into the table first, or write code to enable a start number to be entered

No writing the start number is not working, It is as if the first thre digits of the number are being ignored!?

I don't know how to stipulate a start number within the code, it was the reason for having an autonumber table to stipulate the start number.

Ahhhh! Why is it doing this!
 
R

Rich

Guest
I'd rather use DLookup on a query that's already picked the highest number and just add one to it or DMax your table and again just add 1 to it
 

manix

Registered User.
Local time
Today, 12:10
Joined
Nov 29, 2006
Messages
100
I'd rather use DLookup on a query that's already picked the highest number and just add one to it or DMax your table and again just add 1 to it

Ok I have successfully created a query that utilises Dmax and one that utilises Dlookup +1, but how do I get it to record into the field of choice whenever a new record is added?

I have tried adding the query to the default value of the field but it does not work. I am lost as to how I can utilise these queries!

Thanks again for your suggestions Rich :)
 

neileg

AWF VIP
Local time
Today, 12:10
Joined
Dec 4, 2002
Messages
5,975
You need to apply them in a form that you use to capture the data.
 

manix

Registered User.
Local time
Today, 12:10
Joined
Nov 29, 2006
Messages
100
You need to apply them in a form that you use to capture the data.

That's what I am trying to do...but I am unsure how or where to apply the query? I know I am missing something really obvious here! Is it applied to the forms events tab?

Sorry I am still learning this Access milarky!
 

BobJ

Kestrel - Crawley
Local time
Today, 05:10
Joined
Mar 22, 2007
Messages
47
lol ok im trying to follow what you guys are saying but err im kinda stuck now :>

i created the new table... but i dont understand how to implement the code where to put it and what to link it to etc :s if some1 could explain it simply id be very grateful lol
 

Users who are viewing this thread

Top Bottom