Problem trapping errors 1

[debbieg]

I have a main form with a subform. I have the following code on the main form:

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

    Select Case DataErr
        Case 2279   'InputMask violation
            InputMaskMsg
            Response = acDataErrContinue

        Case 3022   'duplicate primary key
            msgbox "dupe"
            Response = acDataErrContinue    

        Case 3058   'empty primary key
            msgbox "empty"
            Response = acDataErrContinue    

        Case 3314   'cannot contain null, required is true
            msgbox "null"
            Response = acDataErrContinue    

        Case Else
            Response = acDataErrDisplay
    End Select
End Sub

ADPFileNo has an InputMask of 000000;;_ and AutoTab is Yes.

The 2279 error is working fine.

If I try to add a record with no ADPFileNo, I get Access' 3058 error message, not mine.

If I try to enter a duplicate ADPFileNo, as soon as it goes to the next field some controls that I have set as Enabled=False become True AND when I click my Save button I get Access' 3022 error message, not mine.

I have searched for answers and tried everything I read and can't get Form_Error to work. I've done a Compact and Repair.

And the controls becoming enabled is really throwing for a loop. This only happens if it is a duplicate.

Can anyone help me? I've run out of ideas.
Debbie

 

[RoyVidar]

Are you by any chance doing update thru code, using queries/recordsets? The form error event, I think, will trap errors related to bound forms/controls in the GUI. When using code, I think there are run time errors occuring (same numbers, same messages). What is the code behind the save button? You may wan't to add code to the errorhandler there with:

[tt]select case err.number
case 3058
msgbox "...
case 3022
msgbox "..
case else
msgbox err.description
end select[/tt]

- to trap the runtime errors

Does the form error event fire at all when you get the default messages? (I see the commented msgbox, thingie)

oharab posted a nice routine yesterday, thread705-992193, perhaps have a look?

Roy-Vidar

 

[debbieg]

Roy,

Thanks for your response. I added my error trapping to my Save button and everything works well EXCEPT my controls are still enabling if the ADPFileNo is a duplicate. I've tried putting code EVERYWHERE to stop this but it still does it. I've tried stepping through the code to see where it happens and can't seem to catch it.

Any ideas?

Thanks for the help,
Debbie

 

[RoyVidar]

Oh - don't overpopulate the form with code - it just creates headaches - KISS is a good principle;-)

I'm then guessing the enabling/disabling is supposed to be dependent on correct (non dupe) PK?

Try testing in the before update event of the control bound to the PK. Use whatever test you find fitting, Domain Aggregate, recordset... Here's an ADO version

[tt]dim rs as adodb.recordset
dim strSql as string
strSql = "select PK from mytable where PK = " & me!txtPK
' using currentproject connection, replace with your connection
' if you're not using native/linked tables
set rs = currentproject.connection.execute(strSql,,adcmdtext)
cancel=((not rs.eof) and (not rs.bof))
rs.close
set rs = nothing[/tt]

- if the recordset contains records, cancel = true, cancelling the before update. You'll probably need to toggle some controls, use a msgbox, too based on the result here...

Roy-Vidar

 

[debbieg]

Roy-Vidar,

I have always found such good answers from you! I use DAO so here's what I did:

Private Sub ADPFileNo_BeforeUpdate(Cancel As Integer)
    On Error GoTo GotError
    'check for duplicate
    Dim rs As DAO.Recordset
    Dim ckDupe as string
    ckDupe = "SELECT DISTINCT ADPFileNo" & _
        " FROM tblEmployee" & _
        " WHERE ADPFileNo = '" & Me.ADPFileNo & "';"
    Set rs = CurrentDb.OpenRecordset(ckDupe)
    If Not rs.EOF And Not rs.BOF Then
        Cancel = True
        msg1 = "This ADP File No. has already been assigned."
        msg2 = "Enter a different ADP File No."
        answer = MsgBox(msg1 & vbCrLf & msg2, vbCritical, conTitle)
        Me.ADPFileNo.Undo
    End If
    rs.Close
    Set rs = Nothing
    
ExitSub:
    Exit Sub

GotError:
    MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & "frmEmployeeInfo ADPFileNo_BeforeUpdate"
    Resume ExitSub
End Sub

This works great on a new record. BUT, if I change an existing record to an existing ADPFileNo, I get the message and my controls are enabled when they shouldn't be (this is set in Form_Dirty. It's like it doesn't think it's dirty anymore but it is -- I've got the little pencil in the Record Selector. I tried putting me.dirty = true in my code above but that did nothing. As long as the ADPFileNo is blank it works fine -- that's apparently why it's working fine on a new record and if I delete the ADPFileNo on an existing record.

Do you have any more tricks up your sleeve?

Thanks,
Debbie

 

[RoyVidar]

Thanx for the kind words!

Well - there's no code here enabling/disabling controls. What's the .dirty event - you're just testing for contents in the control holding the PK? I think I'd try either placing the code in this event, or create a private sub yourtoggling;-) that you can call from either place (I think it should suffice with being called from here). Let the criterion for being enabled/disabled be eof/bof.

[tt]if ((not .eof) and (not .bof)) then
' enable the controls
' rest of code, cancel, msgbox, undo
else
' disable the controls
end if[/tt]

It seems you are using undeclared variables (answer, msg1, msg2) - you are using Option Explicit as the second line of every module (after Option Compare Databae)? It's strongly encouraged! In Tools | Options check "Required Variable Declaration" to enforce it in all new modules too.

Roy-Vidar

 

[debbieg]

Roy-Vidar,

I found my problem. I tried stepping through it one more time and noticed my subform was changing when I changed to an existing PK. It was checking the Form_Current of the subform and displaying that PK's information in the subform and for some reason I had my parent controls turned on in there. I don't even remember doing that!

This is the last of my testing and this is driving me up a wall. I even dreamed about it last night! I feel so stupid -- I've spent I don't know how many hours on this.

Yes, I use Option Explicit -- I couldn't survive without it. I set up global variables so I didn't have to keep declaring them and it keeps me consistent.

Thanks,
Debbie