sequential alphanumeric order numbers (1 Viewer)

[Bob100]

Hi
MS-2003-I have created a simple customer, order database (4 tables in total) and populated it with 5000 records. The tblorder uses ordernumber as PK which is a text field with data in the form of COD000, COD001. I have now been asked to create an order form where the ordernumber automatically populates to the next sequential value. I am new to access with very little knowledge of code. I have used a query to increase a long integer field by 1 using max, but this doesn't work with text fields. So what would I put in the unbound ordernumber field in the form to do this and how would I make it automatic. Please help.

Many Thanks

 

[AccessBlaster]

You could have a text field with its default value set to "COD".
Then a "incrementing" text field.
Last you would need an unbound text field that would "concatenate" the fields together.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strCriteria As String
Me.[COLOR=red][B]YourField[/B][/COLOR] = Nz(DMax("[B][COLOR=red]YourField[/COLOR][/B]", "[COLOR=red][B]YourTable[/B][/COLOR]", strCriteria), 0001) + 1
End Sub

 

[MarkK]

Hey there, welcome to the forum.
- Notice that once you mix the number 001 with the text "COD" they are really difficult to unmix? So don't mix them, and that sort of makes sense when you think of a database as a place you store raw and uncooked data.
- Like a cabinet shop doesn't buy cabinets and then sell you cabinets. They buy plywood and laminate and glue and screws. If you want a cabinet they make it for you just how you want it when you want it.
- Data is like that. Store it raw. Whip it into shape and dress it up when you want to sell it to someone.
- Access will automatically increment an Autonumber field that you can use for a unique ID or OrderID. Then, if someone for some reason, wants to see an order number in Tahoma 10pt, Red text with "COD" in front and leading zero, flashing...then do that work at retrieval time or at presentation time.

SELECT "COD" & Format(tOrder.OrderID, "000") As OrderNumber
FROM tOrder

- Data: Store it raw in a cool dry place. Cook before serving, and no sooner.

For a field that increments automatically when you add a record, 
1) open a table in design view, 
2) create a field, 
3) call it OrderID, and 
4) for a data type, select Autonumber.

 

[Bob100]

Thanks for your advice. But I already have 5000 records from my boss with the order number already set as COD001. Should I try to remove the letters in excel and work from there? Import as a number and use format to add the "ORD".

 

[vbaInet]

You can still get that the number part of the text to increment by one by using the Mid() function within DMax(), i.e.:

"COD" & Nz(DMax("Mid([Field], 4)", "Table"), 0) + 1

However, when you reach COD999 how would you get the next number?

Thanks for your advice. But I already have 5000 records from my boss with the order number already set as COD001. Should I try to remove the letters in excel and work from there? Import as a number and use format to add the "ORD".

You can always run an Update Query to get rid of the text part using the Mid() fuction as above. Then follow what lagbolt prescribed.

 

[MarkK]

I already have 5000 records from my boss with the order number already set as COD001. Should I try to remove the letters in excel and work from there? Import as a number and use format to add the "ORD".

I'm not sure about the specifics of your situation.
- For instance, you say you have 5000 records with order number of COD001. They all have the same order number? Or they all conform to that format? Except then you can only have 999, as vbaInet observes.
- You ask where it is best to do the work reformatting data. Do that work where you are most comfortable doing it. I know Access very well, and Excel not so much. I would use Access.
- ...Format to add "ORD?" I thought you needed to add "COD"
Cheers,

 

[Bob100]

Sorry all, totally confusing myself now and making toooooo many errors. have approx 5000 records all with sequential COD0001 (missed out a zero). Forget ORD different field and problem.
Someone has suggested the following SELECT "COD" & Format(RIGHT(MAX(ordernumber),4) +1, "0000") FROM tblorders.
This is getting beyond me now. Where would I put this, its believe it must be sql must have never touch this area. I expect I start somewhere in modules. Should the ordernumber not be in [ ]??? Helppppppp!

 

[MarkK]

Bob, are you totally confused? You need to drive this process OK? I'm not exactly sure how much of this you understand now, so then I don't know what part to explain.
Maybe we need to back-track a little and do smaller simpler steps? What exactly do you have? How, exactly, do you want to change it? The more detailed and specific you are the less confusion there will be.
Cheers

 

[Brianwarnock]

I'm not going to jump in here to try and answer this as lagbolt can do the job better, but I would want the answer to 2 questions lagbolt has asked or implied.

do you want to change the current records from COD0001 to a rising number?
Can you, ie are you permitted , to change the current DB to split this field and implement the recommendations of post 3 ?

