Duplicate Error Message

[bruce24444]

I am designing a database to use a work to assign files to certain people. Form includes Date textbox, File Number textbox, File Type combobox and Assigned To combobox. The form is working fine and the File Number textbox is set “not to accept duplicates”.

What I'd like to do is change the error message that pops up if a duplicate file number is entered and attempted to be assigned.

Right now I get an error message which says "The changes you requested to the table were not successful because they would create duplicate values in the index......etc, etc"

When you click “OK” another message pops up saying “You can’t save this record at this time. If you close this object now, the data changes you made will be lost” and then all the other information entered is cleared.

What I’d like to do is change the message to say “This file number has already been assigned to someone. Please confirm information has been correctly entered”

Upon clicking “OK” return to the file number entry textbox to be able to re-enter the data without losing the rest of the information already entered into the form.

Designing for use with Access97

[RJMac]

The form you are using needs to be bound to the Table holding the fields you mentioned, and you need to have Microsoft DAO set in Tools/References on the tool bar. If both of the above are true, then the following should work:

You need to have 2 events set up - One for the Form - the on Current event, and one for the txtFileNumber text box - the AfterUpdate event. I'm assuming that your data table is called tblTable1, and your File Number field is FileNumber. Just insert the subroutines below:

If your File Number is a purely numeric field, just omit the ' either side of txtFileNumber after the SELECT statement!

Private Sub Form_Current()
txtFileNumber = Me!FileNumber
End Sub
 
Private Sub txtFileNumber_AfterUpdate()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
 
If Nz(txtFileNumber, "") <> "" Then
 Set rst = dbs.OpenRecordset("SELECT * FROM tblTable1 WHERE FileNumber = '" & txtFileNumber & "';")
 If rst.RecordCount > 0 Then
  MsgBox "This file number has already been assigned to someone. Please confirm information has been correctly entered."
  txtFileNumber = ""
 Else
  Me!FileNumber = txtFileNumber
 End If
End If
End Sub