handling key violations in VBA (2000 sr1a)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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).

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.

Reply | Quote