Brian

 

[Bob100]

Ok. Database made and populated. Building a form to capture order info which I will append to the order table. Only the ordernumber does not increment by 1 when I open the form with a macro. Next value being cod4332. Would like a solution which keeps the field as one. Can the problem be solved using a query with sql or writing sql somewhere in the order form, in the pretext box? this is the extent of my knowledge. I know this isn't the best design but can it be done and simply.

 

[Bob100]

Have been researching and found the following which I have changed but doesn't work.
Select "cod" & format(right(max(ordernumber),4) + 1, "0000") from tblorder
Tried this in expression builder in a query but no luck????.
Would it work. Can it be put in a query, if so what have I done wrong. If not where would I put it and how.

 

[Bob100]

Forgot to thank you all. It must be frustrating for you.

Bob.

 

[Bob100]

Success. Wow learnt a fair bit tonight. learnt where to put sql into a query, and that code above works. final solution turned out as:
select "COD" & format(right(max([orderNumber]),4) +1, "0000") AS expr1 From tblorder.
Access added [] and AS expr1.
Can know populate form field with answer on opening.
Any thoughts about the answer or ways to improve please reply.

many thanks
bob.

 

[Galaxiom]

There is a mixture of good and bad in most of the answers.

Firstly the advice about storing just the number part is very good. It is more efficient and very easily incremented.

However the suggestion of using autonumber is bad. Autonumber should not be relied on to produce a sequence so should not be used to produce any value that has meaning to the user. The DMax is a better way.

However DMax is not best practice. In a multiuser situation, two orders could get the same number.

It is very important when using DMax that the number is allocated when the record is saved. If you allocate the number with the BeforeInsert event then it is imperative that the record is immediately saved. Do not wait until the rest of the record is entered. The downside is that the number sequence will be skipped if the order is cancelled after another order has been created.

Alternatively use the BeforeUpdate event of the form to allocate a number as the full record is saved. This won't skip number sequence but users won't know the order number until the record is saved.

However, while it is not usally a problem in small databases with few users, in either scenario it is stilll possible for two near simultaneously created records to get the same number. The second record will get an error because of the primary key clash.

The danger period is the time it takes to perform the DLookup and increases as the number of records grows and by the use of the string value as the key instead of the number.

Best practice maintains the new number in a table and accesses via it a recordset that locks out other users while it is read and incremented.

Prefixing COD to the number for display via concatenation is clumsy.
Instead, prepend the string using the following in the Format property of the textbox:
"COD"0

The number section will show as many digits as the original so you will get COD1 and COD12 rather than COD0001 and COD0012 as your existing system.

To pad with zeros to four digits use:
"COD"0000

However it is rather irregular to use a system with padding to four digits and then continue with larger numbers as in COD0001 and COD10000. Normally one would stick to a either fixed number of digits padded with leading zeros or (less frequently) display the number in its original format.

 

[MarkK]

Bob, congrats on your success!

Galaxiom, I think that to say an autonumber is a uniformly bad choice for a meaningful ID is overstated.
A payoff of using an autonumber for an OrderNumber or PO number, is that it's dead simple. Create an autonumber field in a table and you are done. New records will very reliably assume the next ID in the sequence.
The cost, as I understand it, of using this approach is that you cannot add an ID of your choosing to a table, and once an ID is consumed it can never be reused, so if you cancel an insert or delete a record you have a gap in your sequential numering that you can never fill. Yes, this is unacceptable if every number in a sequence absolutely must be present, and in that case an autonumber can be said to be bad.
But where simplicity is desirable and an occasional gap in the numbering is acceptable, an autonumber is very good, and I think for Access users this is commonly the case.

 

[Galaxiom]

I think that to say an autonumber is a uniformly bad choice for a meaningful ID is overstated.

Overstatements abound in the Access developer community. In the case of autonumber, they range from just plain wrong ("every table should have an autonumber primary key") to this particular case. Personally I think saying that autonumber should be avoided for meaningful values is only very slightly overstated.

But where simplicity is desirable and an occasional gap in the numbering is acceptable, an autonumber is very good, and I think for Access users this is commonly the case.

Most design choices are a trade off but developers should be aware of the pros and cons of any decision. Using the best practice generally adds very little complexity. Many developers simply baulk at the learning curve and opt for the expedient solutions they invitably encounter in forums like this.

In small databases with few users, the simple solution works well enough but as the years pass and the jobs get bigger they forget the limitations and use it routinely on larger and larger systems. One day that DMax solution falls over by allocating two records with the same number or the autonumber seed corrupts.

