stormadvisor
01-05-2010, 09:04 AM
I have a form named "Log" that is used to record received calls. A button on that form opens another form called "incidents". This is filled out and a button is pressed to save the incident. This action just assigns the next number in a format of "10-XXX". "10" is the year and "XXX" is the next incremental number. This number is shown in a text box on both forms. The "XXX" number is saved in a table named "tblIncidents" in the "IncrNum" column. the year is taken from the datefield when the record was created.
My problem is that it the incremental number was supposed to start over at "001" on Jan. 1 every year. It did not happen. I have even manually changed the number it assigns in the table to "001" and it assigns the next number as 691. 690 was the last incident of 2009.
Here is the code:
Private Sub SaveUpdateRecord()
On Error GoTo Err_save_Click
'Check to see if the date textbox is empty. It is auto-assigned when the IR form gets it's first data
'entered. If the box is null, then nothing was entered, an ID was not assigned, and there is no report.
'Cancel the save.
If IsNull(Me.txtdate) Then GoTo EOS
If Not Me.Dirty Then GoTo EOS
If Me.txtIncrNum > 0 Then
'Don't update the IR #...it's already been saved
Else
'New incident report - write the increment number
Me.txtIncrNum.Enabled = True
Me.txtIncrNum.SetFocus
Me.txtIncrNum.Text = Nz(DMax("[IncrNum]", "[tblincidents]", _
"Year([datefield]) = " & Year(Date)), 0) + 1
End If
If Me.Dirty Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
EOS:
'command the log screen to reload with the correct log ID
Dim LogNum As Integer
LogNum = Me.txtLogNumber
DoCmd.Close
'If Form_Log.Visible Then
If Not gLogClosed Then
Form_Log.ReloadLogRecord (LogNum)
Else
'Don't reload the record on the log form since the form is closed.
End If
'Form_Log.ReloadLogRecord (LogNum)
Exit_save_Click:
Exit Sub
Err_save_Click:
MsgBox Err.Description
Resume Exit_save_Click
End Sub
I have limited knowledge when it comes to VBA so this was written by someone else. I'd talk to him but he is not available.
I'm using access 2003 in XPsp2
Thanks
Matt
PS - I am off Wed. and Thurs. but will try to monitor this from home to answer any questions.
My problem is that it the incremental number was supposed to start over at "001" on Jan. 1 every year. It did not happen. I have even manually changed the number it assigns in the table to "001" and it assigns the next number as 691. 690 was the last incident of 2009.
Here is the code:
Private Sub SaveUpdateRecord()
On Error GoTo Err_save_Click
'Check to see if the date textbox is empty. It is auto-assigned when the IR form gets it's first data
'entered. If the box is null, then nothing was entered, an ID was not assigned, and there is no report.
'Cancel the save.
If IsNull(Me.txtdate) Then GoTo EOS
If Not Me.Dirty Then GoTo EOS
If Me.txtIncrNum > 0 Then
'Don't update the IR #...it's already been saved
Else
'New incident report - write the increment number
Me.txtIncrNum.Enabled = True
Me.txtIncrNum.SetFocus
Me.txtIncrNum.Text = Nz(DMax("[IncrNum]", "[tblincidents]", _
"Year([datefield]) = " & Year(Date)), 0) + 1
End If
If Me.Dirty Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
EOS:
'command the log screen to reload with the correct log ID
Dim LogNum As Integer
LogNum = Me.txtLogNumber
DoCmd.Close
'If Form_Log.Visible Then
If Not gLogClosed Then
Form_Log.ReloadLogRecord (LogNum)
Else
'Don't reload the record on the log form since the form is closed.
End If
'Form_Log.ReloadLogRecord (LogNum)
Exit_save_Click:
Exit Sub
Err_save_Click:
MsgBox Err.Description
Resume Exit_save_Click
End Sub
I have limited knowledge when it comes to VBA so this was written by someone else. I'd talk to him but he is not available.
I'm using access 2003 in XPsp2
Thanks
Matt
PS - I am off Wed. and Thurs. but will try to monitor this from home to answer any questions.