Duplicate entry

[SHETTY]

Dear Sir,

I want to duplicate a invoice entry

In my invoice entry form there is one sub form for items (item log)

Main table bill & bill log are linked with Bill ID which is promery key in
the table 'Bill'

Kindly advise how I can duplicate one record including records of sub table

We need to change only 2 fields Viz bill no & bill date, rest of the data in
both the tables will be almost same

Please guide


Thanks & regards

Ramesh Shetty

 

[Graham Mandeno]

Hi Ramesh

Try using a function similar to this:

Public Function CloneBill(lBillID As Long) As Long
Dim rsOld As DAO.Recordset, rsNew As DAO.Recordset
Dim i As Integer, fTrans As Boolean, lNewID As Long
On Error GoTo ProcErr
Set rsOld = CurDb.OpenRecordset("Select * from [bill] where [Bill ID]=" &
lBillID)
Set rsNew = rsOld.Clone
BeginTrans
fTrans = True
With rsNew
.AddNew
For i = 0 To .Fields.Count - 1
Select Case .Fields(i).Name
Case "Bill ID"
lNewID = .Fields(i)
Case "Bill Date"
.Fields(i) = Date()
Case Else
.Fields(i) = rsOld(i)
End Select
Next
.Update
CurrentDb.Execute "Insert into [bill log] select " & lNewID _
& " as [Bill ID], [Field2], [Field3], ... " _ <<<<<<<<<<<<<<<<
& "from [bill log] where [Bill ID]=" & lBillID, dbFailOnError
End With
CommitTrans
fTrans = False
CloneBill = lNewID
ProcEnd:
On Error Resume Next
If fTrans Then Rollback
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

At the line marked <<<<<<<<<<<<<, enter the names of all the fields in [bill
log] except for [Bill ID] in place of [Field2], [Field3], ...

This function finds the master record for the given [Bill ID], creates a new
record and copies all the fields across except for [Bill ID] and [Bill Date]
and saves the new record. It then duplicates all the related records from
the [bill log] table, substituting the new [Bill ID] for he old one. If it
is successful then it returns the new [Bill ID] to the calling procedure.