Copy Subform rs to New Record

L

LBinGA

Registered User.
Local time
Today, 15:20
Joined
Oct 23, 2013
Messages
17
Hello! I am trying to copy a subform record set from one record to the next record. The data in the main table is copied over using this method;

Code: 
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
 
v1 = Me![Today's Date].Value
v2 = Me!Insured.Value
v3 = Me!City.Value
v4 = Me!State.Value

RunCommand acCmdRecordsGoToNew
  
Me![Today's Date].Value = DateValue(Now)
Me!Insured = v2
Me!City = v3
Me!State = v4

 End Sub
Can I integrate the copying of the subform data to the new record with this or do I need something different, and if so what?

It's a many to many relationship and I've tried adding the following line to the code:
v5=Me![Endorsements Umb XS].[UmbID]
same=v5

and it works but it only copies the first record out of the set.:eek:

Any help appreciated.
Thanks,
LBinGA:banghead:
 
Thanks, I did see that but was unsure of a couple of things:
1. I may be wrong but it appears to duplicate the entire record of the main form, which I don't want to do. I want some values of the main form and all values of the subform.
2. In either case, I don't know where to place this in relation to my current code.

Thanks,
LBinGA
 
For the main record you can choose the fields you include. It would replace your current code.
 
Yes! I read it a little closer and see where I can just copy over the fields I'd like.

I would like to send over the proper subform rs conditionally, based on the [Type of Insurance] field entry. I have 3 subforms but only one will be completed based on what is in the [Type of Insurance] field.

Once I figure out how to do one, I suppose, I'll backtrack and add the conditions.

Thanks for the response.
LBinGA:banghead:
 
Having different tables might be a normalization problem, but you can test that field and adjust the SQL accordingly. If you put the name of the table into a variable while testing:

strSql = "INSERT INTO [" & VariableName & "] (..."
 
I've reviewed Mr. Browne's code and tried to put it into what makes sense for my db. It copies the main form but the Subform does not copy. I'm certain I don't have my ID path accurate but I don't know what to change. Here's what I have thus far, which replaces the code I had prior (original post).
Code: 
Private Sub BtnCopyIns_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 InsuredID As Long       'Primary key value of the new record.
    
    'Save any 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.
        With Me.RecordsetClone
            .AddNew
                !Insured = Me!Insured.Value
                !City = Me!City.Value
                !State = Me!State.Value
                !Operations = Me!Operations.Value
                !Address = Me!Address.Value
                                               
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            NewID = !InsuredID
            
            'Duplicate the related records: append query.
            If Me.[Endorsements Umb subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [TableInsuredUmb] ( InsuredID, UmbID ) " & _
                    "SELECT " & InsuredID & " As NewID, UmbID " & _
                    "FROM [TblInsuredUmb] WHERE InsuredID = " & Me.InsuredID & ";"
           
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub

Bold signifies Key:
I have COMMERCIAL UMBRELLA TABLE (InsuredID) linked to TblInsuredUmb (InsdUmbID (one to many), UmbID, InsuredID) linked to tblUmb (UmbID, UmbEndts) (many to one).

Any help would be appreciated. thanks,
LB in GA
 
Anyone?
The db is attached.
I've changed the code to something I found here from back in 2012 as follows but it is still not working. It's found on the Copy to Ren button on the Quote Sheet.

Code: 
Private Sub BtnCopyIns_Click()

 On Error Resume Next
    Err.Clear
    If Me.Dirty Then
        If MsgBox("Save this record first?", vbYesNo) = vbYes Then
            RunCommand acCmdSaveRecord
            If Err Then
                MsgBox "Could not save record." & vbCrLf & "Reason given:" & vbCrLf & vbCrLf & Err.Description
                Exit Sub
            End If
        Else
            Exit Sub
        End If
    End If
    Dim iNewID As Long
    DBEngine.BeginTrans
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Commercial Umbrella Table", dbOpenDynaset)
    With rs
        .AddNew
                !Insured = Me!Insured.Value
                !City = Me!City.Value
                !State = Me!State.Value
        .Update
        .Bookmark = .LastModified
        iNewID = !InsuredID
        .Close
    End With
    Set rs = Nothing
    CurrentDb.Execute "INSERT INTO tblInsuredUmb (UmbID) SELECT " & iNewID & " WHERE InsuredID = " & Me.InsuredID
    If Err Then
        MsgBox "Could not duplicate record." & vbCrLf & "Reason given:" & vbCrLf & vbCrLf & Err.Description
        DBEngine.Rollback
    Else
        DBEngine.CommitTrans
        Me.Requery
        DoCmd.GoToRecord acDataForm, Me.Name, acLast
    End If
End Sub

thanks in advance,
LBinGA
 

Attachments

Issues with both. In the sample, try

Code: 
  strSQL = "INSERT INTO [TblInsuredUmb] ( InsuredID, UmbID ) " & _
           "SELECT " & iNewID & " As NewID, UmbID " & _
           "FROM [TblInsuredUmb] WHERE InsuredID = " & Me.InsuredID
  CurrentDb.Execute strSQL
 
Hallelujah, pbaldy! That worked! Thank you SO much!

It is snapping over to the last record automatically.

How can I set the focus to the newly created record? Remove the "requery"?

Lastly, I have 3 conditions, based on the Type of Insurance chosen. I will try to morph what you did into those three conditions afterwards.

Thanks again!
LBinGA :D
 
I added this to after the line:
CurrentDb.ExecutestrSql

Code: 
Dim lngPK As Integer 'change data type if yours is different
'set variable to current record ID
lngPK = Me.InsuredID

Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "InsuredID = " & lngPK
   If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With

It makes the copy, but it continues to send me to the last record?? I also tried setting it to UmbID but that didn't work at all.

Thanks!
LBinGA
 
The new record is stored in iNewID, is it not?
 
pbaldy said:
The new record is stored in iNewID, is it not?
Click to expand...

Yes. I tried iNewID, me.iNewID and me.NewID. Neither worked.

The error is:

Compile Error:
Method or data member not found.

It stops here:
lngPK = Me.iNewID

Thanks!!
LBinGA
 
Not a form reference, the variable you populated here:

iNewID = !InsuredID

so

lngPK = iNewID
 
Ok, thanks! Attached a copy of db with the new code with what you gave me as follows:
Code: 
lngPK = iNewID

Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "InsuredID = " & lngPK
   If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With

The error message is:
The MS Access db engine does not recognize 'iNewID' as a valid field or expression.

Do I maybe have it in the wrong place?

Note: I've added The Policy Type conditions. All 3 work exceedingly well with this one exception of staying on the New Record.

Thanks again!!
LBinGA
 

Attachments

Your code has

.FindFirst "iNewID = " & lngPK

should be what you have above:

.FindFirst "InsuredID = " & lngPK
 
Perfect! I had it that way for two of the conditions but not the first one. I've now changed it.

In any case, with that change, it generates the copy but then gives the MsgBox "Record not found!" and then reverts once again to the last record in the set.

"It's always something!!!" ~Roseann Roseannadanna

So silly, this small thing! I'm very grateful for your assistance.

LBinGA
 
Ah, because you used a transaction and have the CommitTrans after the bookmark part, the record doesn't actually exist yet.
 
Hot D@MN! That was it! I moved the CommitTrans up over the With containing the .FindFirst and it works!

I'll call this solved and here is the final code for those interested.

Code: 
Private Sub BtnCopyIns_Click()

 On Error Resume Next
    Err.Clear
    If Me.Dirty Then
        If MsgBox("Save this record first?", vbYesNo) = vbYes Then
            RunCommand acCmdSaveRecord
            If Err Then
                MsgBox "Could not save record." & vbCrLf & "Reason given:" & vbCrLf & vbCrLf & Err.Description
                Exit Sub
            End If
        Else
            Exit Sub
        End If
    End If

    Dim iNewID As Long
    DBEngine.BeginTrans
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Commercial Umbrella Table", dbOpenDynaset)
    With rs
        .AddNew
                !Insured = Me!Insured.Value
                !City = Me!City.Value
        .Update
        .Bookmark = .LastModified
        iNewID = !InsuredID
        .Close
    End With
    Set rs = Nothing
   strSql = "INSERT INTO [TblInsuredUmb] ( InsuredID, UmbID ) " & _
           "SELECT " & iNewID & " As NewID, UmbID " & _
           "FROM [TblInsuredUmb] WHERE InsuredID = " & Me.InsuredID
   CurrentDb.Execute strSql
  MsgBox "You have just created a copy of a record.  Please make the necessary changes to that record before proceeding!"
   
   
    If Err Then
        MsgBox "Could not duplicate record." & vbCrLf & "Reason given:" & vbCrLf & vbCrLf & Err.Description
        DBEngine.Rollback
    Else
        DBEngine.CommitTrans
        Me.Requery
        DoCmd.GoToRecord acDataForm, Me.Name, acLast
    End If
    
'***To remain on new record***
Dim lngPK As Long
lngPK = iNewID
Me.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "InsuredID = " & lngPK
   If .NoMatch Then 'just in case another user deleted it in the interim
     MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With
End Sub

I've been trying all sorts of things, not noticing that it was Requerying after the Requery. DOH! :banghead:
Moving it was the key. Thank you SO, SO MUCH!!!
:D LBinGA :D
 
You must log in or register to reply here.

Similar threads

N
Solved VBA blocking the saving of foreign key and line numbering in the subform and child table
Replies
7
Views
362
Minty
Minty
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
815
Minty
Minty
N
Solved How to create new record from filtered record
2
Replies
33
Views
2,060
nector
N
N
Solved How to update a currentstock control in the subform after a requery from the parent form
Replies
13
Views
1,498
Mark_
M
A
Solved Copying data from Quote-line-items Subform to Sales-Order-line-items Table : Error 3022
Replies
5
Views
1,007
alan2013
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom