How to revise our numbering sequence?

[Guest]

Each time we log a new inquiry, using our "Main" form, a new ref number is
created automatically by means of the following code on the Before Insert
event:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Ref = Nz(DMax("[Ref]", "[Inquiries]")) + 1
Me.Dirty = False

End Sub

We started our system on 1st October last year and as it is 2007 we are
using a number sequence that started at 70001.

On 1st October we will want this to restart from 80001, for the 2008
financial year.

Should I simply go into the Inquiries table on 1st October and manually log
a dummy inquiry using reference 80000, so that when somebody adds the next
real inquiry the system will use 80001, and onwards from there?

Seems a bit crude but I guess it would do the job - or is there some problem
I haven't thought of, or any more "elegant" way of doing it??

Many thanks for your advice
CW

 

[John W. Vinson]

Each time we log a new inquiry, using our "Main" form, a new ref number is
created automatically by means of the following code on the Before Insert
event:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Ref = Nz(DMax("[Ref]", "[Inquiries]")) + 1
Me.Dirty = False

End Sub

We started our system on 1st October last year and as it is 2007 we are
using a number sequence that started at 70001.

On 1st October we will want this to restart from 80001, for the 2008
financial year.

What will you do in 2010? Not that far away!

Storing two distinct pieces of information in one field is less than ideal.

Should I simply go into the Inquiries table on 1st October and manually log
a dummy inquiry using reference 80000, so that when somebody adds the next
real inquiry the system will use 80001, and onwards from there?

Seems a bit crude but I guess it would do the job - or is there some problem
I haven't thought of, or any more "elegant" way of doing it??

That would work; a more automated approach would be

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim lngNext As Long
lngNext = (Year(DateAdd("m", 3, Date)) - 2000) * 10000
Me.Ref = NZ(DMax("[Ref]", "[Inquiries]", "[Ref] >= " & lngNext), lngNext) + 1
Me.Dirty = False
End Sub


Test it of course... and if you'll ever have more than 10000 inquiries in a
year, prepare for serious trouble.

John W. Vinson [MVP]

 

[Pieter Wijnen]

talk about the y2k problem on steriods <g>
Pieter

Each time we log a new inquiry, using our "Main" form, a new ref number is
created automatically by means of the following code on the Before Insert
event:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Ref = Nz(DMax("[Ref]", "[Inquiries]")) + 1
Me.Dirty = False

End Sub

We started our system on 1st October last year and as it is 2007 we are
using a number sequence that started at 70001.

On 1st October we will want this to restart from 80001, for the 2008
financial year.

What will you do in 2010? Not that far away!

Storing two distinct pieces of information in one field is less than
ideal.

Should I simply go into the Inquiries table on 1st October and manually
log
a dummy inquiry using reference 80000, so that when somebody adds the next
real inquiry the system will use 80001, and onwards from there?

Seems a bit crude but I guess it would do the job - or is there some
problem
I haven't thought of, or any more "elegant" way of doing it??

That would work; a more automated approach would be

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim lngNext As Long
lngNext = (Year(DateAdd("m", 3, Date)) - 2000) * 10000
Me.Ref = NZ(DMax("[Ref]", "[Inquiries]", "[Ref] >= " & lngNext), lngNext)
+ 1
Me.Dirty = False
End Sub


Test it of course... and if you'll ever have more than 10000 inquiries in
a
year, prepare for serious trouble.

John W. Vinson [MVP]

 

[JK]

CW,

This should work:

YrNum = (Year(Date) - 2000 + IIf(DatePart("q", Date) >= 3, 1, 0)) *
10000
Me.Ref = Nz(DMax("[Ref]", "[Inquiries]", [Ref] > YrNum), YrNum) + 1
Me.Dirty = False

Regards/JK


| Each time we log a new inquiry, using our "Main" form, a new ref number is
| created automatically by means of the following code on the Before Insert
| event:
|
| Private Sub Form_BeforeInsert(Cancel As Integer)
| Me.Ref = Nz(DMax("[Ref]", "[Inquiries]")) + 1
| Me.Dirty = False
|
| End Sub
|
| We started our system on 1st October last year and as it is 2007 we are
| using a number sequence that started at 70001.
|
| On 1st October we will want this to restart from 80001, for the 2008
| financial year.
|
| Should I simply go into the Inquiries table on 1st October and manually
log
| a dummy inquiry using reference 80000, so that when somebody adds the next
| real inquiry the system will use 80001, and onwards from there?
|
| Seems a bit crude but I guess it would do the job - or is there some
problem
| I haven't thought of, or any more "elegant" way of doing it??
|
| Many thanks for your advice
| CW
|
|

 

[Guest]

Many thanks, John. (Have been away so apologies for not acknowledging your
response before now!)
CW

Each time we log a new inquiry, using our "Main" form, a new ref number is
created automatically by means of the following code on the Before Insert
event:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Ref = Nz(DMax("[Ref]", "[Inquiries]")) + 1
Me.Dirty = False

End Sub

We started our system on 1st October last year and as it is 2007 we are
using a number sequence that started at 70001.

On 1st October we will want this to restart from 80001, for the 2008
financial year.

What will you do in 2010? Not that far away!

Storing two distinct pieces of information in one field is less than ideal.

Should I simply go into the Inquiries table on 1st October and manually log
a dummy inquiry using reference 80000, so that when somebody adds the next
real inquiry the system will use 80001, and onwards from there?

Seems a bit crude but I guess it would do the job - or is there some problem
I haven't thought of, or any more "elegant" way of doing it??

That would work; a more automated approach would be

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim lngNext As Long
lngNext = (Year(DateAdd("m", 3, Date)) - 2000) * 10000
Me.Ref = NZ(DMax("[Ref]", "[Inquiries]", "[Ref] >= " & lngNext), lngNext) + 1
Me.Dirty = False
End Sub


Test it of course... and if you'll ever have more than 10000 inquiries in a
year, prepare for serious trouble.

John W. Vinson [MVP]

 

[Guest]

JK, many thanks. I have been away so my apologies for not acknowledging your
response until now.
CW