Help! Strange problem with code not creating a record with related child records

  • Thread starter Thread starter Jasonm
  • Start date Start date
J

Jasonm

I am hoping that someone will be able to point me in the right direction
with this problem...
I am using the following code to duplicate a record AND all of the
associated child records that go along with it.

The problem is that on one of my win 2K boxes the code gives me an error
that the record has been created but there are no child records associated
with the record (which there are!)

I am using linked tables and the code works on another win 2K box and two XP
boxes one running access 2003 another running the version that came with
Office XP (2002?) the 2K machines are running access 2000.

The references in the vba projects are all set the same so I must be missing
something... What should I look for?

Thanks for the help! (Code follows...)

Private Sub Active_Click()

If Active = True Then

CompletionDate.Value = (Nz(CompletionDate.Value, Format(Now,
"mm/dd/yy")))

Dim sSQL As String
Dim db As DAO.Database

Dim UniqueID As Long 'this is the new ID number
Dim SQNum As Integer

Set db = DBEngine(0)(0)

If Me.Dirty Then 'Save the current record if there are unsaved
changes
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate - or exit and try again,
this is a new record"
Else
'Begin to duplicate the MAIN record (not the child records yet)
SQNum = Nz(DMax("SeqNumber", "WorkOrders")) + 1

With Me.RecordsetClone
.AddNew
!Name = Me.WOName
!Details = Me.Details
!RepeatIntervalamount = Me.RepeatIntervalamount
!RepeatIntervalunit = Me.RepeatIntervalunit
!WODate = Me.CompletionDate
!DepartmentID = Me.DepartmentID
!SeqNumber = SQNum
.Update
.Bookmark = .LastModified
UniqueID = !KeyField

'Duplicate the CHILD records
If Me.WO2Equip.Form.RecordsetClone.RecordCount > 0 Then

sSQL = "INSERT INTO WOEquipment(WorkOrderID,EquipmentID) " &
"SELECT " & UniqueID & " As NewWorkOrderID, WOEquipment.EquipmentID " &
"FROM WorkOrders INNER JOIN WOEquipment ON WorkOrders.KeyField =
WOEquipment.WorkOrderID " & " WHERE (((WOEquipment.WorkOrderID) = " &
Me.KeyField & "));"

db.Execute sSQL, dbFailOnError
Else
MsgBox "Main record was duplicated, but there were no related CHILD
records to be duplicated."

End If
'Display the duplicated record
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End If


End Sub
 
I just wanted to let the group know that this problem has resolved itself.
After running windows and office update the error no longer occurs! Yeah (I
guess!)

Thanks,
Jason
 
Back
Top