Generating Order numbers (1 Viewer)

Groundrush · Dec 12, 2006

Anyone have have any suggestions on how to create personlised job numbers instead of relying on auto numbers?

At the moment I have to freetype them in when creating a record & I would like to work out how to automatically generate them.

maybe something like

1030:101
1030:102
1030:103
1030:104......and so on.

Any suggestions would be much appreciated

thanks.

Ron_dK · Dec 12, 2006

You might do a search on sequence number or so.
Or look for something like : autonumber DMax().
Pat Hartman posted a number of good suggestion on this DMax thing.

Hth

Groundrush · Dec 12, 2006

Thanks,

DMax is what I was looking for

Thanks to Statsman I found this to try out.

statsman said:
I downloaded this off another forum some time ago. The paper is yellow its so old but it works great.
I don't recall whose code it is but it works like a charm for year and sequence. If you recognize it please claim it.

(quote)

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeUpdate

Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. Its much easier to store and group them when they're stored separately.

Its also easier to format them in a query.

(end quote)

What happens is when you make your first keystroke on the form the year and sequence are entered automatically. So if your last entry was 2006 14 the next will be 2006 15.

KeithG · Dec 12, 2006

Dmax may cause you trouble if you have multiple users entering data at the same time. Why do you not want to use an autonumber?

Groundrush · Dec 12, 2006

KeithG said:
Dmax may cause you trouble if you have multiple users entering data at the same time.

Thats the last thing I need

this db will be split & shared amongst quite a few users, it's likely that users will create records at the same time.

Are there any other options?

Why do you not want to use an autonumber?

Because I need to generate order numbers with personalised prefixes & also need to start them off with 101, 102, 103...etc

1030:101
1030:102
1030:103
1030:104......and so on

I use the auto as a record count.

KeithG · Dec 12, 2006

You are probably going to have to stick with the auto number. You can always prefix the auto number fields with the "1080:" on a report. For example in a query column you could put

InvoiceNumber: "1080:" & [AutoNumber Field Name]

this way you still use the auto number and get your prefix. If you use dmax your users will receive an error stating "Duplicate primary key".

Groundrush · Dec 12, 2006

KeithG said:
You are probably going to have to stick with the auto number. You can always prefix the auto number fields with the "1080:" on a report. For example in a query column you could put

InvoiceNumber: "1080:" & [AutoNumber Field Name]

this way you still use the auto number and get your prefix.

That's how I originally had them, maybe to save any future problems I will leave it simple.

thanks

neileg · Dec 13, 2006

I don't agree with KeithG. Autonumbers will almost guarantee breaks in sequence with multiple users. It is usual to generate the order number as the last stage of creating and order, so that the time between using DMax and saving the record is reduced to a minimum and that you don't allocate numbers to abandoned orders. Adding an index to the order number field and setting it to no duplicates, will ensure that if there is a clash of users, you can use error handling to resolve the clash.

Groundrush · Dec 13, 2006

neileg said:
I don't agree with KeithG. Autonumbers will almost guarantee breaks in sequence with multiple users. It is usual to generate the order number as the last stage of creating and order, so that the time between using DMax and saving the record is reduced to a minimum and that you don't allocate numbers to abandoned orders. Adding an index to the order number field and setting it to no duplicates, will ensure that if there is a clash of users, you can use error handling to resolve the clash.

Intersting

I had it set to generate a number on the after update event of the form
I did notice that if you went back into the record after the no was generated the dmax function was replacing the previous assigned no with a new one each time an ammendmant to the record was made.
not tested it with multiple users though.

John.Woody · Dec 13, 2006

What I've done is create a field in my invoice table
InvoiceNo - Set Indexed to Yes No duplicates

and then use the following procedure to create the next number. Works in a multiuser enviroment too

Public Sub NextInvoiceNo()
On Error Resume Next

If IsNull(DMax("InvoiceNo", "Invoice")) Then
Forms!F_Invoice!InvoiceNo = 1
Else
Forms!F_Invoice!InvoiceNo = DMax("InvoiceNo", "Invoice") + 1
End If

Forms!F_Invoice.Refresh
If Err = 3022 Then
Forms!F_Invoice!InvoiceNo = ""
Forms!F_Invoice.Refresh
NextInvoiceNo
End If

End Sub

HTH

John

Groundrush · Dec 13, 2006

What if you decide to go back & amend a record?

I find that it overwrites the previous number with the next value when changes are made.

I need something that allow you to make any changes you like without changing the order no.

neileg · Dec 13, 2006

Include code to test to see if the order number is null. If it is, populate it. If it isn't, leave it alone.

John.Woody · Dec 13, 2006

My invoice form has a button to create the invoice. This checks to see if here is a value in the InvoiceNo field before it continues or not.

Groundrush · Jan 18, 2007

John.Woody said:
What I've done is create a field in my invoice table
InvoiceNo - Set Indexed to Yes No duplicates

and then use the following procedure to create the next number. Works in a multiuser enviroment too

Code:

Public Sub NextInvoiceNo() On Error Resume Next If IsNull(DMax("InvoiceNo", "Invoice")) Then Forms!F_Invoice!InvoiceNo = 1 Else Forms!F_Invoice!InvoiceNo = DMax("InvoiceNo", "Invoice") + 1 End If Forms!F_Invoice.Refresh If Err = 3022 Then Forms!F_Invoice!InvoiceNo = "" Forms!F_Invoice.Refresh NextInvoiceNo End If End Sub

John,

Thanks for that tip

I tested my db on a multyuser environment today & to my horror it caused problems

Using your solution solved it

Generating Order numbers (1 Viewer)

Groundrush

Registered User.

Ron_dK

Cool bop aficionado

Groundrush

Registered User.

KeithG

AWF VIP

Groundrush

Registered User.

KeithG

AWF VIP

Groundrush

Registered User.

neileg

AWF VIP

Groundrush

Registered User.

John.Woody

Registered User.

Groundrush

Registered User.

neileg

AWF VIP

John.Woody

Registered User.

Groundrush

Registered User.

Similar threads

Users who are viewing this thread