This code duplicates the invoice in a form, along with the detail lines in
the subform. It uses DAO to duplicate the main record so you can get the
InvoiceID for the new record, since you need that value for the child
records. The child records are duplicated with an Append query statement.
The code assumes a command button on the main form.
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 'Save any changes.
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
'etc for other fields.
.Update
.Bookmark = .LastModified
lngInvID = !InvoiceID 'The new primary key value.
'Duplicate the related records.
If Me.fInvoiceDetail.Form.RecordsetClone.RecordCount > 0 Then
sSQL = "INSERT INTO tInvoiceDetail ( InvoiceID, Item,
Amount ) " & _
"SELECT " & lngInvID & " As NewInvoiceID, Item, Amount "
& _
"FROM tInvoiceDetail " & _
"WHERE (InvoiceID = " & Me.InvoiceID & ");"
db.Execute sSQL, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If
'Display the duplicate.
Me.Bookmark = .LastModified
End With
End If
Set db = Nothing
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"pwys" <niteking@hotmail.com> wrote in message
news:3feb9cd9$1_2@news.tm.net.my...[color=blue]
>
> WOndering if there anyone could help me with this.
>
> I have a Primary & a secondary table with the unique key (InvoiceNo)
>
> Waht i want is to make duplicate copy of a selected invoice no (both[/color]
Primary[color=blue]
> & secondary) into a new invoice no.[/color]
Duplicating record 
