FilterOn Causing a Problem

[croy]

When I open this form from a command button, I get an error:
"You cancelled the previous operation."

The code window indicates that the problem is with my
FilterOn statement in the form's OnOpen code.

Here's the code:

*****
Private Sub Form_Open(Cancel As Integer)

If DLookup("[DistrictId]", "qryDistrictStart") = "4" _
Then
Me.FilterOn = False
Me.AllowAdditions = False

If MsgBox("District is set to 'ALL'. You can _
review, but you won't be able to add or edit records.", _
vbOKCancel + vbDefaultButton2, "District Setting") = vbOK _
Then
Me.AllowAdditions = True
DoCmd.Restore
DoCmd.GoToRecord , , acLast
Else
If CurrentProject.AllForms!frmStart.IsLoaded
Then Forms!frmStart.Visible = True
Cancel = True
End If

Else
Me.AllowAdditions = True
Me.Filter = "FO = " & DLookup("District", _
"tblDistrictStart")
Me.FilterOn = True
DoCmd.Restore
DoCmd.GoToRecord , , acNewRec
End If

End Sub
*****

Any thoughts?

 

[croy]

I forgot to mention that tblDistrictStart contains only one
record (by intention).

 

[Dirk Goldgar]

When I open this form from a command button, I get an error:
"You cancelled the previous operation."

The code window indicates that the problem is with my
FilterOn statement in the form's OnOpen code.

Here's the code:

*****
Private Sub Form_Open(Cancel As Integer)

If DLookup("[DistrictId]", "qryDistrictStart") = "4" _
Then
Me.FilterOn = False
Me.AllowAdditions = False

If MsgBox("District is set to 'ALL'. You can _
review, but you won't be able to add or edit records.", _
vbOKCancel + vbDefaultButton2, "District Setting") = vbOK _
Then
Me.AllowAdditions = True
DoCmd.Restore
DoCmd.GoToRecord , , acLast
Else
If CurrentProject.AllForms!frmStart.IsLoaded
Then Forms!frmStart.Visible = True
Cancel = True
End If

Else
Me.AllowAdditions = True
Me.Filter = "FO = " & DLookup("District", _
"tblDistrictStart")
Me.FilterOn = True
DoCmd.Restore
DoCmd.GoToRecord , , acNewRec
End If

End Sub
*****

Any thoughts?

There are two statements manipulating the FilterOn property. I'm guessing
the one that's raising the error is the one near the end:

Me.Filter = "FO = " & DLookup("District", _
"tblDistrictStart")
Me.FilterOn = True

It seems to me that you would get an error on that statement if the .Filter
value is invalid. Can you set a breakpoint at the "Me.FilterON = True"
statement and check the value of Me.Filter? What type of field is
[District] in table [tblDistrictStart]? If that's a text field, you'll need
to build quotes around the value in Filter; for example:

Me.Filter = "FO = " & _
Chr(34) & DLookup("District", "tblDistrictStart") & Chr(34)

But if District is numeric, you wouldn't need that.

 

[croy]

There are two statements manipulating the FilterOn property. I'm guessing
the one that's raising the error is the one near the end:

Me.Filter = "FO = " & DLookup("District", _
"tblDistrictStart")
Me.FilterOn = True

It seems to me that you would get an error on that statement if the .Filter
value is invalid. Can you set a breakpoint at the "Me.FilterON = True"
statement and check the value of Me.Filter? What type of field is
[District] in table [tblDistrictStart]? If that's a text field, you'll need
to build quotes around the value in Filter; for example:

Me.Filter = "FO = " & _
Chr(34) & DLookup("District", "tblDistrictStart") & Chr(34)

But if District is numeric, you wouldn't need that.

Thanks Dirk. One of those "I could've sworn..." moments. I
have two somewhat similar tables, and my lookup field is the
same in both. They started out in life as text fields, and
then [I could've sworn] I changed them *both* to Long
Integer, but I only changed one, as it turns out.

Thanks for the help.