Error Message

Question

Hi

 I have database with two tables, the first table called PatientTb (has the fields PatientName, PatientID as primary key and not allowed to be duplicate, PatientAge and PatientTel) and the second called TreatmentTb. I have created some forms for manipulate patient information. One of the forms is called NewFrm with fields PatientName, PatientID, PatientAge and PatientTel which I enter data for a new patient. I have wrote a code in field Event On Error to display my own error message. I do not want Access display the system error message. This is the code

               Private Sub Form_Error(DataErr As Integer, Response As Integer)

               Select Case DataErr

        Case 3022

            MsgBox _

                "The Patient ID you entered already exists. " & _

                    "Please enter a different value.", _

                vbExclamation, _

                "Already Exists"

            Me.PatientID.SetFocus

            Response = acDataErrContinue

        Case Else

            Response = acDataErrDisplay

    End Select

End Sub

When I enter by mistake an existing PatientID I get an access error message as showing in attached fig1. This message is not as the message which I coded in my form code. When I clear the field of PatientID and close this form I get another message as shown in attached fig2 and the form can not be closed.

I have another form for editing patient detail called EditFrm. If I try to change PatientID and by mistake I enter an existing I get another error message as showing in attached fig3 and ask for debug. When I click on Debug I get the error as showing in the attached fig4. At the end I get my written message as showing in attached fig5. I would like to disable access error message and shows only my own message which fig5 show it.

I will be grateful for your help.

Best Regards

Answer 1

Hello,

The code you shared works for me, it could successfully show custom message box when I open a form and enter a duplicated ID.

According to your description, the Form_Error event handler doesn't work for your NewFrm and would jump into debug mode for your EditFrm.

Since the Error event occurs when a run-time error is produced in Microsoft Access when a form has the focus.

How do you insert the new record for the two forms?

Is the issue reproducible if you create a new form?

Since we could not reproduce your issue, I suggest you share detail steps to reproduce the issue. 

Regards,

Celeste 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Thank you for your answer. I use the NewFrm for create a new patient, and I code the message error to be generated when I enter by mistake an existing number. I use the EditFrm to edit a patient detail, I use the same code in EditFrm to avoid changing PatientNumber to an existing number if I want to change PatientNumber.

Thank you for your help.

Best Regards

Answer 3

Hello,

For the information above, we still failed to reproduce your issue. Is the issue reproducible if you create a new form for a new table?

I test with the table form and code below.

If you follow my steps, would the code works for you?

If the code works for new created form, I suggest you check the differences between them.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
MsgBox _
"The Patient ID you entered already exists. " & _
"Please enter a different value.", _
vbExclamation, _
"Already Exists"
Me.PatientID.SetFocus
Response = acDataErrContinue
Case 3314
MsgBox _
"The Patient ID Could not empty " & _
"Please enter a value.", _
vbExclamation, _
"No Value"
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select
End Sub

Regards.

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Thank you for your help. I have tried but it does not work with my database. The system generate its own error message as show in attached figure, then it shows my written message. I will be grateful for any help.

Best Regards

Answer 5

Hi

Please try:

1 in Visual Basic - compile the code

2 "Compact and Repair" the database

else try

Change the line "Me.PatientID.SetFocus" to Forms![NameOfForm].PatientID.SetFocus

---

Best // Peter Forss Stockholm GMT +1.00

Answer 6

Thank you for your answer. I did the change but still have error. I use the compile and Compact and Repair but still error.

Best Regards

Answer 7

Hello,

According to your description, you follow the steps in my previous post, creating a form in your current database. Then you get two message boxes. So in conclusion, Form_Error event doesn't work for your current database.

If you test the database from https://1drv.ms/u/s!AkcxDWH1nFmJpS4azWxSji2OipCZ, would the message box show twice? Please disable all add-ins when testing.

If the issue exists in the database I shared, what is your detail version of Office? I suggest you repair your Office.

If the issue only exists in your current database, I think you need to re-create your database.

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

The reason you are getting the error message is because you have over-ridden the purpose of a Primary Key. So when you input a duplicate, of course you will get the message because ACCESS has detected that it is duplicate.

Do yourself a huge favor and go into the table where the Primary Key is located and change the data type to AutoNumber instead of Number. That way ACCESS wil automatically assign a Primary Key value for each new record that can be set to consecutive or random. No user should ever be allowed to input a Primary Key value. ACCESS should always do that using an AutoNumber. If you have a specific ID for patients (such as an account number) that needs to be used, then create a separate field for that and allow users to input that value, but never use a Primary Key for that. Primary Keys are used for identifying unique records within ACCESS only.