Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    handling key violations in VBA (2000 sr1a)

    I'm having some problems handling errors arising when moving back and forward from suforms used for data entry. The usual erro is 2110, "can't move focus to control ...", which seems to arise in 3 situations. The first is where the control to which the focus is being given has some code attached redirecting the focus, the second is odd random unreproduceable glitch, and the third is if there has been a key violation.

    My problem is that I can't find any way to distinguish between the 3 cases in VB. In cases 1 and 2 I'd just like to resume, in case 3 I clearly need to do something, or get the user to do something. Is there anyway to find out if there has been a key violation from VBA?

    thanks for your help

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

    Re: handling key violations in VBA (2000 sr1a)

    The key violation error (3022) won't be the same error as 2110, so just trap for the key violation error and do a Resume Next on the other two.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: handling key violations in VBA (2000 sr1a)

    The trouble is Charlotte, I never get error 3220, whatever the circumstances, only error 2110. If I then end the code, and try and save the form, then I am informed of the key violation in access. I have tried resuming if error is 2110, but then get error 2165 (as I hide the subform when the focus returns to the main form), and if I resume on errors 2110 and 2165, the focus stays with the subform, and nothing else happens. I never get error 3220.

    So, how can I tell that the reason I cannot move the focus away from the subform is because there's a key violation?

    Thanks again

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

    Re: handling key violations in VBA (2000 sr1a)

    Moving out of a subform forces a save, and that's where the 3220 error is coming in. You could use an If Me.Dirty Then ... to force a save before trying to move to another form or control and trap the 3220 error there before you ever get to the SetFocus. You'll need error handling to handle the 2110 error as well, but I would advise you to examine the causes of the 2110 error and think about what you're doing that causes it. You can't always avoid it but in some cases it will *always* occur and you should fix those--For example, setting the focus to a control that sets the focus elsewhere in its GotFocus event.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: handling key violations in VBA (2000 sr1a)

    Dear Charlotte,

    Forcing a save on dirty does cause a 3022 error. Not sure why moving the focus never did, but I have at least a workaround (though it will require some recoding).

    I have 2 supplementary questions:

    1) At present I have my error handling routines in separate procedures, as they are the same for many procedures. I cannot find anyway to resume the procedure that caused the error unless the error handler is in the same procedure. Am i being stupid?

    2) If I am, and there is a way to resume a named procedure, is there any specific way to pass or find out the name of the procedure that caused the error? I could put it in a global variable before I call the error handler, but global variables have a habit of being empty when the code breaks.

    Thanks

    PS have now ordered some books from overseas, so maybe won't have to bother you so often!

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

    Re: handling key violations in VBA (2000 sr1a)

    1) No, you're not being stupid. Access, and in fact, VB/VBA isn't really designed for centralized error handling routines. You can fake it somewhat, but it requires more effort than putting individual error handlers into the separate routines.

    2) You can use the err.Raise method to pass information about the error to the next level up, but that can cause other problems and it makes debugging on the fly much harder in my opinion.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: handling key violations in VBA (2000 sr1a)

    Just when I thought I had it.

    There are 2 tables involved that could suffer a key violation as the result of the data entered into the subform - the table underlying the parent form, and the table underlying the subform, or both. My plan was to test the parent form first, as if the individual had already been "used", the subform selection process would have to start afresh, then to test the subform table, in which case I would renumber the existing entry (with a number identifiable as having been changed in this fashion), and allow things to continue. This, then, was the code:

    NotInParentFlag = 2
    Parent.Refresh
    NotInParentFlag = 1
    If Me.Dirty = True Then Me.Refresh
    NotInParentFlag = 0

    and the error handling routine:

    Select Case Err.Number
    Case 3022
    If NotInParentFlag = 2 Then
    MsgBox "This individual is already in the database for this survey." & _
    "You must choose again"
    Parent.Undo
    Me.Undo
    Parent.Hamlet.SetFocus
    Me.Visible = False
    ElseIf NotInParentFlag = 1 Then
    DoCmd.RunSQL "UPDATE [Household Data Corrections] SET ID=500000+ID " & _
    "WHERE ID=" & Me.ID 'Run update query to change number of existing entry
    Resume
    Else
    MsgBox "ERROR, value of NotInParentFlag is " & NotInParentFlag
    End If
    End Select

    (Sorry, indentation structure doesn't seem to show in preview of message - not sure why)

    Only problem was, even when I chose an individual who already had a record in the table underlying the subform, but definitely not in the table underlying the main form, the error always happened at the Parent.Refresh statement, presumably because that also forced a refresh of the subform.

    Any suggestions as to how I can do this? It is probably as simple as checking in advance whether the individual exists in the table underlying the main form, but I can't work out how to use a SELECT statement to create a temporary recordset whose members I can count (and if the count equals 1 can know I'm in trouble).

    Thanks

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

    Re: handling key violations in VBA (2000 sr1a)

    Use the [ pre] tags from the tag panel to preserve the formatting of your code. Just be sure to break the lines properly so they don't create a huge horizontal scroll.

    I'm lost in your question. The main form should contain the records that hold the keys that are used to link the records in the subform to the parent form records. You wouldn't ordinarily allow records in the subform that did *not* contain an existing key from the parent form. Are you using an unbound subform control (i.e., no master and child links), and if so, why? I think you need to explain more about the tables and structures involved between the parent and child forms.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: handling key violations in VBA (2000 sr1a)-MULTI-R

    Parent and child forms based on separate tables. Parent table has primary key (study number), and second unique key (individual ID), child table has primary key (individual ID - same value as parent). Parent and child forms are linked on non key values (name or village/hamlet depending on the subform).

    For various reasons, the child table and parent table are separate, and the child form passes some information from it's record back to the parent on the press of the button, and it is on this event that key violations can occur if the individual entered in the subform has been entered previously in the subform. However, it is possible that that individual has been entered before through another subform, in which case they would have a record (as determined through individual ID) in the parent table but not the child table, or entered through this child form but subsequently deleted from the parent table, in which case they would have a record in the child table but not the parent table, or the individual is being reentered, in which case they will have records in both.

    If a record already exists in the parent form, then the user must start entering all data again, as something has gone dramatically wrong.

    If there is a record in the child form but not the parent, things might not have gone dreadfully wrong at this stage, so this is when I want to renumber the existing record in the child table and allow the passing of information back to the parent form as normal.

    For a more detailed description of the whole process, see my previous post:

    http://www.wopr.com/cgi-bin/w3t/showthread...&vc=1#Post14017

    Thanks

  10. #10
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Answer

    <center><img src=/w3timages/blackline.gif width=33% height=2> </center>
    It is probably as simple as checking in advance whether the individual exists in the table underlying the main form, but I can't work out how to use a SELECT statement to create a temporary recordset whose members I can count (and if the count equals 1 can know I'm in trouble).

    <center><img src=/w3timages/blackline.gif width=33% height=2> </center>

    Might as well answer my own question as I've found DCOUNT, which seems to work quite well, testing if DCOUNT("IDParent", "ParentFormTableName", "IDParent= " & Me.ID) is greater than 0.

Posting Permissions

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