Frustrated - Creating Auto Number

[Susanne]

I've tried so many times to get this without asking for help.

I have a number field containing the current date in yyyymmdd format (this
is being used as a reference number). Then, there is a customer number of
that day (we are taking orders) formatted 000 (first order of that day is
001, second is 002, etc.). So the order number will officially become
"yyyymmdd-001" (20081101-001) or even "yyyymmdd-025" (20081101-025). I want
the form on the "On Current" event to fill in the next number automatically.
The next day it would start back at 001 since the job number field will be
set to the new date.

This is one version of what I've tried:

Me.Job_CustomerNumber.DefaultValue = DMax("[Job_CustomerNumber]",
"tbl_Orders", "Job_Number = " & Job_Number, 0) + 1

I can't recall them all, but none of them work. I've tried doing a format
on the job_number as well as creating a new variable to have the Job_Number
set to to get the layout and answer I want.

Does anyone have any suggestions? If I need to explain further, please let
me know.

Thanks in advance!

 

[John W. Vinson]

I've tried so many times to get this without asking for help.

I have a number field containing the current date in yyyymmdd format (this
is being used as a reference number). Then, there is a customer number of
that day (we are taking orders) formatted 000 (first order of that day is
001, second is 002, etc.). So the order number will officially become
"yyyymmdd-001" (20081101-001) or even "yyyymmdd-025" (20081101-025). I want
the form on the "On Current" event to fill in the next number automatically.
The next day it would start back at 001 since the job number field will be
set to the new date.

This is one version of what I've tried:

Me.Job_CustomerNumber.DefaultValue = DMax("[Job_CustomerNumber]",
"tbl_Orders", "Job_Number = " & Job_Number, 0) + 1

I can't recall them all, but none of them work. I've tried doing a format
on the job_number as well as creating a new variable to have the Job_Number
set to to get the layout and answer I want.

Does anyone have any suggestions? If I need to explain further, please let
me know.

Thanks in advance!

Use the form's BeforeInsert event - the Current event fires even if you're
just browsing through existing records, NOT what you want.

Since your "order number" contains punctuation and is too big for a number,
then it should certainly NOT be a Number type field. If you insist on using
this "intelligent key" - a bad idea IMO, and only reasonable if you're keeping
consistant with a longstanding paper system - then you'll need two fields,
OrderDate and OrderSeq. You can combine them for display purposes:

OrderNo: Format([OrderDate], "yyyymmdd") & "-" & Format([OrderSeq], "000")

To populate them, simply set the DefaultValue property of OrderDate to

=Date()

and use code in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[OrderSeq]", "[Orders]", "[OrderDate] = Date()")) + 1
If iNext >= 1000 Then
MsgBox "Go home, too many customers came today", vbOKOnly
Cancel = True
Else
Me!OrderSeq = iNext
End If
End Sub

 

[Susanne]

Thanks. Actually, the numbers are being kept in 2 different fields. What I
was displaying is what I will display in the invoice, my fault for not
explaining it. I wasn't sure whether I should use a place holder (like 000)
or just use consecutive numbers (1, 2, 3 vs 001, 002, 003). For the business
this is for, it will be highly unlikely that they will get even 50 orders a
day, but I thought your comment (msgbox) was funny and something to think
about.

Thanks again.

I've tried so many times to get this without asking for help.

I have a number field containing the current date in yyyymmdd format (this
is being used as a reference number). Then, there is a customer number of
that day (we are taking orders) formatted 000 (first order of that day is
001, second is 002, etc.). So the order number will officially become
"yyyymmdd-001" (20081101-001) or even "yyyymmdd-025" (20081101-025). I want
the form on the "On Current" event to fill in the next number automatically.
The next day it would start back at 001 since the job number field will be
set to the new date.

This is one version of what I've tried:

Me.Job_CustomerNumber.DefaultValue = DMax("[Job_CustomerNumber]",
"tbl_Orders", "Job_Number = " & Job_Number, 0) + 1

I can't recall them all, but none of them work. I've tried doing a format
on the job_number as well as creating a new variable to have the Job_Number
set to to get the layout and answer I want.

Does anyone have any suggestions? If I need to explain further, please let
me know.

Thanks in advance!

Use the form's BeforeInsert event - the Current event fires even if you're
just browsing through existing records, NOT what you want.

Since your "order number" contains punctuation and is too big for a number,
then it should certainly NOT be a Number type field. If you insist on using
this "intelligent key" - a bad idea IMO, and only reasonable if you're keeping
consistant with a longstanding paper system - then you'll need two fields,
OrderDate and OrderSeq. You can combine them for display purposes:

OrderNo: Format([OrderDate], "yyyymmdd") & "-" & Format([OrderSeq], "000")

To populate them, simply set the DefaultValue property of OrderDate to

=Date()

and use code in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[OrderSeq]", "[Orders]", "[OrderDate] = Date()")) + 1
If iNext >= 1000 Then
MsgBox "Go home, too many customers came today", vbOKOnly
Cancel = True
Else
Me!OrderSeq = iNext
End If
End Sub

 

[NEC-2008]

Great "MsgBox" answer Mr. Vinson, I wish I had that problem...good laugh...

I've tried so many times to get this without asking for help.

I have a number field containing the current date in yyyymmdd format (this
is being used as a reference number). Then, there is a customer number of
that day (we are taking orders) formatted 000 (first order of that day is
001, second is 002, etc.). So the order number will officially become
"yyyymmdd-001" (20081101-001) or even "yyyymmdd-025" (20081101-025). I want
the form on the "On Current" event to fill in the next number automatically.
The next day it would start back at 001 since the job number field will be
set to the new date.

This is one version of what I've tried:

Me.Job_CustomerNumber.DefaultValue = DMax("[Job_CustomerNumber]",
"tbl_Orders", "Job_Number = " & Job_Number, 0) + 1

I can't recall them all, but none of them work. I've tried doing a format
on the job_number as well as creating a new variable to have the Job_Number
set to to get the layout and answer I want.

Does anyone have any suggestions? If I need to explain further, please let
me know.

Thanks in advance!

Use the form's BeforeInsert event - the Current event fires even if you're
just browsing through existing records, NOT what you want.

Since your "order number" contains punctuation and is too big for a number,
then it should certainly NOT be a Number type field. If you insist on using
this "intelligent key" - a bad idea IMO, and only reasonable if you're keeping
consistant with a longstanding paper system - then you'll need two fields,
OrderDate and OrderSeq. You can combine them for display purposes:

OrderNo: Format([OrderDate], "yyyymmdd") & "-" & Format([OrderSeq], "000")

To populate them, simply set the DefaultValue property of OrderDate to

=Date()

and use code in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[OrderSeq]", "[Orders]", "[OrderDate] = Date()")) + 1
If iNext >= 1000 Then
MsgBox "Go home, too many customers came today", vbOKOnly
Cancel = True
Else
Me!OrderSeq = iNext
End If
End Sub