caling the recordset of a subform in VBA

[focosi]

Hi all,
I have frmX (bound to tblX, primary key [ID]) and sfmY (bound to tblY, primary key [IDbis]).
I would like to apply conditional visibility to some controls on sfmY according to recordset position (basically, no visibility on first record and visibility on all other records).


I'm using this code but this doesn't seem to work.

Dim rst As DAO.Recordset
Set rst = Me!sfmY!RecordsetClone
rst.MoveFirst
Select Case Me!sfmY![IDbis]
Case rst![IDbis]
        Me.[ctrl1].Visible = False
        Me.[ctrl2].Visible = False
Case Else
        Me.[ctrl1].Visible = True
        Me.[ctrl2].Visible = True
End Select
rst.Close
    Set rst = Nothing

What's wrong ???

[ssanfu]

The syntax for referring to the controls on the subform is wrong. It is tricky when referring to subform objects. Maybe this will help:

http://access.mvps.org/access/forms/frm0031.htm

[focosi]

thanks for the reference. I have modified my syntax accordingly but the debugger still highlights one line....

Dim rst As DAO.Recordset
Set rst = Me!sfmY.Form.RecordsetClone
rst.MoveFirst
Select Case Me!sfmY.Form![IDbis]
Case rst!Me!sfmY.Form![IDbis]             'highlighted by debugger
        Me!sfmY.Form.[ctrl1].Visible = False
        Me!sfmY.Form.[ctrl2].Visible = False
Case Else
        Me!sfmY.Form.[ctrl1].Visible = True
        Me!sfmY.Form.[ctrl2].Visible = True
End Select
rst.Close
    Set rst = Nothing

Any hint ?

[ssanfu]

"Me" provides a way to refer to the specific instance of the class where the code is executing. In this case, "ME" refers to "Forms!frmX" (per your exampel).
So you cannot have "rst!Me!sfmY.Form![IDbis]"

Dim rst As DAO.Recordset
Set rst = Me!sfmY.Form.RecordsetClone
rst.MoveFirst
Select  Case Me!sfmY.Form![IDbis]  'looking at the subform, not the recordset
Case rst!Me!sfmY.Form![IDbis] 'You cannot have "rst!" with "Me". this should be a value, ie when "Me!sfmY.Form![IDbis]" = 1, what should happen? when 2, whatshould happen?
        Me!sfmY.Form.[ctrl1].Visible = False
        Me!sfmY.Form.[ctrl2].Visible = False
Case Else
        Me!sfmY.Form.[ctrl1].Visible  = True
        Me!sfmY.Form.[ctrl2].Visible  = True
End Select
rst.Close
    Set rst = Nothing

example of case syntax

Select  Case Me!sfmY.Form![IDbis] 
Case 1 to 5
        Me!sfmY.Form.[ctrl1].Visible = False
        Me!sfmY.Form.[ctrl2].Visible = False
Case 10
        Me!sfmY.Form.[ctrl1].Visible  = False
        Me!sfmY.Form.[ctrl2].Visible  = True
 Case Else
        Me!sfmY.Form.[ctrl1].Visible  = True
        Me!sfmY.Form.[ctrl2].Visible  = True
End Select

I would like to apply conditional visibility to some controls on sfmY according to recordset position (basically, no visibility on first record and visibility on all other records).

I don't think you can do what you want like this. There is no "first" in a recordset, it depends on how the recordset is sorted.

You might investigate the "AbsolutePosition" property. There are a couple of examples under "See Also" ....