Unexpected Error 0 in VBA

[dip757]

hi All,

Can you please help me with this error msg. I get an "Unexpected Error" Message box with a Big Red X followed by the message "0:"


I have a form with a "FilterOn" based on a text box's after update event. This in turns calls a function that gives me the error. After that my Filter on "after Update" ceases to work.

Private Sub txtTime_AfterUpdate()
If IsNull(Me![txtTime]) Then
Me.FilterOn = False
Else
Me.Filter = "[PERIOD] = '" & Me![txtTime] & "'"
Me.FilterOn = True
Me.Requery

End If
LockPeriod
End Sub



Public Function LockPeriod()
On Error GoTo errHandler

Dim PlanLocked As Boolean

PlanLocked = DLookup("Locked", "tbl_CustomerRecord", "[Period] = '" & Me![txtTime] & "'")

If (PlanLocked = True) Then
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletions = False

Else
Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletions = True
End If

errHandler:
MsgBox Err.Number & " : " & Err.Description, vbCritical, "Unexpected Error"

End Function


-------------
thanks
D

 

[PHV]

...
End If
[!]Exit Sub[/!]
errHandler:
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Just wondering....

You have:

    [blue]If IsNull(Me![txtTime]) Then
        Me.FilterOn = False
    Else[/blue]
        Me.Filter = "[PERIOD] = '" & Me![txtTime] & "'"
        Me.FilterOn = True
        Me.Requery
    End If

Is textbox in VBA capable of holding NULL value?
Does your logic ever go to this part of your code?
I did try your code (in Excel) and even with empty text box I could not go to this part of the code.

I always do:

    If [blue]Len(Me![txtTime.Text]) = 0[/blue] Then
        Me.FilterOn = False
    Else
        Me.Filter = "[PERIOD] = '" & Me![txtTime] & "'"
        Me.FilterOn = True
        Me.Requery
    End If

Have fun.

---- Andy

 

[PHV]

Is textbox in VBA capable of holding NULL value?
Andy, I guess it's access VBA and thus the answer is: Yes.

 

[dip757]

PHV .... tried it put gave me a "Exit Sub not allowed in Function or Property" error now.

"LockPeriod" is a function in my case.

 

[PHV]

Sorry for the typo:
...
End If
Exit Function
errHandler:
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dip757]

that kinda worked.... only loops thru 2 times. e.g. I selected the first Value in txtTime drop down list. It worked. I selected the second one it worked.

but when I selected the 3rd. That value didn't even get assigned to the "txtTime". It looked like the form hung up on itself. But no error messages though.

 

[dip757]

.... By the way .... if I select the "second" or "third" value, then the filter works correctly. But it gets frozen then. Doesn't allow me to select any other values from the drop down. If I select the first value then it lets me selects 1 more. Then the forms stops to respond.

If it helps the value of tbl_CustomerRecord is as follows now:

Period Locked
Quarter 1 False
Quarter 2 True
Quarter 3 True

 

[Andrzejek]

Thanks PH, that makes sense.

dip757
Do you 'clear' your Filter before applying a new one?
Or do you stack Filter one, then add another filter to that one, then another one.....?

Do you get the correct results after Filter two?


Have fun.

---- Andy

 

[dip757]

How do you clear the filter? I think I am just stacking it. The code above is all I have in my DB too.

 

[dip757]

I added couple of Me.FilterOn = False ..... still no luck.

~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub cboQF_AfterUpdate()

Me.FilterOn = False


If IsNull(Me![cboQF]) Then
Me.FilterOn = False
Else
Me.Filter = "[PERIOD] = '" & Me![cboQF] & "'"
Me.FilterOn = False
Me.FilterOn = True
Me.Requery

End If

LockPeriod

End Sub
~~~~~~~~~~~~~~~~~~~~~~