Report/Invoice numbering convention

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

I have posted codes sent to me by Jerry Whittle for doing exactly what you
want to do. You can perhaps ignor the first part, the part of interest should
be the where it say "Now what to do about starting over" Have a read, I think
you may be able to adapt this by changind fields and control names to your
convention. It worked a treat for me with thanks going to Jerry.

I really hope this jem helps you?

Regards
Mike B (Now the Code below)

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

--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

By the way, you can see the whole issue by typing "Serial Numbers again" (No
qotes) in the "Search For" window for new users news group. (Thats why,
pressing YES to answering the question is important, the thread stays active
longer. Date of posting Dec 2005)

Again hope this helps
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


"MikeJohnB" wrote:

I have posted codes sent to me by Jerry Whittle for doing exactly what you
want to do. You can perhaps ignor the first part, the part of interest should
be the where it say "Now what to do about starting over" Have a read, I think
you may be able to adapt this by changind fields and control names to your
convention. It worked a treat for me with thanks going to Jerry.

I really hope this jem helps you?

Regards
Mike B (Now the Code below)

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

--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

What's the specific reason why you'd like things to reset at the start of the
year?

"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

In a single user environment you don't need your tblNCDateStart table at all
as you can compute the next NCRepNum value from the data in the
tbleNCReportLog table by looking up the highest sequential number for the
current year and adding 1. You'd do this in the frmNCReportLog form's
BeforeInsert event procedure with:

Dim strYear as String
Dim strWeek as String
Dim strCriteria As String

strYear = Format(VBA.Date,"yy")
strWeek = Format(VBA.Date,"ww")

