M
Marianne
Hello!
I am trying to duplicate a form and subform details as described in KB
208824. The problem is that my Invoice form is already a subfrom of another
main form. As follows:
DataEntry From
Invoice subform
Invoice details subform
When the code is run as in the KB article it thinks the last last inv number
is within the invoice subform ie "4182". so it tries to append the new record
with number 4183. however in the invoice table the last invoice number is
actually 5000 and 4183 has already been used so i get the message "cannot
update the table due to key violations....."
Is there a way to modify this to get the "next" correct invoice number?
here is the code as I have it:
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
Me.Tag = Me![Invoice_Number]
With Rst
.AddNew
!Invoice_Number = Me!Invoice_Number
!Invoice_Date = Me!Invoice_Date
!Invoice_Name = Me!Invoice_Name
!Entity = Me!Entity
!CustNum = Me!CustNum
!DocCode = Me!DocCode
!Current_Month_Payment = Me!Current_Month_Payment
!Pymt_Date = Me!Pymt_Date
!Pymt_Amt = Me!Pymt_Amt
!FY_Pymt = Me!FY_Pymt
!Invoice_File = Me!Invoice_File
!Support_Doc = Me!Support_Doc
!Ref_Num = Me!Ref_Num
!Department = Me!Department
!Person_Dept = Me!Person_Dept
!POS = Me!POS
!POS_Beg = Me!POS_Beg
!POS_End = Me!POS_End
!Memo = Me!Memo
!Bill_To = Me!Bill_To
!Bill_From = Me!Bill_From
!Attn = Me!Attn
!Address1! = Me!Address1
!Address2! = Me!Address2
!City = Me!City
!St = Me!St
!Zip = Me!Zip
!Terms = Me!Terms
!Femail = Me!Femail
!Fphone = Me!Fphone
!Ffax = Me!Ffax
.Update
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_DuplicateInvoice"
DoCmd.SetWarnings True
Me![frm_Invoice_Detail].Requery
Exit_btnduplicate_Click:
Exit Sub
Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub
I am trying to duplicate a form and subform details as described in KB
208824. The problem is that my Invoice form is already a subfrom of another
main form. As follows:
DataEntry From
Invoice subform
Invoice details subform
When the code is run as in the KB article it thinks the last last inv number
is within the invoice subform ie "4182". so it tries to append the new record
with number 4183. however in the invoice table the last invoice number is
actually 5000 and 4183 has already been used so i get the message "cannot
update the table due to key violations....."
Is there a way to modify this to get the "next" correct invoice number?
here is the code as I have it:
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
Me.Tag = Me![Invoice_Number]
With Rst
.AddNew
!Invoice_Number = Me!Invoice_Number
!Invoice_Date = Me!Invoice_Date
!Invoice_Name = Me!Invoice_Name
!Entity = Me!Entity
!CustNum = Me!CustNum
!DocCode = Me!DocCode
!Current_Month_Payment = Me!Current_Month_Payment
!Pymt_Date = Me!Pymt_Date
!Pymt_Amt = Me!Pymt_Amt
!FY_Pymt = Me!FY_Pymt
!Invoice_File = Me!Invoice_File
!Support_Doc = Me!Support_Doc
!Ref_Num = Me!Ref_Num
!Department = Me!Department
!Person_Dept = Me!Person_Dept
!POS = Me!POS
!POS_Beg = Me!POS_Beg
!POS_End = Me!POS_End
!Memo = Me!Memo
!Bill_To = Me!Bill_To
!Bill_From = Me!Bill_From
!Attn = Me!Attn
!Address1! = Me!Address1
!Address2! = Me!Address2
!City = Me!City
!St = Me!St
!Zip = Me!Zip
!Terms = Me!Terms
!Femail = Me!Femail
!Fphone = Me!Fphone
!Ffax = Me!Ffax
.Update
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_DuplicateInvoice"
DoCmd.SetWarnings True
Me![frm_Invoice_Detail].Requery
Exit_btnduplicate_Click:
Exit Sub
Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:
End Sub