Copy records!

[Priya]

Hi,

I have a " Revise quote " main form which when I type in
quote number will display related info (revision number,
customer info etc)thats stored. This main form also has a
subform which displays all the items that were quoted for
that quote number with details like unit & total price.

Problem no 1:

When I double click a record on the subform (one item) it
opens a form with all the details of that item.

But when I change some details on this pop up form and
close it, I want my changed information to be displayed on
the subform.

Right now, only when I close this main form, open it
again , the new values for that record shows on the
subform.

Problem no2:

How can I copy one record as a whole with all the fields -
using visual basic code?

Should I open that table, and copy each and every field
into the new record or is there an easier way to copy
one record as a whole?

Thanks in advance

 

[Allen Browne]

A1: To show the changes, requery the form:
Me.Requery
Note that this will cause you to move to the first record.

A2: Duplicate the record.
I think you are saying you want to duplicate the main record and all the
related records in the subform? If so, you need to use DAO to duplicate the
main record so you can get the ID of the new record, because you need that
value to create the related records. The related records can be generated
with an Append query statement.

The example below shows how to do this for an invoice and the invoice detail
records. Change the names to match your field and table names. If you have
trouble figuring out the append query statement, mock up a query, change it
to an Append query (Append on query menu), put in some dummy criteria, and
then switch it it to SQL View (View menu) for an example of the statement
you need.

------------code starts-----------------
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

'Duplicate the related records.
If Me.frmInvoiceDetail.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."
End If

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

Set db = Nothing
End Sub
------------code ends-----------------

 

[Allen Browne]

Priya, I'm still not clear on your data structure here.

You have two tables, with fields like this:
- Quote: QuoteNumber, Customer, QuoteDate,...
- QuoteDetail: ID, QuoteNumber, Item, Price, Qty.
In which table is the QuoteRevision field?

I think you are asking that any time any QuoteDetail line gets edited, you
want the software to cancel the edit, and make a new revision of the Quote
record and all of its QuoteDetail line items as well???

Personally, I would find that rather confusing. Would you accept a "Revise
Quote" command button that creates a duplicate of the existing quote and all
its line items, and then allows the user to make the changes to the newly
duplicated revision?

Let me know if I have not grasped what you are trying to do: especially how
the QuoteRevision number fits in.