G
Guest
Hi folks,
I have a form called tbldocinput that contains a subform countrack. The
form countrack is behind a query also called countrack that will provide me
with the MAX() of the value of a tracking number that I am trying to create
and autogenerate.
I want the user to input the data on the tbldocinput form which is
essentially a data entry form. When the user clicks save, the countrack is
requery as well as a unbound field on the form tbldocinput that is default to
0.
If there is a value in the countrack form field dtrack, then I want to use
that value to input in my tracking number.
Here is my SQL:
Private Sub cmdsave_Click()
On Error GoTo Err_cmdsave_Click
Me.countrack.Requery
Me.trcount.Requery
Me.trcount1.Requery
If Nz(Me.countrack.Form.dtrack, 0) = 0 Then
Me.Track = Left(Me.Dept, 1) & Format(Me.DateEntered, "yymmdd") &
Format(Me.trcount + 1, "000")
End If
If Nz(Me.countrack.Form.dtrack, 0) > 0 Then
Me.Track = Left(Me.Dept, 1) & Format(Me.DateEntered, "yymmdd") &
Format(Me.countrack.Form.dtrack + 1, "000")
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdsave_Click:
Exit Sub
Err_cmdsave_Click:
MsgBox Err.Description
Resume Exit_cmdsave_Click
End Sub
The goal here is to have a tracking number generated by the first letter of
the department, the date of entry (date()) and the MAX value of the last 3
digits of dtrack (which is essentially the last 3 values of the max tracking
number in the table corresponding to a date).
So the tracking number will be as such F071511000 when there is no matching
data in the query (query is null) or will be equal to the max value of dtrack
in countrack + 1 or F071511001 at the end of the day.
Basically what we want is to have a tracking number that is based on the
department, date of entry and the last few digits which would be an
increasing number that defaults back to 0 when the first record of that day
is entered.
Hope this is clear, otherwise if you have any questions, please let me know.
I have a form called tbldocinput that contains a subform countrack. The
form countrack is behind a query also called countrack that will provide me
with the MAX() of the value of a tracking number that I am trying to create
and autogenerate.
I want the user to input the data on the tbldocinput form which is
essentially a data entry form. When the user clicks save, the countrack is
requery as well as a unbound field on the form tbldocinput that is default to
0.
If there is a value in the countrack form field dtrack, then I want to use
that value to input in my tracking number.
Here is my SQL:
Private Sub cmdsave_Click()
On Error GoTo Err_cmdsave_Click
Me.countrack.Requery
Me.trcount.Requery
Me.trcount1.Requery
If Nz(Me.countrack.Form.dtrack, 0) = 0 Then
Me.Track = Left(Me.Dept, 1) & Format(Me.DateEntered, "yymmdd") &
Format(Me.trcount + 1, "000")
End If
If Nz(Me.countrack.Form.dtrack, 0) > 0 Then
Me.Track = Left(Me.Dept, 1) & Format(Me.DateEntered, "yymmdd") &
Format(Me.countrack.Form.dtrack + 1, "000")
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdsave_Click:
Exit Sub
Err_cmdsave_Click:
MsgBox Err.Description
Resume Exit_cmdsave_Click
End Sub
The goal here is to have a tracking number generated by the first letter of
the department, the date of entry (date()) and the MAX value of the last 3
digits of dtrack (which is essentially the last 3 values of the max tracking
number in the table corresponding to a date).
So the tracking number will be as such F071511000 when there is no matching
data in the query (query is null) or will be equal to the max value of dtrack
in countrack + 1 or F071511001 at the end of the day.
Basically what we want is to have a tracking number that is based on the
department, date of entry and the last few digits which would be an
increasing number that defaults back to 0 when the first record of that day
is entered.
Hope this is clear, otherwise if you have any questions, please let me know.