On 21 Nov 2005 20:40:57 -0800, "Kd" <ke******@yahoo.com> wrote:
I have the OrderNo set as the following
Right(Format(Date(),"yyyy"),1) & Format(Date(),"mm") &
"-"Format([OrderNo],"000")+1
This works great until
I get to a new month and need the numbers to reset like the following
example
512-001
Anybody have any thoughts to point me in the right direction,got stuck
with making updates to someone elses created database and getting
frustrated.
Any thoughts or ideas would be appreciated
Thank you
The example you gave must be from memory because that won't quite work, but in
any case, I think the most straightforward approach is to simply have a table
with month and year as fields in the key, and a NextSuffix field that gets
incremented for each new record.
I would not use existing records to determine the last number in use because a
deleted record could have its number reused, and that's bad for any kind of
auditing or reconciliation you have to do. I would also not just keep a
single counter and reset it because you may at some point need to insert a
record with a prior effective date.
The function to get a suffix value for a new record could be something like
the following (untested)...
Public Function NewSuffixInMonth( _
SuffixTable As String, _
RecDate As Date _
) As String
Dim lngResult As Long
On Error Goto Err_Catch
Dim dbs As DAO.Database
Set dbs = CurrentDB()
Dim qdf As DAO.Querydef
Set qdf = dbs.CreateQuerydef("")
qdf.SQL = "SELECT * FROM [" & SuffixTable & "] " & _
"WHERE [" & SuffixTable & "].YearNum = prm_YearNum AND " & _
"[" & SuffixTable & "].MonthNum = prm_MonthNum
Debug.Assert qdf.Parameters.Count = 2
qdf.Parameters!prm_YearNum = DatePart("yyyy", RecDate)
qdf.Parameters!prm_MonthNum = DatePart("m", RecDate)
Dim rstSuffix As DAO.Recordset
Set rstSuffix = qdf.OpenRecordset(dbOpenDynaset)
If rst.RecordCount > 0 Then
rst.Edit
lngResult = rstSuffix!SuffixNum + 1
Else
rst.AddNew
lngResult = 1
End If
rst!SuffixNum = lngResult
rst.Update
Proc_Final:
If Not rst Is Nothing Then
On Error Resume Next
rst.Close: Set rst = Nothing
On Error Goto 0: Err.Clear
End If
Set qdf = Nothing
Set dbs = Nothing
NewSuffixInMonth = Format(lngResult, "000")
Exit Function
Err_Catch:
MsgBox Err.Description
Resume ProcFinal
End Function
