C
CJ
Hi Groupies
I am trying to incorporate Allen Brownes code to create a duplicate record
but
an adjustment I require is not firing for me.
If equipment has been rented during the month and not returned by month end
then it gets carried over to the next month on a new duplicated work order.
The code would be perfect for me except that I have to use a code generated
sequence number for the Work Order number.
I have tried using the same code that works when I originally create a work
order
but I am not getting anything to happen. When I look in tblWOSequence, I do
not
have a new Work Order number. Below is what I have for code:
Private Sub cmdDuplicate_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngWOId As Long 'Primary key value of the new record.
Dim WOSeq As Integer 'Auto generated Work Order Number
'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If
'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
' MY inserted code to capture the available sequence number
WOSeq = DLookup("seqno", "tblWOSequence")
With Me.RecordsetClone
.AddNew
!WOSeq = WOSeq
!Customer_ID = Me.Customer_ID
!strTicket = Me.strTicket
!Work_Order_Type = Me.Work_Order_Type
!Land_Location = Me.Land_Location
!lngInstaller = Me.lngInstaller
!Office_Comments = Me.Office_Comments
!RigName = Me.RigName
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngWOId = !Work_Order_ID
'Duplicate the related records: append query.
If Me.[fsubMonthEndDetails].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tblWorkOrderDetails] ( SKUNumber,_
Work_Order_ID, Unit_Description, Start_Date,_
Price, ysnOverRidePrice, curExceptionPrice, _
ysnDontInvoice, Dormant, dtmDateOut ) " & _
"SELECT " & lngWOId & " As Work_Order_ID, ( SKUNumber,_
Unit_Description, Start_Date, Price,
ysnOverRidePrice,_
curExceptionPrice, ysnDontInvoice, Dormant,
dtmDateOut " & _
"FROM [tblWorkOrderDetails] WHERE Work_Order_ID = " &
Me.lngWOId & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If
'Display the new duplicate.
Me.Bookmark = .LastModified
'MY code to generate the next WOSequence
Call fcnUpdate_tblWOSequence
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdDuplicate_Click"
Resume Exit_Handler
End Sub
FYI - code used for creating the WOSequence number located
in a module called basControl
(and no, I didn't write it myself....)
Public Function fcnUpdate_tblWOSequence(Optional strStart As String)
Dim strSql As Variant
Dim strLtr As String
Dim strNum As Variant
Dim strconcat As String
If Len(strStart) = 0 Then
strNum = Right(DLookup("seqno", "tblWOSequence"), 5)
Else
strNum = Right(strStart, 5)
End If
If strNum = "99999" Then
strNum = "00001"
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "00000")
strconcat = strNum
strSql = "UPDATE tblWOSequence set seqno = " & Chr$(39) & strNum &
Chr$(39)
CurrentDb.Execute strSql, dbFailOnError
End Function
I hope somebody can help me figure this out.
I am much better at reading code than writing it.
I am trying to incorporate Allen Brownes code to create a duplicate record
but
an adjustment I require is not firing for me.
If equipment has been rented during the month and not returned by month end
then it gets carried over to the next month on a new duplicated work order.
The code would be perfect for me except that I have to use a code generated
sequence number for the Work Order number.
I have tried using the same code that works when I originally create a work
order
but I am not getting anything to happen. When I look in tblWOSequence, I do
not
have a new Work Order number. Below is what I have for code:
Private Sub cmdDuplicate_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the
subform.
Dim strSql As String 'SQL statement.
Dim lngWOId As Long 'Primary key value of the new record.
Dim WOSeq As Integer 'Auto generated Work Order Number
'Save and edits first
If Me.Dirty Then
Me.Dirty = False
End If
'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else
'Duplicate the main record: add to form's clone.
' MY inserted code to capture the available sequence number
WOSeq = DLookup("seqno", "tblWOSequence")
With Me.RecordsetClone
.AddNew
!WOSeq = WOSeq
!Customer_ID = Me.Customer_ID
!strTicket = Me.strTicket
!Work_Order_Type = Me.Work_Order_Type
!Land_Location = Me.Land_Location
!lngInstaller = Me.lngInstaller
!Office_Comments = Me.Office_Comments
!RigName = Me.RigName
.Update
'Save the primary key value, to use as the foreign key for the
related records.
.Bookmark = .LastModified
lngWOId = !Work_Order_ID
'Duplicate the related records: append query.
If Me.[fsubMonthEndDetails].Form.RecordsetClone.RecordCount > 0
Then
strSql = "INSERT INTO [tblWorkOrderDetails] ( SKUNumber,_
Work_Order_ID, Unit_Description, Start_Date,_
Price, ysnOverRidePrice, curExceptionPrice, _
ysnDontInvoice, Dormant, dtmDateOut ) " & _
"SELECT " & lngWOId & " As Work_Order_ID, ( SKUNumber,_
Unit_Description, Start_Date, Price,
ysnOverRidePrice,_
curExceptionPrice, ysnDontInvoice, Dormant,
dtmDateOut " & _
"FROM [tblWorkOrderDetails] WHERE Work_Order_ID = " &
Me.lngWOId & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If
'Display the new duplicate.
Me.Bookmark = .LastModified
'MY code to generate the next WOSequence
Call fcnUpdate_tblWOSequence
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdDuplicate_Click"
Resume Exit_Handler
End Sub
FYI - code used for creating the WOSequence number located
in a module called basControl
(and no, I didn't write it myself....)
Public Function fcnUpdate_tblWOSequence(Optional strStart As String)
Dim strSql As Variant
Dim strLtr As String
Dim strNum As Variant
Dim strconcat As String
If Len(strStart) = 0 Then
strNum = Right(DLookup("seqno", "tblWOSequence"), 5)
Else
strNum = Right(strStart, 5)
End If
If strNum = "99999" Then
strNum = "00001"
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "00000")
strconcat = strNum
strSql = "UPDATE tblWOSequence set seqno = " & Chr$(39) & strNum &
Chr$(39)
CurrentDb.Execute strSql, dbFailOnError
End Function
I hope somebody can help me figure this out.
I am much better at reading code than writing it.