Better to learn and impliment best practice as early as possible in one's career. This is why I often detail the reservations about the conventional wisdom of using autonumber or DMax + 1.

In the case of allocating a sequential number, the process can be easily contained in a function that actually simplifies the use in any new record procedures when compared to DMax + 1.

Create an autonumber field in a table and you are done. New records will very reliably assume the next ID in the sequence.

Unfortunately not always reliably. The autonumber seed can corrupt. When it reverts to a lower number, the table rejects new records and the seed must be reset, requiring urgent intervention by support personel. This is the main reason why I generally eschew autonumber whenever a suitable natural key, even a composite one, is available.

once an ID is consumed it can never be reused, so if you cancel an insert or delete a record you have a gap in your sequential numering that you can never fill.

Not quite. A record with an unused value in the autonumber field can be inserted into the table using a query.

 

[MarkK]

Most design choices are a trade off but developers should be aware of the pros and cons of any decision.
...
In small databases with few users, the simple solution works well enough ...

Those are exactly the points I am trying to make.
And thanks for the more detailed insights.
Cheers,

 

[gemma-the-husky]

bob100

not sure what you gathered from all the foregoing

the points are

a) - joining text and numbers together to make a single value means you cant add one to get the next number - although you can still get a max value.

b) arbitrarily splitting the text/number at the 4th character is OK - as long as you never decide to have a prefix longer than 3 chars, and as long as you never decide you need another prefix

c) you may have an issue anyway if you want to restart the sequence at another number

d) you will probably have an issue anyway when you reach order 9999 or 10000

e) splitting the order number into 2 fields, to yield a separate text prefix, and numeric value is a possibility, you could then change the numeric bit into an autonumber - but bear in mind the notes mentioned by others above regarding autonumbers.

f) another mechanism is to store the next number to be used in a separate table


In the end it's up to you to understand the do's and don'ts, and advantages/disadvantages of each idea - and decide how you want to implement this in your system

 

[DCrake]

Touching on what Dave has pointed to; you already have a table with 5000+ records that are prefixed COD. How long has it taken to get to this point? Reason being if it has not been long, it is not gpoing to be long before you run out of numbers 9999+.

Firstly consider the size of your text field, is it set to 7? If so, increase that to 10 for futureproofing.

Or simply convert the field to a number field and format the field contents acordingly

COD:"COD" & Format([CODNumber],"000000")


Next, and again already questioned is the prefix always going to be COD? if so, then why prefix the number in the field. Simply concat this onto the desired field when viewing it. Not only does it save space but also reduces the keystrokes a user will have to make to type it in a search box or combo box.

If you have different prefixes store the code of the prefix in a table and store the PK in the main table as a foreign key and use that.

Something along the lines of

DocumentNo:Choose(DocType,"ORD","COD","INV","GRN","PO","CR","ETC") & Format([CODNumber],"000000")

 

[TecknoFreak]

bob100

not sure what you gathered from all the foregoing

the points are

a) - joining text and numbers together to make a single value means you cant add one to get the next number - although you can still get a max value.

b) arbitrarily splitting the text/number at the 4th character is OK - as long as you never decide to have a prefix longer than 3 chars, and as long as you never decide you need another prefix

c) you may have an issue anyway if you want to restart the sequence at another number

d) you will probably have an issue anyway when you reach order 9999 or 10000

e) splitting the order number into 2 fields, to yield a separate text prefix, and numeric value is a possibility, you could then change the numeric bit into an autonumber - but bear in mind the notes mentioned by others above regarding autonumbers.

f) another mechanism is to store the next number to be used in a separate table


In the end it's up to you to understand the do's and don'ts, and advantages/disadvantages of each idea - and decide how you want to implement this in your system

Hello guys,
Sorry for asking in an old thread but this is basically the problem I have Based on that line above (d). Is there a fix for this?

I got this going on as I auto populate that ConfigNo based on another box selection

'------------------------------------------------------------
' add_new_record_Click
'
'------------------------------------------------------------
Private Sub add_new_record_Click()
Dim abc As String
Dim ab As Integer
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select MAX(ConfigNo) as maxConfigNo from [IAAIMS DATA ENTRY TABLE]")

ab = Right(rs!maxConfigNo, Len(rs!maxConfigNo) - 1) + 1

Me.txtMaxConfig = "C" & ab
   On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
'    Me.ConfigNo = "C" & abc
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
Me.ConfigNo = Me.txtMaxConfig
'Me.btnComplete.Enabled = True
  

End Sub