Inserting Data to Same Table (1 Viewer)

[mike60smart]

Hi Everyone

I have a Main Form named "frmQuotes"
With a subform named "frmQuotePartNoSubForm" whose Record Source is "tblQuotationPartNo" which displays as follows:-

The fields in the table are :-
QuotePartNoID - PK
QuoteID - FK
PartNo - Text

I have some VB on a Command Button which generates a New Quote with a Revision letter.

This works just fine and creates a new record with the new revision.

What I am trying to do is append the Part No's to tblQuotationPartNo but with a new QuoteID
that has been obtained by using the following Reference in an Unbound Textbox named txtQuoteID:-
=[Forms]![frmQuotes]![frmQuotationPartNosubform].[Form]![LastOfQuoteID]

The Record Source of the Form "frmQuotationPartNosubform" is as follows:-

SELECT TOP 1 Last(tblQuotation.QuoteID) AS LastOfQuoteID FROM tblQuotation ORDER BY Last(tblQuotation.QuoteID) DESC;

I have tried to make this process as clear as possible and hope you are following.
How can I do this by using a Command Button on either the Main Form or Subform ?
Any help appreciated

 

[bastanu]

If QuoteID is an autonumber (which it should) use Max instead of Last to get the newly created one. And you don't really need the form, just use DMax in the insert SQL statement. You can create a new insert query in design view and add a calculated field using dMax to get the new quote id and append that to the FK field but also have the QuoteID field from the table in the query because that is where you need to add the criteria (=the original QuoteID that hopefully you still have it loaded somewhere on your form); don't append this field. Once the query is OK you can save it and call it from your button or copy its SQL and call it using CurrentDB.Execute (you will need to change it a bit to isolate the variable for the original ID).

Cheers,

 

[Pat Hartman]

Jet and ACE, unlike SQL Server have NO certain way of obtaining the last autonumber inserted by "you". In a multi-user environment, multiple people are all adding records and timing is everything. If you both hit insert at the same time, one record will be saved and then the next. Even if your next instruction is a dMax() to obtain the "last" autonumber ID, it might not be your record. THEREFORE, if you are using Jet/ACE, the safest method is to copy the parent record using DAO. That allows you to capture the specific RecordID that "you" just entered. Then to copy the child records, you can use an append query that takes TWO argument's. It takes the OLD OrderID to select the records to be copied and it uses the NEW OrderID in the append clause as the FK rather than the FK from the old record.

In the rare cases where you have more than two levels, the process is:
1. copy the parent level using DAO so you can obtain the new ID
2. Using two recordsets, create a loop that reads the second level table using the OldID to select the records from the first recordset. Then use .AddNew on the second recordset for each record using the NewID as the FK
3. the third or "bottom" level can always be copied using an append query with two arguments.

I've never had four levels but they would be handled by repeating step 2 for the 3rd table. So all the levels between 1 and bottom use a DAO loop to copy so they can append the correct FK value.

 

[ebs17]

Jet and ACE, unlike SQL Server have NO certain way of obtaining the last autonumber inserted by "you".

This statement is incorrect.

db.Execute strSQL, dbFailOnError
lngArtIDNew = db.OpenRecordset("Select @@Identity")(0)

 

[The_Doc_Man]

I believe that a comma is missing for Pat's statement, and that it is correct if you supply the comma.

Jet and Ace, unlike SQL Server, have NO certain way of obtaining the last autonumber inserted by "you".

You could execute a query if your record contains your ID, in which case you COULD find the record with the highest Autonumber ID AND your ID. But neither Jet nor ACE has the "@@Identity" construct. You have to actually ask the right question of SQL (not of Jet or ACE).

 

[ebs17]

The instruction shown works on an access file as a backend. Try it.
What SQL other than Jet-SQL would be involved, and what would Jet/ACE understand other than Jet-SQL?

 

[mike60smart]

Jet and ACE, unlike SQL Server have NO certain way of obtaining the last autonumber inserted by "you". In a multi-user environment, multiple people are all adding records and timing is everything. If you both hit insert at the same time, one record will be saved and then the next. Even if your next instruction is a dMax() to obtain the "last" autonumber ID, it might not be your record. THEREFORE, if you are using Jet/ACE, the safest method is to copy the parent record using DAO. That allows you to capture the specific RecordID that "you" just entered. Then to copy the child records, you can use an append query that takes TWO argument's. It takes the OLD OrderID to select the records to be copied and it uses the NEW OrderID in the append clause as the FK rather than the FK from the old record.

