On 10 Dec 2003 15:44:39 -0800 in comp.databases. ms-access,
we****@hotmail. com (Michael) wrote:
Danny,
Thanks for your message. The problem is finding out where the error
is occuring because the error doesn't seem to be occuring on any code
that I can detect.
I have wrapped all of my code with a error tracking script that writes
all the errors to a table, but this hasn't turned anything up.
One suggestion is to export all the database objects to another blank
database which might clear any niggly errors like this. I might try
this if I don't get any joy.
That won't help, it's a PITA I have to live with, maybe some
triggering code that updates another bound control on the form and yes
it happens randomly (not in any recordset based operations so the help
and KB on it are useless).
Below is some trapping code I came up for it, works most of the time,
basically it runs around the form and collects all the values so far,
undoes the current record then goes around and pokes everything back
in.
in the form use:
Private Sub Form_Error(Data Err As Integer, Response As Integer)
Dim lngError As Long
lngError = DataErr
Select Case lngError
Case 7787, 7786, 0 ' can't remember what these were :-\
Response = acDataErrContin ue
Case 3020
Error3020 Me, Response
Case Else
MsgBox "Error #" & lngError & " - " &
AccessError(lng Error), vbCritical, Me.Name & " Form Error Event"
Response = acDataErrDispla y
End Select
End Sub
An in the timer event:
Private Sub Form_Timer()
Select Case Me.TimerInterva l
Case 302
Me.TimerInterva l = 301
Error3020Timer
Case Else
End Select
End Sub
Now the module that does the bees knees:
(basSillyErrors )
Option Compare Database
Option Explicit
Dim mstrParentFormN ame3020 As String
Dim mstrFormName302 0 As String
Dim mstrCtlName3020 As String
Dim mvarCtlValue302 0 As Variant
Function Error3020(pfrm As Form, Response As Integer)
' Error Trapped: 06/05/2000 10:00:36 sa
On Error GoTo Error3020_Err
' called from Form Error 3020 that hapens when Access gets
' it's knickers in a twist.
Dim varCtl() As Variant
Dim ctl As Control, i As Integer
' reset these
mstrParentFormN ame3020 = Screen.ActiveFo rm.Name
mstrFormName302 0 = pfrm.Name
mstrCtlName3020 = ""
mvarCtlValue302 0 = Null
ReDim varCtl(0 To pfrm.Controls.C ount - 1)
For Each ctl In pfrm.Controls
varCtl(i) = Null
' cannie do anything with the current
' control
If ctl.Name <> pfrm.ActiveCont rol.Name Then
varCtl(i) = ControlValue(ct l)
Else
' plug these in in the timer event as it won't
' accept them until after this error event
' has completed.
mstrCtlName3020 = pfrm.ActiveCont rol.Name
mvarCtlValue302 0 = ctl.Text
End If
Debug.Print varCtl(i)
i = i + 1
Next
pfrm.Undo
pfrm.Undo
i = 0
For Each ctl In pfrm.Controls
Debug.Print ctl.Name
If Not ctl.ControlSour ce Like "=*" Then
If Left(varCtl(i), 7) <> "#Error " Then
If "" & ctl.Value <> "" & varCtl(i) Then
If ctl.Name <> pfrm.ActiveCont rol.Name Then
ctl.Value = varCtl(i)
End If
End If
End If
End If
i = i + 1
Next
Response = acDataErrContin ue
' set up the timer to plug the values into
' the current control but you must make sure that
' the form's timer event calls Error3020Timer if the
' TimerInterval property is set to 302 and also
' set it to a different value so it don't keep
' on calling the code every 302 milliseconds.
pfrm.TimerInter val = 302 ' (3020/10)
Error3020_Exit:
On Error Resume Next
Exit Function
Error3020_Err:
Select Case Err
Case 438 ' object doesn't support this property or method
' who gives a rat's arse?
Resume Next
Case Else
LogMsgBox Err, Err.Description , 16, "Error #" & Err & " In
Error3020()"
End Select
Resume Error3020_Exit
Resume
End Function
Public Function Error3020Timer( )
' Error Trapped: 07/11/2001 16:58:35 sa
On Error GoTo Error3020Timer_ Err
Dim frm As Form, ctl As Control
If mstrFormName302 0 = mstrParentFormN ame3020 Then
Set frm = Forms(mstrFormN ame3020)
Else
Set frm =
Forms(mstrParen tFormName3020). Controls(mstrFo rmName3020).For m
End If
Set ctl = frm(mstrCtlName 3020)
ctl.Value = mvarCtlValue302 0
Set ctl = Nothing
Set frm = Nothing
Error3020Timer_ Exit:
On Error Resume Next
Exit Function
Error3020Timer_ Err:
Select Case Err
Case 3162 ' IOW Invalid use of Null
' happens if the mvarCtlValue302 0 got null from the
handler and the ctl in
' question was a checkbox, as it's the current control it
had no text
' property in the handler so a null was returned. Not sure
what happens at
' this stage as it's difficult to see being as the error
occurs randomly
' and only occasionally. Hopefully the value of the
checkbox will be changed
' to what the user wants but if not then it should look
like the user is
' cack handed and didn't click on it properly... hehe.
Resume Next
Case Else
LogMsgBox Err, Err.Description , 16, "Error #" & Err & " In
Error3020Timer( ) while trying to recover from Error 3020"
End Select
Resume Error3020Timer_ Exit
Resume
End Function
Function ControlValue(pc tl As Control) As Variant
' TB 30/10/2001
' Returns the value of a control, if that control
' is something that doesn't have one then returns:
' #Error <num> - <errortext>
'
On Error Resume Next
Dim varvalue As Variant
varvalue = Null
varvalue = pctl.Value
If Err.Number Then
varvalue = "#Error " & Err.Number & " - " & Err.Description
End If
ControlValue = varvalue
End Function
--
A)bort, R)etry, I)nfluence with large hammer.