Display duplicate record

[Johnthelleader]

I have a popup form called ADD NEW INVOICE that a user enters invoice data. This gets stored in the table INVOICES. Users can then view the invoices already entered in the form INVOICES. The first field in the popup form , INVOICE NUMBER, has a Before Update function that warns the user if an invoice number has already been entered. This works fine. What would be great is to then display that invoice that has already been entered in a new form. I have used some code using Bookmark, but it did not work. I have tried to find it in this forum, but to no avail. I would be grateful for any help. This is the code I already have:

Private Sub Invoice_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo Invoice_Number_BeforeUpdate_Err

If (DCount("[Invoice Number]", "Invoices", "[Invoice Number]=Forms![Add new Invoice]![Invoice Number]"))
Then MsgBox "This Invoice Number has already been entered. Please check the invoice.", vbInformation, "Duplicate Invoice Number"


Invoice_Number_BeforeUpdate_Exit:
Exit Sub

Invoice_Number_BeforeUpdate_Err:
MsgBox Error$
Resume Invoice_Number_BeforeUpdate_Exit

End Sub

Points to note. Invoice number is stored as a text field in the Table and it is also a Primary Key.

 

[Michael J Ross]

Adding a DoCmd open form command to your existing code like below should work.


If (DCount("[Invoice Number]", "Invoices", "[Invoice Number]=Forms![Add new Invoice]![Invoice Number]"))
Then MsgBox "This Invoice Number has already been entered. Please check the invoice.", vbInformation, "Duplicate Invoice Number"
DoCmd.OpenForm "nameofform",,,"[Invoice Number]=Forms![Add new Invoice]![Invoice Number]"

 

[Johnthelleader]

Thanks for that. That is certainly what I want. However, as the Add New Invoice form is a pop up form, it stays on top, whilst the form showing the duplicated invoice is behind it. Is there perhaps some code to make the Add New Invoice close after I have hit the OK button on the warning box that comes up?

 

[Michael J Ross]

Sure, use

DoCmd.Close acForm, "Add New Invoice"

after the open form command

It needs to be after the open form as the open form needs to pick up the value from the add new invoice form when it opens.

 

[Johnthelleader]

That's great that's done it. Thanks for you help.

 

[Michael J Ross]

Glad to help