Strange Compile error using Access & ODBC SQLServer

[DJJ]

I created the following procedure to selectively lock and unlock specific
records in a form. It works well using a combination of SQLServer Tables
(Using ODBC) and Access tables but when I converted to all SQLServer Tables
I got the following error. The only thing I can think of is that field that
is the control source for the check box that's locks the records is now
stored with -1 and 0 (SQLServer) but I don't know how to get around that.
Would greatly appreciate any input.

The Event (compile error - invalid use of property):

Private Sub Form_Current()
Call Locked(Me, Me.frmCollNameDataEntrySub, Me.chkLocked)
End Sub


The Standard Module Code:

Public Sub Locked(frm As Form, sfrm As SubForm, chk As CheckBox)
On Error Resume Next

Dim ctl As Control

If chk.Value = True Then

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox
.Locked = True

Case acComboBox
.Locked = True

Case acSubform
.Locked = True
End Select
End With
Next ctl

frm.AllowDeletions = False
sfrm.Form.AllowDeletions = False

ElseIf chk.Value = False Then

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox
.Locked = False

Case acComboBox
.Locked = False

Case acSubform
.Locked = False
End Select
End With
Next ctl

frm.AllowDeletions = True
sfrm.Form.AllowDeletions = True
End If

Set ctl = Nothing
Set frm = Nothing

End Sub

 

[Douglas J. Steele]

I can't think of why that should matter: Access actually treats any non-zero
number as True, so the fact that SQL Server stores the True as 1, while
Access stores it as -1 shouldn't really enter into the equation.

See whether changing the declaration from chk As CheckBox to chk As Control
makes any difference.

BTW, I don't see any reason the "ElseIf chk.Value = False Then". A simple
"Else" should be sufficient.

 

[DJJ]

Doug,

I changed chk As Control but I am still getting the same error. I am
wondering why the error says "invalid use of property" when calling a
procedure? This error only started when I converted the underlining table
from Access to SQLServer. I still have a copy of the Access version and it
runs fine.

DJJ

 

[mcnews]

trace your code with some break points to see where it's failing.

 

[DJJ]

It seems that Access was getting confused because the name of my sub also a
properity within Access itself "Locked". I changed the name of the sub and
now it works! Thanks anyway...

DJJ

 

[Douglas J. Steele]

You're probably right. You shoud never use reserved words for your own
purposes.

Sorry I mised that in the first place.