Recordset of Subform(Datasheet) inside another subform which is inside a mainform

[ramoj]

Hi All,

I am new to this Forum.

Good Day All,

I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.

Main Form - frm_1_0_LMS
Subform - frm_1_4_0_TeamApprovals
Subform(Datasheet) - frm_1_4_1_TeamApprovalsList

Here is my code:

Dim rs As DAO.Recordset
 
Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset
 
If Not (rs.EOF And rs.BOF) Then
 Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date
End If
 

I am getting this error:
Object doesn't support this property or method

Any replies are highly appreciated.

Thanks in advance.

[MikeTheBike]

Hi

First we need to establish that the name of the sub-form CONTROL(S) have the same name as the forms thay contain (this is the default name when using a wizard).

On the basis that this is the case then to expose the sub-form's objects, properies and methods you need to refer tyo to FORM Object contained in the sub-form control. Therefore your code should be modified to look something like this

Dim rs As DAO.Recordset 
 
Set rs = Forms!frm_1_0_LMS.Form.frm_1_4_0_TeamApprovals.Form.frm_1_4_1_TeamApprovalsList.Form.Recordset 
 
If Not (rs.EOF And rs.BOF) Then 
 Forms!frm_1_4_2_ApproveDeclineUserLeave.Form.Controls("lblFiledDateLeave").Caption = rs!Leave_Date 
End If

HTH

p.s. not sure where frm_1_4_2_ApproveDeclineUserLeave is located !?

MTB

[ramoj]

Thanks a lot MikeTheBike. Re: frm_1_4_2_ApproveDeclineUserLeave it is in a new opened form.

Really helps a lot.

[zmbd]

MikeTheBike
is on the correct track as usual; however, just to provide both context and tools for your future work:

This is the most simple and sweet: Allen Browne: Referring to Controls on a Subform

THis is the reference I will use when I get lost:
Forms: Refer to Form and Subform properties and controls
I'm worried enough that this site might ever go down that I took advantage of the download offer of a hardcopy of the table - upper right hand corner!

General Search return: Google Search for VBA Form References

IF you are using the navigation control form available in ACC2010 the give a buzz, it's somewhat in a world of its own as a hybrid.