Me.AllowEdits with ComboBox

[Devon]

Hello

I found the posting:
http://www.microsoft.com/office/com...-4b11-4b29-9ad6-d4a6cff9b94a&cat=en-us-office
access〈=en&cr=US&sloc=en-us&m=1&p=1
which works great, but I am getting an error, and I am not sure how to
remedy the error. After copying the code from Dirk, I am getting the
following error:
Error 2455: You entered an expression that has an invalid reference to the
property ControlSource.

On my form, all of the fields have a ControlSource, except for the Combo
Box, which is unbound.

Any guessses?

Please let me know if you need any more information. Once I click OK on the
above warning, everything appears to be working great. I think that this
warning would be confusing to the user and would like to eliminate the
warning and fix the problem if possible.

Thanks in advance.

Devon

 

[Devon]

Linq, thanks for looking at my post.

I am attempting to do the following.

I have a Main Form and a Subform. I don't want the user to be able to
update the data on the main form, but instead simply enter data in the
Subform. I would use the Me.AllowEdits=False, but when I do so, it forces an
unbound ComboBox that searches for a couple of the fields to not work. I
think Dirk's suggestion in the post from the link above is in the right
direction, but it isn't quite working correctly.

I get Error 2455 when the Main Form first opens. Also, upon further
testing, after clicking OK on the error warning, I can't update the Main Form.

I copied and pasted the following code from Dirk into a Module:

Public Function fncLockUnlockControls(frm As Form, LockIt As Boolean)

' Lock or unlock all data-bound controls on form <frm>,
' depending on the value of <LockIt>: True = lock; False = unlock.

On Error GoTo Err_fncLockUnlockControls

Const conERR_NO_PROPERTY = 438

Dim ctl As Control

For Each ctl In frm.Controls

With ctl
If Left(.ControlSource & "=", 1) <> "=" Then
..Locked = LockIt
End If
End With
Skip_Control: ' come here from error if no .ControlSource property
Next ctl

Exit_fncLockUnlockControls:
Exit Function

Err_fncLockUnlockControls:
If Err.Number = conERR_NO_PROPERTY Then
Resume Skip_Control
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_fncLockUnlockControls
End If

End Function

I then copied and pasted the following into the Main forms On Current event:
Private Sub Form_Current()

fncLockUnlockControls Me, True

End Sub

Finally, I created an Add Record button on the Main form and included the
following On Click event:

Private Sub AddRecord_Click()

fncLockUnlockControls Me, False
End Sub

My theory was by putting the 'Unlock' coding in the Add Record button, it
will allow the user to Add New Records to the new form without knowing they
can delete or edit the other records.

Hope this makes sense.

Thanks

Devon

 

[Dirk Goldgar]

Hello

I found the posting:
http://www.microsoft.com/office/com...-4b11-4b29-9ad6-d4a6cff9b94a&cat=en-us-office
access〈=en&cr=US&sloc=en-us&m=1&p=1
which works great, but I am getting an error, and I am not sure how to
remedy the error. After copying the code from Dirk, I am getting the
following error:
Error 2455: You entered an expression that has an invalid reference to
the
property ControlSource.

On my form, all of the fields have a ControlSource, except for the Combo
Box, which is unbound.

Any guessses?

Please let me know if you need any more information. Once I click OK on
the
above warning, everything appears to be working great. I think that this
warning would be confusing to the user and would like to eliminate the
warning and fix the problem if possible.

What version of Access are you using? The code, which loops through all
controls on the form checking their ControlSource properties, expects to
raise error 438 ("Object doesn't support this property or method") for any
control that doesn't have that property -- a Label control, for example. So
the code traps that error and ignores it.

I can't recall if I've tested it with Access 2007, and whether that version
(maybe) raises error 2455 instead, when the ControlSource property doesn't
exist. If that's the case, you can just change the code to declare an
addition error constant at the top of the procedure:

Const conERR_NO_PROPERTY = 438
Const conERR_INVALID_PROPERTY_REFERENCE = 2455

And then, in the error-handling code, ignore that property, too:

'----- start of revised code snippet -----
Err_fncLockUnlockControls:
If Err.Number = conERR_NO_PROPERTY _
Or Err.Number = conERR_INVALID_PROPERTY_REFERENCE _
Then
Resume Skip_Control
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_fncLockUnlockControls
End If
'----- end of revised code snippet -----

Please understand, this revision is based on the assumption that the problem
is just that a different error code is being generated. Without more
in-depth debugging, I can't say for sure where the problem is coming from.
However, try this and see if it soves it for you.

 

[Devon]

Dirk

Thanks for the response. I am using Access 2003 for this dbase.

I have added the 'revised code snippet' into the module as follows
(unfortunately, I am still getting Error 2455).

Public Function fncLockUnlockControls(frm As Form, LockIt As Boolean)

' Lock or unlock all data-bound controls on form <frm>,
' depending on the value of <LockIt>: True = lock; False = unlock.

On Error GoTo Err_fncLockUnlockControls

Const conERR_NO_PROPERTY = 438

Dim ctl As Control

For Each ctl In frm.Controls

With ctl
If Left(.ControlSource & "=", 1) <> "=" Then
..Locked = LockIt
End If
End With
Skip_Control: ' come here from error if no .ControlSource property
Next ctl

Exit_fncLockUnlockControls:
Exit Function

Err_fncLockUnlockControls:
If Err.Number = conERR_NO_PROPERTY _
Or Err.Number = conERR_INVALID_PROPERTY_REFERENCE _
Then
Resume Skip_Control
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_fncLockUnlockControls
End If

End Function

Thanks

Devon

 

[Dirk Goldgar]

Dirk

Thanks for the response. I am using Access 2003 for this dbase.

I have added the 'revised code snippet' into the module as follows
(unfortunately, I am still getting Error 2455).

Public Function fncLockUnlockControls(frm As Form, LockIt As Boolean)

' Lock or unlock all data-bound controls on form <frm>,
' depending on the value of <LockIt>: True = lock; False = unlock.

On Error GoTo Err_fncLockUnlockControls

Const conERR_NO_PROPERTY = 438

Dim ctl As Control

For Each ctl In frm.Controls

With ctl
If Left(.ControlSource & "=", 1) <> "=" Then
.Locked = LockIt
End If
End With
Skip_Control: ' come here from error if no .ControlSource property
Next ctl

Exit_fncLockUnlockControls:
Exit Function

Err_fncLockUnlockControls:
If Err.Number = conERR_NO_PROPERTY _
Or Err.Number = conERR_INVALID_PROPERTY_REFERENCE _
Then
Resume Skip_Control
Else
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_fncLockUnlockControls
End If

End Function

If that's an accurate quote of your current code, you left out this line
that I said to put near the top of the procedure, next to the declaration of
conERR_NO_PROPERTY:

Const conERR_INVALID_PROPERTY_REFERENCE = 2455

Please put that line in and try it again.

The fact that your code didn't raise an error at compile time without that
declaration suggests that you don't have the VB option "Require Variable
Declaration" checked, and don't have an Option Explicit statement at the top
of the module. I strongly suggest that you set that option (on the Editor
tab of the Tools -> Options... dialog in the VB Editor environment), and
correct all existing modules to have the statement

Option Explicit

.... immediately under the Option Compare Database statement at the top of
the module (in the General / Declarations section).