Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Referential Integrity (2000)

    Back again,

    Is there any way to get past referential integrity error messages. I have tblTimesheet with links to tblClientDemographics and tblAccountDemographics with referential integrity set. My problem is that when the user opens a new record by accident or decides to end mid stream in entering data due to error they get a message that states a related record is required in either of the other two tables. This wouldn't be an issue except that once they hit OK they are unable to get out of the form or even the program till they finish entering data into the record. They end up hitting ctrl/alt/delete to shut down the entire program which is bothersome at best. Is there a way past this without shutting off the referential integrity?

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referential Integrity (2000)

    Fire the users? Educate them?

    As an interim measure, you can tell your users that pressing Esc (Escape) once or twice will almost always cancel the new record they started, and get them out of the gridlock.

    You can trap this kind of error at the form level using the On Error event of the form. The event procedure is of the form

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

    DataErr is the number of the error that has occurred, and you can get the corresponding error message by calling AccessError(DataErr). Response is an argument that you can change. Its default value is acDataErrDisplay, meaning that Access will display the standard error message belonging to the error. You can set it to acDataErrContinue to tell Access that it can ignore the error (because you have handled it yourself.)

    During development and testing, just report the error number and message. Write these down when an error occurs.

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    MsgBox "Error " & DataErr & ": " & AccessError(DataErr)
    Response = acDataErrContinue
    End Sub

    When you have an idea of the errors that can occur, you can start handling individual errors:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
    Case 3022 ' Duplicate key value
    MsgBox "You entered a duplicate key value.", vbExclamation
    Me.Undo
    Response = acDataErrContinue
    Case 3058 ' Null value in key
    MsgBox "You left a key value empty.", vbExclamation
    Me.Undo
    Response = acDataErrContinue
    Case Else
    Response = acDataErrDisplay
    End Select
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referential Integrity (2000)

    >>Fire the users? Educate them?

    OMG Han's, you're slaying me today with your humor. Unfortunately the user works for a friend so I can't fire them and as for educating them, I might kill them first. I'm not cut out for user support as I want to scream, where is your brain!! OK, enough venting. I have to remind myself how patient you've been with me!

    >>As an interim measure, you can tell your users that pressing Esc (Escape) once or twice will almost always cancel the new record they started, and get them out of the gridlock

    I'll try this as its gonna take me bit to understand and absorb the remainder of your post! But, I'm a stubborn cuss who doesn't accept defeat, so I will get it!

    Thanks,
    Alicia

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referential Integrity (2000)

    Hi, Hans.
    I have a form consisting of a combobox and two listboxes. The user makes a selection from the combobox and then double-clicks a name in the left listbox to move that name over to the right listbox. (Double-clicking the name in the right listbox will move it back over to the left box.) This scenario works just fine. Referential integrity is set to 1-1 so if the user tries to move two names from left to right the 3022 error message pops up. This is great except I want my own message with just an "OK" button. I've tried trapping the error as you said here but the 3022 message still occurs. I've tried trapping it in several different places on this form to no avail. It's as if I wasn't even trying to trap for this error.

    I appreciate any guidance you can offer me.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referential Integrity (2000)

    I'm sorry, but I don't understand where one-to-one referential integrity comes into play if you're moving a value from one table to another. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referential Integrity (2000)

    <P ID="edit" class=small>(Edited by WendellB on 21-Jan-04 22:42. Changed long search URL to post # to eliminate horizontal scrolling.)</P>Hi, Charlotte.
    I apologize for not being clear enough in what I'm doing. I should have said my list list boxes give the "appearance" of moving names back ond forth. I have attached a stripped model for you to see firsthand what's going on without any interference from me.

    As a matter of disclosure, Hans told me in <post#=317437>post 317437</post#> to eliminate my connecting table. I have been struggling since then by doing that but could not really get anything to work satisfactorily because people change racks all the time. What I have here works great if I can trap the 3022 message and replace it with my own.

    Thanks for your help.
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referential Integrity (2000)

    Eureka! I finally figured out what I needed to do. I added

    On Error GoTo ErrHandler
    ...

    ErrHandler:
    If Err.Number = 3022 Then
    MsgBox "Only ONE recruit can be assigned to a rack!!", vbExclamation, "Too Many Recruits"
    End If

    Don't know if it's right or not but it works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •