Sequential number assignment

[mattc66 via AccessMonster.com]

I need to assign a sequential number to a serial number. The serial number is
the YEAR and a LETTER for the Month. Then followed by a sequential number for
the that month and year. The sequential number starts over each month. The
number must start with a 01.

How could I automate this and insure I don't duplicate it?

Thanks
Matt

 

[dan artuso]

Hi Matt,
Here is a function that will return an incrementing number which will start
over on the 1st
day of each month. Because you wanted in the form of 01, the return value is
actually a string.

In order for this to work, you must create a table, tblSeqNum, with one
field, num(data type Text).
Enter 01 into the table and you're set to go.
Here is the function, place it in a module:

Public Function GetSeqNum() As String
Dim db As DAO.Database
Dim strSql As String
Dim rs As DAO.Recordset
Dim strNum As String
Dim intNum As Integer
Dim strRet As String

On Error GoTo seqnum_err

Set db = CurrentDb()
strSql = "Select num From tblSeqNum"

Set rs = db.OpenRecordset(strSql)

strNum = rs(0)

If Day(Date) = 1 Then 'if it's day 1 then reset the number
strSql = "Update tblSeqNum set num = ""01"""
db.Execute strSql
GetSeqNum = "01"
Else
If Asc(strNum) = 48 Then 'because it;s a string, we have to increment
then add the leading zero back in
intNum = CInt(strNum)
intNum = intNum + 1
If intNum <= 9 Then
strNum = "0" & CStr(intNum)
Else
strNum = CStr(intNum)
End If
Else ' it's 10 or greater
strNum = CStr(CInt(strNum) + 1)
End If
strSql = "Update tblSeqNum set num =""" & strNum & """"
db.Execute strSql
GetSeqNum = strNum
End If

ExitHere:
If Not rs Is Nothing Then
Set rs = Nothing
End If

If Not db Is Nothing Then
Set db = Nothing
End If

Exit Function

seqnum_err:
MsgBox "Error! " & Err.Description
GoTo ExitHere

End Function

 

[dan artuso]

Sorry, please edit this line to include the extra check:

If Day(Date) = 1 And strNum <> "01" Then 'if it's day 1 then reset the
number, but only if it's the first time!

I forgot to check to se if the number had already been re-set.

 

[mattc66 via AccessMonster.com]

So then I guess I'd create a button on my form to get the sequence number. On
the OnClick event I'd call the function. How would I then add it to my serial
number?

Thanks
Matt

Sorry, please edit this line to include the extra check:

If Day(Date) = 1 And strNum <> "01" Then 'if it's day 1 then reset the
number, but only if it's the first time!

I forgot to check to se if the number had already been re-set.

I need to assign a sequential number to a serial number. The serial number
is

[quoted text clipped - 7 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

Okay I got it to work almost.. However when I change my computer date to next
month it doesn't start over.

Below is my code to run the the Function.


Private Sub cmdSeqNum_Click()

Me.Seq = GetSeqNum

End Sub

So then I guess I'd create a button on my form to get the sequence number. On
the OnClick event I'd call the function. How would I then add it to my serial
number?

Thanks
Matt

Sorry, please edit this line to include the extra check:

[quoted text clipped - 8 lines]

 

[mattc66 via AccessMonster.com]

What it's doing is it will only start over with 01 if its the first of the
month. The issue is if the first time they go to build this product may not
always be the 1st of the month. Maybe we could add another field in the table
to indicate the month and if it's a new month# it restarts the sequence
number?

Matt

Okay I got it to work almost.. However when I change my computer date to next
month it doesn't start over.

Below is my code to run the the Function.

Private Sub cmdSeqNum_Click()

Me.Seq = GetSeqNum

End Sub

So then I guess I'd create a button on my form to get the sequence number. On
the OnClick event I'd call the function. How would I then add it to my serial

[quoted text clipped - 8 lines]

 

[mattc66 via AccessMonster.com]

Okay I am trying this on my own, but struggling.

I added another Field called mo and in there I will store the month number.
March = 3.

If the current month and the mo do not match then I want the num to change
back to 01 and then I want the mo data to change to the current month number.

Below is my attempt. I am struggling with updating the mo data. Can you offer
some help?

Thank you

Public Function GetSeqNum() As String
Dim db As DAO.Database
Dim strSql As String
Dim strSql2 As String

Dim rs As DAO.Recordset
Dim strNum As String
Dim strMon As String
Dim intNum As Integer
Dim strRet As String
Dim strMonth As String

On Error GoTo seqnum_err

Set db = CurrentDb()
strSql = "Select num From tblSeqNum"
strSql2 = "Select mo From tblSeqNum"
Set rs = db.OpenRecordset(strSql)
strMon = DLookup("[mo]", "tblSeqNum")


strMonth = DatePart("m", Now)
strNum = rs(0)

If strMon <> strMonth Then
'If Day(Date) = 1 And strNum <> "01" Then 'if it's day 1 then reset the
number
strSql = "Update tblSeqNum set num = ""01"""
db.Execute strSql
strSql2 = "Update tblSeqNum set mo = strMonth"
db.Execute strSql2

GetSeqNum = "01"


Else
If Asc(strNum) = 48 Then 'because it's a string, we have to_
'increment then add the leading zero back in
intNum = CInt(strNum)
intNum = intNum + 1
If intNum <= 9 Then
strNum = "0" & CStr(intNum)
Else
strNum = CStr(intNum)
End If
Else ' it's 10 or greater
strNum = CStr(CInt(strNum) + 1)
End If
strSql = "Update tblSeqNum set num =""" & strNum & """"
db.Execute strSql
GetSeqNum = strNum

End If

ExitHere:
If Not rs Is Nothing Then
Set rs = Nothing
End If


If Not db Is Nothing Then
Set db = Nothing
End If

Exit Function

seqnum_err:
MsgBox "Error! " & Err.Description
GoTo ExitHere

End Function

Sorry, please edit this line to include the extra check:

If Day(Date) = 1 And strNum <> "01" Then 'if it's day 1 then reset the
number, but only if it's the first time!

I forgot to check to se if the number had already been re-set.

I need to assign a sequential number to a serial number. The serial number
is

[quoted text clipped - 7 lines]

Thanks
Matt

 

[John W. Vinson]

I need to assign a sequential number to a serial number. The serial number is
the YEAR and a LETTER for the Month. Then followed by a sequential number for
the that month and year. The sequential number starts over each month. The
number must start with a 01.

How could I automate this and insure I don't duplicate it?

Thanks
Matt

So something like 2007A01 for the first record in January, 2006L13 for
the thirteenth in last December?

You are somewhat lucky that your code sorts chronologically, so you
can use DMax().

Try:

Dim iNum As Integer
Dim strLast As String
Dim strNew As String

' get the year and month
strNew = Format(Date, "yyyy") & Mid("ABCDEFGHIJKL", Month(Date), 1)
' find the largest existing serial number in this month; use "00" if
' there is none
strLast = NZ(DMax("[SerialNo]", "[tablename]", "[SerialNo] LIKE " _
& strNew), strNew & "00")
iNum = Val(Right(strLast, 2))
If iNum = 99 Then
MsgBox "Take the rest of the month off - no more serials available"
Else
strNew = Left(strNew, 5) & Format(iNum + 1, "00")
End If

Me.SerialNum = strNew

John W. Vinson [MVP]