Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordset navigation (2000 latest)

    My database has, among others, 2 tables...
    tblTrinity
    tblChildren
    They are joined by tblTrinity.UniqueId with tblChildren.MemberId

    In the data entry form, frmTrinity is the main form, [tblChildren subform] is the subform, linked as indicated in the previous line.

    When certain circumstances exist, I want to be able to copy a record from [tblChildren subform] and make a new record in the main form. There are 4 fields to copy. So far so good. I can do this with VBA code. My code follows...
    Private Sub cmdCopy_Click()
    Dim strTemp As String
    Dim strTemp2 As String
    Dim strTemp3 As String
    Me.Form![tblChildren subform].SetFocus
    DoCmd.GoToRecord , , acFirst
    Me.Form![tblChildren subform]!ChildName.SetFocus
    strTemp = Me.Form![tblChildren subform]!ChildName
    strTemp3 = Me.Form![tblChildren subform]!DateOfBirth
    Me.LastName.SetFocus
    strTemp2 = Me.LastName
    DoCmd.GoToRecord , "frmTrinity", acNewRec
    Me.LastName.SetFocus
    Me.LastName = strTemp2
    Me.FirstName.SetFocus
    Me.FirstName = strTemp
    Me.Person1DateOfBirth = strTemp3
    End Sub

    But there's another step I would like to do...
    Return to the exact place in the subform, from which the data was copied, and Delete that record from the subform (now that the entry is made in the main form, there is no purpose in leaving it in the subform).

    How do I get back to that exact place in the subform?

    Or is there an entirely better way to accomplish this task?

    (By the way, in the code I have not shown all the fields that are copied. If I can get the movement back to the record to delete it, I can plug in the rest of the fields. And the reason I am copying "field by field" is that there are number of additional fields in the main form and they will not all have entries at this point.)

    Any suggestions would be most appreciated.

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordset navigation (2000 latest)

    You will have to store the unique ID of the subform record in a variable, and use that later on when you want to delete the record. You could actually return to this record, but I don't think this is necessary. If tblChildren has a single ID field (primary key), you can do it as follows; I{ have assumed that this field is named ChildID and is numeric.

    ...
    ' additional declarations
    Dim lngChildID As Long
    Dim strSQL As String
    ...
    DoCmd.GoToRecord , , acFirst
    ' remember child ID
    lngChildID = Me![tblChildren subform]![ChildID]
    ...
    ' create new record in main form
    ...
    ' delete child record
    strSQL = "DELETE * FROM tblChildren WHERE ChildID = " & lngChildID
    DoCmd.RunSQL strSQL

    Note: you will be asked whether it is OK to delete the record; if you don't want this, replace the last line (DoCmd.RunSQL) by

    CurrentDb.Execute strSQL

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset navigation (2000 latest)

    Thanks, Hans.

    I'll give it a try.

    Tom

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset navigation (2000 latest)

    Hans
    The code you suggested works perfectly!!

    Thanks so much.
    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •