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

Discussion in 'Microsoft Access VBA Modules' started by Jasonm, Sep 22, 2004.

  1. Jasonm

    Jasonm Guest

    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
     
    Jasonm, Sep 22, 2004
    #1
    1. Advertisements

  2. Jasonm

    Jasonm Guest

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

    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" <> wrote in message
    news:...
    > 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
    >
    >
     
    Jasonm, Sep 23, 2004
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John

    Copy a record and all related records

    John, Sep 16, 2003, in forum: Microsoft Access VBA Modules
    Replies:
    8
    Views:
    212
    Tim Ferguson
    Sep 18, 2003
  2. Guest

    Creating sub-records from a main record.

    Guest, Oct 21, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    206
  3. Guest

    One Related record will not show

    Guest, Feb 28, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    131
    Guest
    Feb 28, 2005
  4. Guest

    Code does not 'see' the required related record.

    Guest, Nov 11, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    9
    Views:
    165
    Dirk Goldgar
    Nov 12, 2007
  5. Jazz57

    Creating a backup record from an original record

    Jazz57, Dec 11, 2007, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    156
    Jazz57
    Dec 12, 2007
Loading...

Share This Page