Duplicate Record, Main Form and its Sub Form details. 1

[uncleG]

Originally I used the wizard. It would create a new record with the ID and quQuoteNo only

Next I followed MS Article 208824 rev 3 as a guideline to create duplicates of Request for Quotation.
My Problem appears to be that the pk quQuoteNo does not increase by 1 and I am unable to figure out why. The quQuoteNo (pk) is normally created by the On Current event of the form using the following code without incident. This is a single user application.
tblQuote and tblQuoteDetail have a one to many relationship via quQuoteNo. I am Using A2K

My goal is to create a new record identical to the original with ConID blank, this should be able to be performed 2 or 3 times in order to send to several vendors.

Looking for Help and Thanks in advance,
Uncle G


************'Normal source of the quQuoteNo on Form.
Private Sub Form_Current()
If Me.NewRecord = True Then
intmax = Nz(DLookup("max(quQuoteNo)", "tblQuote"), 0)
intmax = intmax + 1
Me.quQuoteNo = intmax
End If
End Sub
************
From a command button on the Form (frmQuote) runs the following code

Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![quQuoteNo]

' Add new record to end of Recordset object.
' there is an ID autonumber field which is not included below
With Rst
.AddNew
intmax = Nz(DLookup("max(quQuoteNo)", "tblQuote"), 0)
intmax = intmax + 1
Me.quQuoteNo = intmax
!quQuoteNo = intmax 'I added these 4 lines to get new # to no avail
!quDate = Me!quDate
!ConID = Me!ConID
!quAtt = Me!quAtt
!lupType = Me!lupType
!quTerm = Me!quTerm
!lupFOB = Me!lupFOB
!quExpires = Me!quExpires
!quNotes = Me!quNotes
!quOurNotes = Me!quOurNotes
!lupStatus = Me!lupStatus
!quVendRef = Me!quVendRef
!quCustRef = Me!quCustRef
.Update ' Save changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

' Run the qryDuplicateQuoteDetails append query which selects all
' detail records that have the quQuoteNo stored in the form's
' Tag property and appends them back to the detail table with
' the quQuoteNo of the duplicated main form record.
'Currently quQuoteNo does not increament I suspect because it occurs on current in form.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDuplicateQuoteDetails"
DoCmd.SetWarnings True

'Requery the subform to display the newly appended records.
Me![frmQuoteDetailsub2].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub

The append query follows:
INSERT INTO tblQuoteDetail ( qusLine, qusQnty, qusDisc, qusPN2, qusProcess, qusUnitPrice, qusDelivery, qusAccepted, qusMemo, qusLotChg, qusExpChg, qusOthChg, qusPN, quQuoteNo )
SELECT tblQuoteDetail.qusLine, tblQuoteDetail.qusQnty, tblQuoteDetail.qusDisc, tblQuoteDetail.qusPN2, tblQuoteDetail.qusProcess, tblQuoteDetail.qusUnitPrice, tblQuoteDetail.qusDelivery, tblQuoteDetail.qusAccepted, tblQuoteDetail.qusMemo, tblQuoteDetail.qusLotChg, tblQuoteDetail.qusExpChg, tblQuoteDetail.qusOthChg, tblQuoteDetail.qusPN, CLng(Forms!frmQuote!quQuoteNo) AS NewQuoteNo
FROM tblQuoteDetail
WHERE (((tblQuoteDetail.quQuoteNo)=[Forms]![frmQuote].[Tag]));

 

[Zion7]

I've never seen this before, doesn't mean it's
wrong though?

Maybe this,
intmax = Nz(DMax("quQuoteNo", "tblQuote"), 0)

 

[uncleG]

Thanks, Zion7

"Maybe this", forced me to look where I hadn't before.

I replaced this:

With Rst
.AddNew
intmax = Nz(DLookup("max(quQuoteNo)", "tblQuote"), 0)
intmax = intmax + 1
Me.quQuoteNo = intmax
!quQuoteNo = intmax 'I added these 4 lines to get new # to no avail
!quDate = Me!quDate

With This:

With Rst
.AddNew
!quQuoteNo = Nz(DMax("quQuoteNo", "tblQuote"), 0) + 1
!quDate = Me!quDate

Runs like a champ, Thanks Again
UncleG