strCriteria = "Left(NCRepNum,2) = """ & strYear & """"

Me.NCRepNum = strYear & strWeek & _
Format(Nz(DMax(Right(NCRepNum,3)),-1)+1,"000")

In a multi-user environment where the tables are in a shared back end a
conflict could arise if two or more users are adding a record simultaneously.
Provided that the NCRepNum column is indexed uniquely (no duplicates) then
what would happen is that the first user to save the record would be
successful, but the other users would incur an error when they attempt to
save their record. There are various solutions to this, but one, which I've
used successfully for many years, is to use a tblNCDateStart table as you are
doing but to put this in a separate database in a shared folder on the
network and open it exclusively when a user is getting the next number. As
you are starting your number sequence each year you only need the year in a
ReportYear column in tblNCDateStart rather than the year and week, which
needs no DefaultValue or Format property set, but should still be an integer
number data type and a NextNumber column of integer data type. You only need
store the last number used per year, not all numbers. You can then get the
next NCRepNum value by means of the following function, which should go in a
standard module in your database's front end:

Public Function GetNextNCRepNum(strCounterDb As String)

' Accepts: Full path to database containing tblNCDateStart table with
' integer column NextNumber and integer column ReportYear.

' Returns next number in sequence for specified Report Year
' if external database can be opened and number obtained.
' Returns Null if unable to get next number.

Const NOCURRENTRECORD As Integer = 3021
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim n As Integer, I As Integer, intInterval As Integer
Dim intYear As Integer
Dim strSQL As String

intYear = Year(VBA.Date)

strSQL = "SELECT * FROM tblNCDateStart WHERE ReportYear = " & intYear

' make 10 attempts to open external database exclusively
DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, "Attempting to get new number"
On Error Resume Next
For n = 1 To 10
Err.Clear
Set dbs = OpenDatabase(strCounterDb, True)
If Err = 0 Then
Exit For
Else
intInterval = Int(Rnd(Time()) * 100)
For I = 1 To intInterval
DoEvents
Next I
End If
Next n
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False

If Err 0 Then
GetNextNCRepNum = Null
Exit Function
End If

Err.Clear

Set rst = dbs.OpenRecordset(strSQL)

With rst
.Edit
' insert new row if no existing record for this Report Year
If Err = NOCURRENTRECORD Then
.AddNew
!ReportYear = intYear
!NextNumber = 0
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & "000"
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & _
Format(rst!NextNumber, "000")
End If
End With

rst.Close

End Function


In the frmNCReportLog form's BeforeUpdate event procedure you can then call
the function, passing the path to the database containing the tblNCDateStart
table as its argument. If the back end database is simply an Access .mdb
file, and the database file containing the tblNCDateStart table, which I'll
call Counter.mdb for this example, is in the same shared folder as the back
end, then you can get the path with the following function:

Public Function ConnectPath() As String

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strConnectString As String, strDbName As String, intSlashPos As
Integer

Set dbs = CurrentDb
' loop through tabledefs collection until
' first linked table is encountered and
' get its Connect property
For Each tdf In dbs.TableDefs
If tdf.Connect "" Then
strConnectString = tdf.Connect
End If
Next tdf

' remove table name from connect string
intSlashPos = 1
strDbName = strConnectString
Do While intSlashPos 0
intSlashPos = InStr(strDbName, "\")
strDbName = Right(strDbName, Len(strDbName) - intSlashPos)
Loop

' remove part of connect string before path
ConnectPath = Mid(strConnectString, 11, Len(strConnectString) _
- (10 + Len(strDbName)))

End Function


You can then call the GetNextNCRepNum function in the frmNCReportLog form's
BeforeInsert event procedure like so:

On Error GoTo Err_Handler

Const conMESSAGE = "Unable to get NCRepNum number at present."
Dim strCounterDb As String, varNCRepNum As Variant

strCounterDb = ConnectPath() & "Counter.mdb"

varNCRepNum = GetNextNCRepNum(strCounterDb)

If Not IsNull(varNCRepNum) Then
Me!NCRepNum = varNCRepNum
Else
MsgBox conMESSAGE, vbExclamation, "Error"
Cancel = True
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here


Note that, as with an autonumber, once a number is obtained it won't be
re-used even if the record is not saved or is subsequently deleted.

Ken Sheridan
Stafford, England

"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

Good question, David. It's one that I keep asking myself because I get the
feeling I am creating an overly complicated solution to a rather simple
problem. I work in the quality department and one of my duties is to
continually improve the overall quality of our products and services. This
number sequence will be issued on our Nonconformance reports when defective
products are manufactured.

Having the three-digit number reset at the beginning of the year allows me
to quickly and informally assess one aspect of the quality of work that is
being put out as the year progresses. Alternatively, a report number that
continually increments every year would not give me that information and
would require a report to be generated that would tell me how many NC reports
had been created.

Using the yyww###, five or six years down the road I will still be able to
pick up a printed copy of an NC report and determine how many have been
created at that point during the year. To me, it helps me informally assess
the quality of our work.

I'm new at using Access so my logic is probably a bit flawed. I'm open to
suggestions or criticism if there are better ways to go about this.

-David K.

"David H" wrote:

What's the specific reason why you'd like things to reset at the start of the
year?

"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

Thanks, Ken. I'm trying the first solution (single user) to get my feet wet
with event procedures.

I get an error when I begin entering data in the frmNCReportLog. It says...

Compile error:
Argument not optional

I'm too new at this to know what this means. Should I put something in that
takes care of null values?

Take Care,
David K.

"Ken Sheridan" wrote:

In a single user environment you don't need your tblNCDateStart table at all
as you can compute the next NCRepNum value from the data in the
tbleNCReportLog table by looking up the highest sequential number for the
current year and adding 1. You'd do this in the frmNCReportLog form's
BeforeInsert event procedure with:

Dim strYear as String
Dim strWeek as String
Dim strCriteria As String

strYear = Format(VBA.Date,"yy")
strWeek = Format(VBA.Date,"ww")

strCriteria = "Left(NCRepNum,2) = """ & strYear & """"

Me.NCRepNum = strYear & strWeek & _
Format(Nz(DMax(Right(NCRepNum,3)),-1)+1,"000")

In a multi-user environment where the tables are in a shared back end a
conflict could arise if two or more users are adding a record simultaneously.
Provided that the NCRepNum column is indexed uniquely (no duplicates) then
what would happen is that the first user to save the record would be
successful, but the other users would incur an error when they attempt to
save their record. There are various solutions to this, but one, which I've
used successfully for many years, is to use a tblNCDateStart table as you are
doing but to put this in a separate database in a shared folder on the
network and open it exclusively when a user is getting the next number. As
you are starting your number sequence each year you only need the year in a
ReportYear column in tblNCDateStart rather than the year and week, which
needs no DefaultValue or Format property set, but should still be an integer
number data type and a NextNumber column of integer data type. You only need
store the last number used per year, not all numbers. You can then get the
next NCRepNum value by means of the following function, which should go in a
standard module in your database's front end:

Public Function GetNextNCRepNum(strCounterDb As String)

' Accepts: Full path to database containing tblNCDateStart table with
' integer column NextNumber and integer column ReportYear.

' Returns next number in sequence for specified Report Year
' if external database can be opened and number obtained.
' Returns Null if unable to get next number.

Const NOCURRENTRECORD As Integer = 3021
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim n As Integer, I As Integer, intInterval As Integer
Dim intYear As Integer
Dim strSQL As String

intYear = Year(VBA.Date)

strSQL = "SELECT * FROM tblNCDateStart WHERE ReportYear = " & intYear

' make 10 attempts to open external database exclusively
DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, "Attempting to get new number"
On Error Resume Next
For n = 1 To 10
Err.Clear
Set dbs = OpenDatabase(strCounterDb, True)
If Err = 0 Then
Exit For
Else
intInterval = Int(Rnd(Time()) * 100)
For I = 1 To intInterval
DoEvents
Next I
End If
Next n
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False

If Err 0 Then
GetNextNCRepNum = Null
Exit Function
End If

Err.Clear

Set rst = dbs.OpenRecordset(strSQL)

With rst
.Edit
' insert new row if no existing record for this Report Year
If Err = NOCURRENTRECORD Then
.AddNew
!ReportYear = intYear
!NextNumber = 0
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & "000"
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & _
Format(rst!NextNumber, "000")
End If
End With

rst.Close

End Function


In the frmNCReportLog form's BeforeUpdate event procedure you can then call
the function, passing the path to the database containing the tblNCDateStart
table as its argument. If the back end database is simply an Access .mdb
file, and the database file containing the tblNCDateStart table, which I'll
call Counter.mdb for this example, is in the same shared folder as the back
end, then you can get the path with the following function:

Public Function ConnectPath() As String

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strConnectString As String, strDbName As String, intSlashPos As
Integer

Set dbs = CurrentDb
' loop through tabledefs collection until
' first linked table is encountered and
' get its Connect property
For Each tdf In dbs.TableDefs
If tdf.Connect "" Then
strConnectString = tdf.Connect
End If
Next tdf

' remove table name from connect string
intSlashPos = 1
strDbName = strConnectString
Do While intSlashPos 0
intSlashPos = InStr(strDbName, "\")
strDbName = Right(strDbName, Len(strDbName) - intSlashPos)
Loop

' remove part of connect string before path
ConnectPath = Mid(strConnectString, 11, Len(strConnectString) _
- (10 + Len(strDbName)))

End Function


You can then call the GetNextNCRepNum function in the frmNCReportLog form's
BeforeInsert event procedure like so:

On Error GoTo Err_Handler

Const conMESSAGE = "Unable to get NCRepNum number at present."
Dim strCounterDb As String, varNCRepNum As Variant

strCounterDb = ConnectPath() & "Counter.mdb"

varNCRepNum = GetNextNCRepNum(strCounterDb)

If Not IsNull(varNCRepNum) Then
Me!NCRepNum = varNCRepNum
Else
MsgBox conMESSAGE, vbExclamation, "Error"
Cancel = True
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here


Note that, as with an autonumber, once a number is obtained it won't be
re-used even if the record is not saved or is subsequently deleted.

Ken Sheridan
Stafford, England

"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

David:

Mea culpa on three counts! I missed the table name as the second argument
of the DMax function, the criterion as the third argument, and the first
argument should have been wrapped in quotes. It should have been:

Me.NCRepNum = strYear & strWeek & _
Format(Nz(DMax("Right(NCRepNum,3)", _
"tblNCReportLog", strCriteria),-1)+1,"000")

Sorry about the confusion. Three cock-ups in one line must be a record,
even for me!

BTW if you want the numbering to start at 001 rather than 000 each year
change the -1 to 0.

Ken Sheridan
Stafford, England

"David K." wrote:

Thanks, Ken. I'm trying the first solution (single user) to get my feet wet
with event procedures.

I get an error when I begin entering data in the frmNCReportLog. It says...

Compile error:
Argument not optional

I'm too new at this to know what this means. Should I put something in that
takes care of null values?

Take Care,
David K.

"Ken Sheridan" wrote:

In a single user environment you don't need your tblNCDateStart table at all
as you can compute the next NCRepNum value from the data in the
tblNCReportLog table by looking up the highest sequential number for the
current year and adding 1. You'd do this in the frmNCReportLog form's
BeforeInsert event procedure with:

Dim strYear as String
Dim strWeek as String
Dim strCriteria As String

strYear = Format(VBA.Date,"yy")
strWeek = Format(VBA.Date,"ww")

strCriteria = "Left(NCRepNum,2) = """ & strYear & """"

Me.NCRepNum = strYear & strWeek & _
Format(Nz(DMax(Right(NCRepNum,3)),-1)+1,"000")

In a multi-user environment where the tables are in a shared back end a
conflict could arise if two or more users are adding a record simultaneously.
Provided that the NCRepNum column is indexed uniquely (no duplicates) then
what would happen is that the first user to save the record would be
successful, but the other users would incur an error when they attempt to
save their record. There are various solutions to this, but one, which I've
used successfully for many years, is to use a tblNCDateStart table as you are
doing but to put this in a separate database in a shared folder on the
network and open it exclusively when a user is getting the next number. As
you are starting your number sequence each year you only need the year in a
ReportYear column in tblNCDateStart rather than the year and week, which
needs no DefaultValue or Format property set, but should still be an integer
number data type and a NextNumber column of integer data type. You only need
store the last number used per year, not all numbers. You can then get the
next NCRepNum value by means of the following function, which should go in a
standard module in your database's front end:

Public Function GetNextNCRepNum(strCounterDb As String)

' Accepts: Full path to database containing tblNCDateStart table with
' integer column NextNumber and integer column ReportYear.

' Returns next number in sequence for specified Report Year
' if external database can be opened and number obtained.
' Returns Null if unable to get next number.

Const NOCURRENTRECORD As Integer = 3021
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim n As Integer, I As Integer, intInterval As Integer
Dim intYear As Integer
Dim strSQL As String

intYear = Year(VBA.Date)

strSQL = "SELECT * FROM tblNCDateStart WHERE ReportYear = " & intYear

' make 10 attempts to open external database exclusively
DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, "Attempting to get new number"
On Error Resume Next
For n = 1 To 10
Err.Clear
Set dbs = OpenDatabase(strCounterDb, True)
If Err = 0 Then
Exit For
Else
intInterval = Int(Rnd(Time()) * 100)
For I = 1 To intInterval
DoEvents
Next I
End If
Next n
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False

If Err 0 Then
GetNextNCRepNum = Null
Exit Function
End If

Err.Clear

Set rst = dbs.OpenRecordset(strSQL)

With rst
.Edit
' insert new row if no existing record for this Report Year
If Err = NOCURRENTRECORD Then
.AddNew
!ReportYear = intYear
!NextNumber = 0
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & "000"
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & _
Format(rst!NextNumber, "000")
End If
End With

rst.Close

End Function


In the frmNCReportLog form's BeforeUpdate event procedure you can then call
the function, passing the path to the database containing the tblNCDateStart
table as its argument. If the back end database is simply an Access .mdb
file, and the database file containing the tblNCDateStart table, which I'll
call Counter.mdb for this example, is in the same shared folder as the back
end, then you can get the path with the following function:

Public Function ConnectPath() As String

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strConnectString As String, strDbName As String, intSlashPos As
Integer

Set dbs = CurrentDb
' loop through tabledefs collection until
' first linked table is encountered and
' get its Connect property
For Each tdf In dbs.TableDefs
If tdf.Connect "" Then
strConnectString = tdf.Connect
End If
Next tdf

' remove table name from connect string
intSlashPos = 1
strDbName = strConnectString
Do While intSlashPos 0
intSlashPos = InStr(strDbName, "\")
strDbName = Right(strDbName, Len(strDbName) - intSlashPos)
Loop

' remove part of connect string before path
ConnectPath = Mid(strConnectString, 11, Len(strConnectString) _
- (10 + Len(strDbName)))

End Function


You can then call the GetNextNCRepNum function in the frmNCReportLog form's
BeforeInsert event procedure like so:

On Error GoTo Err_Handler

Const conMESSAGE = "Unable to get NCRepNum number at present."
Dim strCounterDb As String, varNCRepNum As Variant

strCounterDb = ConnectPath() & "Counter.mdb"

varNCRepNum = GetNextNCRepNum(strCounterDb)

If Not IsNull(varNCRepNum) Then
Me!NCRepNum = varNCRepNum
Else
MsgBox conMESSAGE, vbExclamation, "Error"
Cancel = True
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here


Note that, as with an autonumber, once a number is obtained it won't be
re-used even if the record is not saved or is subsequently deleted.

Ken Sheridan
Stafford, England

"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

Thanks, Mike. Haven't finished putting this example in place yet but I
wanted to let you know your help is greatly appreciated.

"MikeJohnB" wrote:

By the way, you can see the whole issue by typing "Serial Numbers again" (No
qotes) in the "Search For" window for new users news group. (Thats why,
pressing YES to answering the question is important, the thread stays active
longer. Date of posting Dec 2005)

Again hope this helps
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


"MikeJohnB" wrote:

I have posted codes sent to me by Jerry Whittle for doing exactly what you
want to do. You can perhaps ignor the first part, the part of interest should
be the where it say "Now what to do about starting over" Have a read, I think
you may be able to adapt this by changind fields and control names to your
convention. It worked a treat for me with thanks going to Jerry.

I really hope this jem helps you?

Regards
Mike B (Now the Code below)

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

--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.

It worked great! Thanks! I really appreciate the detailed instructions as
well as an explanation of the logic that goes with the solutions.

-David K.

"Ken Sheridan" wrote:

David:

Mea culpa on three counts! I missed the table name as the second argument
of the DMax function, the criterion as the third argument, and the first
argument should have been wrapped in quotes. It should have been:

Me.NCRepNum = strYear & strWeek & _
Format(Nz(DMax("Right(NCRepNum,3)", _
"tblNCReportLog", strCriteria),-1)+1,"000")

Sorry about the confusion. Three cock-ups in one line must be a record,
even for me!

BTW if you want the numbering to start at 001 rather than 000 each year
change the -1 to 0.

Ken Sheridan
Stafford, England

"David K." wrote:

Thanks, Ken. I'm trying the first solution (single user) to get my feet wet
with event procedures.

I get an error when I begin entering data in the frmNCReportLog. It says...

Compile error:
Argument not optional

I'm too new at this to know what this means. Should I put something in that
takes care of null values?

Take Care,
David K.

"Ken Sheridan" wrote:

In a single user environment you don't need your tblNCDateStart table at all
as you can compute the next NCRepNum value from the data in the
tblNCReportLog table by looking up the highest sequential number for the
current year and adding 1. You'd do this in the frmNCReportLog form's
BeforeInsert event procedure with:

Dim strYear as String
Dim strWeek as String
Dim strCriteria As String

strYear = Format(VBA.Date,"yy")
strWeek = Format(VBA.Date,"ww")

strCriteria = "Left(NCRepNum,2) = """ & strYear & """"

Me.NCRepNum = strYear & strWeek & _
Format(Nz(DMax(Right(NCRepNum,3)),-1)+1,"000")

In a multi-user environment where the tables are in a shared back end a
conflict could arise if two or more users are adding a record simultaneously.
Provided that the NCRepNum column is indexed uniquely (no duplicates) then
what would happen is that the first user to save the record would be
successful, but the other users would incur an error when they attempt to
save their record. There are various solutions to this, but one, which I've
used successfully for many years, is to use a tblNCDateStart table as you are
doing but to put this in a separate database in a shared folder on the
network and open it exclusively when a user is getting the next number. As
you are starting your number sequence each year you only need the year in a
ReportYear column in tblNCDateStart rather than the year and week, which
needs no DefaultValue or Format property set, but should still be an integer
number data type and a NextNumber column of integer data type. You only need
store the last number used per year, not all numbers. You can then get the
next NCRepNum value by means of the following function, which should go in a
standard module in your database's front end:

Public Function GetNextNCRepNum(strCounterDb As String)

' Accepts: Full path to database containing tblNCDateStart table with
' integer column NextNumber and integer column ReportYear.

' Returns next number in sequence for specified Report Year
' if external database can be opened and number obtained.
' Returns Null if unable to get next number.

Const NOCURRENTRECORD As Integer = 3021
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim n As Integer, I As Integer, intInterval As Integer
Dim intYear As Integer
Dim strSQL As String

intYear = Year(VBA.Date)

strSQL = "SELECT * FROM tblNCDateStart WHERE ReportYear = " & intYear

' make 10 attempts to open external database exclusively
DoCmd.Hourglass True
SysCmd acSysCmdSetStatus, "Attempting to get new number"
On Error Resume Next
For n = 1 To 10
Err.Clear
Set dbs = OpenDatabase(strCounterDb, True)
If Err = 0 Then
Exit For
Else
intInterval = Int(Rnd(Time()) * 100)
For I = 1 To intInterval
DoEvents
Next I
End If
Next n
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False

If Err 0 Then
GetNextNCRepNum = Null
Exit Function
End If

Err.Clear

Set rst = dbs.OpenRecordset(strSQL)

With rst
.Edit
' insert new row if no existing record for this Report Year
If Err = NOCURRENTRECORD Then
.AddNew
!ReportYear = intYear
!NextNumber = 0
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & "000"
Else
' update row and get next number in sequence
!NextNumber = !NextNumber + 1
.Update
GetNextNCRepNum = Format(VBA.Date, "yyww") & _
Format(rst!NextNumber, "000")
End If
End With

rst.Close

End Function


In the frmNCReportLog form's BeforeUpdate event procedure you can then call
the function, passing the path to the database containing the tblNCDateStart
table as its argument. If the back end database is simply an Access .mdb
file, and the database file containing the tblNCDateStart table, which I'll
call Counter.mdb for this example, is in the same shared folder as the back
end, then you can get the path with the following function:

Public Function ConnectPath() As String

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strConnectString As String, strDbName As String, intSlashPos As
Integer

Set dbs = CurrentDb
' loop through tabledefs collection until
' first linked table is encountered and
' get its Connect property
For Each tdf In dbs.TableDefs
If tdf.Connect "" Then
strConnectString = tdf.Connect
End If
Next tdf

' remove table name from connect string
intSlashPos = 1
strDbName = strConnectString
Do While intSlashPos 0
intSlashPos = InStr(strDbName, "\")
strDbName = Right(strDbName, Len(strDbName) - intSlashPos)
Loop

' remove part of connect string before path
ConnectPath = Mid(strConnectString, 11, Len(strConnectString) _
- (10 + Len(strDbName)))

End Function


You can then call the GetNextNCRepNum function in the frmNCReportLog form's
BeforeInsert event procedure like so:

On Error GoTo Err_Handler

Const conMESSAGE = "Unable to get NCRepNum number at present."
Dim strCounterDb As String, varNCRepNum As Variant

strCounterDb = ConnectPath() & "Counter.mdb"

varNCRepNum = GetNextNCRepNum(strCounterDb)

If Not IsNull(varNCRepNum) Then
Me!NCRepNum = varNCRepNum
Else
MsgBox conMESSAGE, vbExclamation, "Error"
Cancel = True
End If

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here


Note that, as with an autonumber, once a number is obtained it won't be
re-used even if the record is not saved or is subsequently deleted.

Ken Sheridan
Stafford, England

"David K." wrote:

I've created a invoice numbering system based on a combination of the current
year and week number plus a 3-digit consecutive number. The format is
YYWW###. Starting today, the report number will look like this; 0849000,
0849001, etc. On the following Monday the week would change to 50 and the
report number would be as such; 0850002, 0850003, etc. Come January 1st,
the first report number created would look like this; 0901000.

I would like the 3-digit number to restart at 000 or 001 at the beginning of
each year depending on what is possible and/or easier to do in Access. FYI,
we will never create more than a couple hundred in one year.

I have one table called tblNCDateStart that has two fields; ReportDate &
NCStart:

tblNCDateStart
ReportDate
Data Type / Long Int.
Format / 0000
Default Value / =Format(Date(),"yy") & "" & Format(Date(),"ww")
'creates current 4-digit yyww number; 0849, 0850...0901, 0902, etc...
NCStart
Data Type = Long Int.
Format = 000

Another table named NCReportLog will be used to store each individual
nonconformance instance.

tbleNCReportLog
NCID 'Autonumber & Primary Key
NCRepNum 'this is where I want to store the 7-digit yyww### number
DateInitiated
CustID
PartNumID
...

Here are my questions I was hoping to get some help with. I'm new at this
so if this requires a bit of code with event procedures could you help me out?

1) I will create a form (frmNCReportLog) to enter nonconformance instances.
How would I set up the form to create (concatenate ReportDate & NCStart) and
then store the NCRepNum in the tblNCReportLog? I was thinking that the
number could either be created upon opening up a new form entry or by
creating a "Get NC No." button.

2) I also want the NCReportNum to increment by +1 for each consecutive
entry. From fishing through the forum it seems that I would use DMax but I'm
a bit confused with the syntax and where to put it.

3) How can I set up the system to automatically reset the 3-digit suffix
back to 000 on January 1st each year.

Thanks in advance.