Serial Numbers again

[Guest]

Sorry if this is similar to any previous questions.
I have a table which contains.
Year 05
Month 12
Initials TMM

which when joined together generates
05/12/TMM, this part is simple

However, I want to add a serial number to the end

05/12/TMM/###

the serial number should start at 001 and increment by 1 until the end of
the month at which point it should restart at 001. Can anyone assist with
this?

 

[Guest]

Create a table named tblSeqNum with on Long field named SeqNum and a Date
field called RestartDate.

The default value for the serial number field on your form will be:
=DMax("SeqNum","tblSeqNum")

Also the format for your serial number needs to be 000 in the table, forms,
and all reports to show the leading zeros.

In the BeforeInsert event of your form you need the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum =
[tblSeqNum]![SeqNum]+1;"
DoCmd.SetWarnings True
End Sub

OK. This will increment the number. It's possible that if
two people are entering records at the same time that the numbers could get
out of proper sequence. It also does not take into account if someone
creates a new record then deletes it.

Now what to do about starting over at the beginning of the month..
Put the code below in the Form Open event of the input form.
It checks the RestartDate whenever the form is open and updates it if over
a month old. Watch out for word wrapping if you cut and paste.

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum = 1,
tblSeqNum.RestartDate = Now() WHERE Year([RestartDate]) &
Month([RestartDate])<Year(Date()) & Month(Date());"
DoCmd.SetWarnings True
End Sub

 

[Guest]

Hi Jerry, thanks for your lightning response, however, am I doing something
stupid? Ever likely I might add.

Have done exactly as you suggest with the following code in the right places

Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum=[tblSeqNum]![SeqNum]+1;"
DoCmd.SetWarnings True

End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum =
1,tblSeqNum.RestartDate = Now() WHERE Year([RestartDate]) &
Month([RestartDate])<Year(Date()) & Month(Date());" 'This is all 1 line
DoCmd.SetWarnings True

End Sub
(Copied and pasted from the database)
But the serial number fails to increment on opening the form?

I am using Access 2000, does this make a difference?

Tables as you suggested tblSeqNum
Long integer Field [SeqNum] formatt 000
Date field [RestartDate]

Create a table named tblSeqNum with on Long field named SeqNum and a Date
field called RestartDate.

The default value for the serial number field on your form will be:
=DMax("SeqNum","tblSeqNum")

Also the format for your serial number needs to be 000 in the table, forms,
and all reports to show the leading zeros.

In the BeforeInsert event of your form you need the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum =
[tblSeqNum]![SeqNum]+1;"
DoCmd.SetWarnings True
End Sub

OK. This will increment the number. It's possible that if
two people are entering records at the same time that the numbers could get
out of proper sequence. It also does not take into account if someone
creates a new record then deletes it.

Now what to do about starting over at the beginning of the month..
Put the code below in the Form Open event of the input form.
It checks the RestartDate whenever the form is open and updates it if over
a month old. Watch out for word wrapping if you cut and paste.

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum = 1,
tblSeqNum.RestartDate = Now() WHERE Year([RestartDate]) &
Month([RestartDate])<Year(Date()) & Month(Date());"
DoCmd.SetWarnings True
End Sub
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry if this is similar to any previous questions.
I have a table which contains.
Year 05
Month 12
Initials TMM

which when joined together generates
05/12/TMM, this part is simple

However, I want to add a serial number to the end

05/12/TMM/###

the serial number should start at 001 and increment by 1 until the end of
the month at which point it should restart at 001. Can anyone assist with
this?

 

[Guest]

Hi,

It won't increment or show the number until the new record is saved. That's
why I said to put it in the BeforeInsert event. That's the split second
before the record is saved. This is good for a couple of reasons. If you
created the number when you first start a new record and then someone decides
not to use it, either a mostly blank record will be created or there could be
a missing number. Also if more than one person is entering data at the same
time, one person could start a record; go to lunch; another person enter a
record; and the first person come back from lunch and finish the record. That
could throw things out of order.

Try this: create a couple of new records then close the form. Open the form
and see if those two records have properly incremented numbers now.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry, thanks for your lightning response, however, am I doing something
stupid? Ever likely I might add.

Have done exactly as you suggest with the following code in the right places

Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum=[tblSeqNum]![SeqNum]+1;"
DoCmd.SetWarnings True

End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum =
1,tblSeqNum.RestartDate = Now() WHERE Year([RestartDate]) &
Month([RestartDate])<Year(Date()) & Month(Date());" 'This is all 1 line
DoCmd.SetWarnings True

End Sub
(Copied and pasted from the database)
But the serial number fails to increment on opening the form?

I am using Access 2000, does this make a difference?

Tables as you suggested tblSeqNum
Long integer Field [SeqNum] formatt 000
Date field [RestartDate]

Create a table named tblSeqNum with on Long field named SeqNum and a Date
field called RestartDate.

The default value for the serial number field on your form will be:
=DMax("SeqNum","tblSeqNum")

Also the format for your serial number needs to be 000 in the table, forms,
and all reports to show the leading zeros.

In the BeforeInsert event of your form you need the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum =
[tblSeqNum]![SeqNum]+1;"
DoCmd.SetWarnings True
End Sub

OK. This will increment the number. It's possible that if
two people are entering records at the same time that the numbers could get
out of proper sequence. It also does not take into account if someone
creates a new record then deletes it.

Now what to do about starting over at the beginning of the month..
Put the code below in the Form Open event of the input form.
It checks the RestartDate whenever the form is open and updates it if over
a month old. Watch out for word wrapping if you cut and paste.

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblSeqNum SET tblSeqNum.SeqNum = 1,
tblSeqNum.RestartDate = Now() WHERE Year([RestartDate]) &
Month([RestartDate])<Year(Date()) & Month(Date());"
DoCmd.SetWarnings True
End Sub
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry if this is similar to any previous questions.
I have a table which contains.
Year 05
Month 12
Initials TMM

which when joined together generates
05/12/TMM, this part is simple

However, I want to add a serial number to the end

05/12/TMM/###

the serial number should start at 001 and increment by 1 until the end of
the month at which point it should restart at 001. Can anyone assist with
this?

 

[Guest]

Hi Jerry, just a cuplle of words for you, you're a wizzard, thanks for your
assistance, all is working now, been struggling for days on this one. Thanks
a lot for all.