locked
Error Message RRS feed

  • 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

    Friday, April 21, 2017 5:35 AM

All replies

  • 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 MSDNFSF@microsoft.com.

    Monday, April 24, 2017 7:14 AM
  • 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

    Monday, April 24, 2017 9:36 PM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, April 25, 2017 7:17 AM
  • 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

    Tuesday, April 25, 2017 8:56 AM
  • 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


    Tuesday, April 25, 2017 7:06 PM
  • 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

    Tuesday, April 25, 2017 8:27 PM
  • 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 MSDNFSF@microsoft.com.

    Wednesday, April 26, 2017 6:38 AM
  • 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.

    Wednesday, April 26, 2017 9:47 PM