In the rare cases where you have more than two levels, the process is:
1. copy the parent level using DAO so you can obtain the new ID
2. Using two recordsets, create a loop that reads the second level table using the OldID to select the records from the first recordset. Then use .AddNew on the second recordset for each record using the NewID as the FK
3. the third or "bottom" level can always be copied using an append query with two arguments.

I've never had four levels but they would be handled by repeating step 2 for the 3rd table. So all the levels between 1 and bottom use a DAO loop to copy so they can append the correct FK value.

Hi Pat

Would it be possible for you to give me an example of both of the following?
1. copy the parent level using DAO so you can obtain the new ID
2. Using two recordsets, create a loop that reads the second level table using the OldID to select the records from the first recordset. Then use .AddNew on the second recordset for each record using the NewID as the FK

 

[Pat Hartman]

It's not as hard as you are envisioning. Notice that the append query to append accessories (level 3) runs inside the loop for level 2 because you need to append accessories for each row of level 2.

Private Sub cmdCopyQuote_Click()
On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the subform.
    Dim str2Sql          As String
    Dim strSQL           As String  
    Dim NewQuoteID       As Long       'FK value of the new record.
    Dim OldQuoteID       As Long
    Dim NewQuoteDetailID As Long
    Dim db               As DAO.Database
    Dim qd               As DAO.QueryDef
    Dim FromQD           As DAO.QueryDef
    Dim FromRS           As DAO.Recordset
    Dim ToTD             As DAO.TableDef
    Dim ToRS             As DAO.Recordset

    '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."
        Exit Sub
    End If  

    'Duplicate the main record: add to form's clone.
    OldQuoteID = Me.QuoteID
    With Me.RecordsetClone
        .AddNew
        !CustID = Me.cboCustID
        !JobName = Me.JobName
        !Notes = Me.Notes
        !TermsID = Me.TermsID
        !ShippingID = Me.ShippingID
        !CustConID = Me.cboCustConID
        !CustLocID = Me.cboCustLocID
        !QuoteNumber = Nz(DMax("QuoteNumber", "tblQuotes"), 0) + 1
        !QuoteDate = Date
        !Expires = Date + 30
        .Update

        'Save the primary key value, to use as the foreign key for the related records.
        .Bookmark = .LastModified
        NewQuoteID = !QuoteID
        'Display the new duplicate.  This repositions the form to the new Order
        Me.Bookmark = .LastModified
    End With

        'Duplicate the related Item records using DAO recordset.  Append Accessories with  append query inside loop
        Set db = CurrentDb()
        Set ToTD = db!tblquoteDetails
        Set ToRS = ToTD.OpenRecordset
        Set FromQD = db.QueryDefs!qQuoteCopyDetails 'just a select query with a where
            FromQD.Parameters!EnterOldQuoteID = OldQuoteID
        Set FromRS = FromQD.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        With FromRS
            .MoveFirst
            Do Until .EOF = True
                ToRS.AddNew
                ToRS!ItemNo = !ItemNo
                ToRS!EstID = !EstID
                ToRS!ModelNo = !ModelNo
                ToRS!Description = !Description
                ToRS!Qty = !Qty
                ToRS!Price = !Price
                ToRS!AccessPrice = !AccessPrice
                ToRS!QuoteID = NewQuoteID
                NewQuoteDetailID = ToRS!QuoteDetailID
                ToRS.Update              
               
                ''  copy accessories
                Set qd = db.QueryDefs!qCopyAppendQuoteAcc
                    qd.Parameters!EnterOldQuoteDetailID = !QuoteDetailID
                    qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
                    qd.Execute dbSeeChanges
           
                .MoveNext
            Loop
        End With

        Me.SfrmQuoteDetails.Requery  '' Show the newly copied records
        ToRS.Close
        FromRS.Close
        Set ToRS = Nothing
        Set FromRS = Nothing
        Set db = Nothing

Exit_Handler:
    Exit Sub

Err_Handler:
     Select Case Err.Number
        Case 3021, 2501   ' update cancelled
            Resume Exit_Handler
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Handler
    End Select

End Sub

This is the append query. Green = the FK which will be inserted. Orange = the OldQuoteID for the where clause

INSERT INTO tblquoteacc ( QuoteDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price )
SELECT [EnterNewQuoteDetailID] AS Expr1, tblQuoteAcc.ItemNo, tblQuoteAcc.EstID, tblQuoteAcc.ModelNo, tblQuoteAcc.Description, tblQuoteAcc.Qty, tblQuoteAcc.Price
FROM tblQuoteAcc
WHERE (tblQuoteAcc.QuoteDetailID = [EnterOldQuoteDetailID];