By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 996 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

Duplicate Error Message

P: 20
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
Mar 16 '07 #1
Share this Question
Share on Google+
1 Reply


P: 9
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!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. txtFileNumber = Me!FileNumber
  3. End Sub
  4.  
  5. Private Sub txtFileNumber_AfterUpdate()
  6. Dim dbs As Database, rst As Recordset
  7. Set dbs = CurrentDb
  8.  
  9. If Nz(txtFileNumber, "") <> "" Then
  10.  Set rst = dbs.OpenRecordset("SELECT * FROM tblTable1 WHERE FileNumber = '" & txtFileNumber & "';")
  11.  If rst.RecordCount > 0 Then
  12.   MsgBox "This file number has already been assigned to someone. Please confirm information has been correctly entered."
  13.   txtFileNumber = ""
  14.  Else
  15.   Me!FileNumber = txtFileNumber
  16.  End If
  17. End If
  18. End Sub
  19.  
Mar 16 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.