Duplicate button on a form and subform.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top