PC Review


Reply
Thread Tools Rate Thread

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

 
 
Jasonm
Guest
Posts: n/a
 
      22nd Sep 2004
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


 
Reply With Quote
 
 
 
 
Jasonm
Guest
Posts: n/a
 
      23rd Sep 2004
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


"Jasonm" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box for related records AND non-related records rocketD Microsoft Access 3 16th Nov 2009 07:34 PM
Related records depend on other related records BruceM Microsoft Access Database Table Design 2 31st Oct 2008 12:46 PM
Child/Master link set up but child records not showing Tmarkos Microsoft Access Forms 3 9th Oct 2008 05:33 PM
child of a child of a child on a form. wdsnews Microsoft Access 9 17th Mar 2008 09:27 PM
Allen Browne's function: "Duplicate record (and related child record)" -- my modified version doesn't work properly Tom Microsoft Access Form Coding 3 8th Feb 2006 07:55 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:10 PM.