Problem with Allen Browne Duplicating code

  • Thread starter Thread starter CJ
  • Start date Start date
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 think that this part:

'MY code to generate the next WOSequence
Call fcnUpdate_tblWOSequence

Should be BEFORE this part:

' MY inserted code to capture the available sequence number
WOSeq = DLookup("seqno", "tblWOSequence")

Or else you will be trying to insert a duplicate.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com


CJ said:
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.
Click to expand...
 
Hi Bob, thanks for popping in

The way that I have the code set up elsewhere is that the
sequence number that is currently sitting in the table is the
next new one. It's just the way it was done and it seems to work.

I have manually changed the number to something out of sequence
and it still does not pick it up so, unfortunately, I don't think
that is the problem.

Any other ideas?

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Bob Larson said:
I think that this part:

'MY code to generate the next WOSequence
Call fcnUpdate_tblWOSequence

Should be BEFORE this part:

' MY inserted code to capture the available sequence number
WOSeq = DLookup("seqno", "tblWOSequence")

Or else you will be trying to insert a duplicate.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com


CJ said:
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.
Click to expand...
Click to expand...
 
Back
Top