Duplication of record and sub-records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've never coded VB in my LIFE here =) Good times. I found this code which I
believe was posted by Allen Browne - thanks Allen.

Anywho, I have 3 subforms. I want to create a duplicate invoice with
duplicate records. The only things that get changed are the Invoice ID and
the Date (which is in this code already, nice).

I think I've worked out what everything does. How do I add the ability to
duplicate the records in all three subforms?

Also, from the line of code "If
Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then" what does
"fInvoice" represent?

Thanks in advance for any help. Once I get my head around this then the rest
of the project *might* actually build itself. Here's hoping.

<downloaded code>

"This example duplicates the invoice in the main form, and the line items in
the subform. It uses the RecordsetClone of the form to duplicate the main
record, so you can choose the fields selectively. For example, the new
invoice gets today's date.

Private Sub cmdDupe_Click()
Dim sSQL As String
Dim db As DAO.Database
Dim lngInvID As Long

Set db = DBEngine(0)(0)

If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record
With Me.RecordsetClone
.AddNew
!InvoiceDate = Date
!ClientID = Me.ClientID
!Ref = Me.Ref
'etc for other fields
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID

'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
sSQL = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount) " & _
"SELECT " & lngInvID & " As NewInvoiceID,
tInvoiceDetail.Item, tInvoiceDetail.Amount FROM tInvoiceDetail " & _
"WHERE (tInvoiceDetail.InvoiceID = " & Me.InvoiceID &
");"
db.Execute sSQL, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records.", vbInformation, "Information"
End If

'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If

Set db = Nothing
End Sub
 
In the reference:
Me.fInvoiceDetail.Form.RecordsetClone.RecordCount
"fInvoiceDetail" represents the name of your subform control. That may not
be the same as the name of the form that is loaded into the control (its
SourceObject). If you are not sure, open the main form in design view,
right-click the edge of the subform control, and choose Properties.

If you have 3 subforms, you will need to repeat the part under the heading:
'Duplicate the related records.
twice more. To help you figure out the SQL string you need to create, you
can mock up a query to insert some literal values into the other related
table as well. Make sure it is in Append Query (Append on Query menu in
query design view). Then switch the query it SQL View (View menu), and you
have a sample of the string you are trying to create.

To help you debug this, add the line:
Debug.Print sSQL
After running the code you can open the Immediate window (Ctrl+G) to see
what actually executed.
 
hi,

i need to create a subset of records (based on the values in a sub-form). i
have brought this to the attention of the "Access Forms Coding" area in the
subject line "automatic record propagation" but i started to feel as if i
needed to explore beyond that venue and chanced to find this thread. hope
it's ok.
 
Back
Top