05-03-2001, 07:31 AM
I am using VBA in Access.The Primary key in my Table is PO. I managed to put code in the AfterUpdate to show a duplicate instead of finding it on attempting to leave the form or go to another record. But what I would like to do is have the form go to the previous record when a duplicate PO is entered. Then I would be able to enter the other fields such as Ship date, Invoice Number, etc. At present my message box tells the user that there is a duplicate PO, but the only thing to do is to close the form, which take about three attempts
<P ID="edit"><FONT class="small"><EM>Edited by dnala on 05/03/01 08:47 AM.</EM></FONT></P>
05-03-2001, 04:38 PM
You need to implement your own error handling instead of letting access do it. ie. On Error Goto ErrMsg
at the end of the procedure (just before end sub) insert
Dim strMsg as string
strMsg=MsgBox("Tell the user what you want")
--insert the code you want to happen here--
05-08-2001, 11:51 AM
1- Write a procedure in the Validation Event of the Text Box
2- In the procedure, create an instance of Connection and Recordset object
3- Open the Connection object and Recordset with the select statement that:
"SELECT PO FROM YourTable;"
4- Check the recordset to see whether it is empty or not:
If NOT (Recordset.BOF and Recordset.EOF) Then
Cancel = True
TextBox.Text = ""
MsgBox "The data entry is duplicate and is erased", vbInformation, "AnhMy Tran"
I hope the idea will help.