none
compile error: Expected identifier or bracketed expression

    Question

  • I have a main form containing a subform which then contains many subforms (staff roster). In each control field I have an on KeyDown event that passes 3 variables to a function that colours the active control.

    Private Sub rMon1L_KeyDown(KeyCode As Integer, Shift As Integer)
        Call ColourField1(Me.Parent.Name, Me.ActiveControl.Parent.Name, Me.ActiveControl.Name, KeyCode)
    End Sub

    pass values to

    Function ColourField1(Value1, Value2, Value3, KeyCode As Integer)

    MsgBox "myValue = Value1 & ", " & Value2 & ", " & Value3 & ", " & KeyCode"

        If KeyCode = 71 Then
            'MsgBox "G pressed"
            Forms!frmBaseRosterPeriods!(Value1)!(Value2)!(Value3).BackColor = 8454016
        Else
            Exit Function
        End If

    End Function

    I used the msgbox to check the values are passed correctly.

    If I try to reference the forms & control using Forms!frmBaseRosterPeriods!(Value1)!(Value2)!(Value3).BackColor = 8454016 then I get a compile error: expected identifier or bracketed expression at the !(Value2) position but if I reference using the actual form name in place of (Value2) e.g.  Forms!frmBaseRosterPeriods!(Value1)!subfrmChild1!(Value3).BackColor = 8454016 it compiles OK.

    I am obviously missing something in the syntax but cannot see it. As I will have up to 200 subform names for Value2 I would prefer to be able to reference these instead of having to hard code them.

    Any help would be appreciated

    Jeff

    Friday, February 17, 2012 12:52 PM

Answers

  • I'd change the event procedures to

    Private Sub rMon1L_KeyDown(KeyCode As Integer, Shift As Integer)
        Call ColourField1(Me.ActiveControl, KeyCode)
    End Sub

    and the function to

    Function ColourField1(ctl As Control, KeyCode As Integer)
        If KeyCode = 71 Then
            ctl.BackColor = 8454016
        End If
    End Function


    Regards, Hans Vogelaar

    Friday, February 17, 2012 1:36 PM
  • As Hans has pointed out, it would be more efficient just to have have the function operate on the active control or else pass a reference to the control to be manipulated.  That is certainly what I would do in this case.

    However, if you really want to pass the names and build a reference to the control by using string arguments containing the names of the forms and controls involved, the syntax would involve explicitly referring to a form object's Controls collection, similar to this:

        Forms!frmBaseRoster.Controls(Value1).Form.Controls(Value2).Form.Controls(Value3).BackColor = 8454016

    HOWEVER, you still have to face the additional difficulty of identifying the name of a subform control that is displaying a particular form.  That's because you need to use the name of the subform control in these references, which may or may not be the same as the form object being displayed.  But the .Parent property of a form (when displayed as a subform) returns the name of the *Form* that is displaying it, not the name of the subform *control* that is displaying it.

    I have a function that can be run on a subform to return a reference to the subform control that is displaying it.  However, I really don't think it's worth your while doing that, given the much simpler approach to your problem that Hans suggested.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Jeffrey Souter Saturday, February 18, 2012 12:37 PM
    Friday, February 17, 2012 4:14 PM

All replies

  • I'd change the event procedures to

    Private Sub rMon1L_KeyDown(KeyCode As Integer, Shift As Integer)
        Call ColourField1(Me.ActiveControl, KeyCode)
    End Sub

    and the function to

    Function ColourField1(ctl As Control, KeyCode As Integer)
        If KeyCode = 71 Then
            ctl.BackColor = 8454016
        End If
    End Function


    Regards, Hans Vogelaar

    Friday, February 17, 2012 1:36 PM
  • As Hans has pointed out, it would be more efficient just to have have the function operate on the active control or else pass a reference to the control to be manipulated.  That is certainly what I would do in this case.

    However, if you really want to pass the names and build a reference to the control by using string arguments containing the names of the forms and controls involved, the syntax would involve explicitly referring to a form object's Controls collection, similar to this:

        Forms!frmBaseRoster.Controls(Value1).Form.Controls(Value2).Form.Controls(Value3).BackColor = 8454016

    HOWEVER, you still have to face the additional difficulty of identifying the name of a subform control that is displaying a particular form.  That's because you need to use the name of the subform control in these references, which may or may not be the same as the form object being displayed.  But the .Parent property of a form (when displayed as a subform) returns the name of the *Form* that is displaying it, not the name of the subform *control* that is displaying it.

    I have a function that can be run on a subform to return a reference to the subform control that is displaying it.  However, I really don't think it's worth your while doing that, given the much simpler approach to your problem that Hans suggested.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Jeffrey Souter Saturday, February 18, 2012 12:37 PM
    Friday, February 17, 2012 4:14 PM
  • Thanks Hans.

    I had tried an iteration of this but I had

    Call ColourField1(me!FieldName, KeyCode) with the function ColourField1(ctl as Access.Control, KeyCode as Integer) but this didn't work which is why I thought that I would have to explicitly declare the string.

    Now I realise that I simply mucked up the syntax a bit.....

    Thanks again

    Jeff

    Saturday, February 18, 2012 12:31 PM
  • Thanks for you help (again) Dirk. It is always good to understand a couple of ways to solve a problem and this will help when/if I need to pass the name of a control that is not the current control.

    Jeff

    • Edited by Jeffrey Souter Saturday, February 18, 2012 12:50 PM incomplete post
    Saturday, February 18, 2012 12:35 PM