Problem with datasheet subforms using recursion to set control properties in form

Question

I am using a piece of code that successfully sets the background colour of controls on a form. And i use similar bits of code to set the background colour of required fields etc..

Public Sub colCtrlNorm(frm As Form)

Dim setColour As String
setColour = RGB(252, 252, 252)
Dim ctl As Control
For Each ctl In frm.Controls
    With ctl
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or         ctl.ControlType = acListBox Then
    ctl.BackColor = setColour
    ElseIf ctl.ControlType = acSubform Then
        colCtrlNorm frm(ctl.Name).Form
    End If
End With
Next ctl
Set ctl = Nothing

End Sub

Everything works fine and dandy except on a form that contains a subform which has nested datasheet.

When trapping the error I get the following error message:

Error 2455: You entered an expression that has an invalid reference to the property Form/Report

I appreciate i cant change the datasheet controls background colour. This problem only arises with nested datasheets. Anyone any ideas as to how I can avoid this?

Thanks in advance for any assistance with this issue.

Cheers Noel

I'm guessing here (I'd post an answer if I knew it was correct), but I think you should check to see if the parent of the subform control is in datasheet view, and skip it if so. A datasheet is a form, so a subdatasheet is a subform, but one that is the child of a form in datasheet view. — David-W-Fenton
@David Yep, got it working by checking to see if the default view is as datasheet — noelmcg

David-W-Fenton David-W-Fenton · Accepted Answer · 2010-11-28T00:41:09

Check to see if the parent of the subform control is in datasheet view, and skip it if so. A datasheet is a form, so a subdatasheet is a subform, but one that is the child of a form in datasheet view. Use the subform's .CurrentView property.

You can use the named constants that are part of the acCurrentView Enum:

  acCurViewDatasheet = 2
  acCurViewDesign = 0
  acCurViewFormBrowse = 1
  acCurViewPivotChart = 4
  acCurViewPivotTable = 3
  acCurViewPreview = 5

Obviously, some of those don't apply to forms but to reports, but they will still work.

Problem with datasheet subforms using recursion to set control properties in form

2 Answers