How to reference a field in a subform within a tab control using a loop.

Question

Hey guys, I had a form with 75 subforms and a loop to fill the fields for viewing purposes. I have recently wanted to add multiple pages to the same form, so I added a tab control in order to not get a memory exceed error. My issue is that while looping, I get a run-time error 2455 "You entered an expression that has an invalid reference to the property Form/Report." Any thoughts?

Here is my mod code:

Sub Fill_subform(cur_sub As SubForm, rs As Recordset)

   With cur_sub
If (Not IsNull(rs!Top)) Then .Top = rs!Top
If (Not IsNull(rs!Left)) Then .Left = rs!Left

      !txtTitle = rs![Job Title]
      !txtContactName = rs![Contact Name]
      !txtMPhone = rs![M Phone]
      !txtOPhone = rs![O Phone]
      !txtBlackBerry = rs![BB Phone]
      !txtHPhone1 = rs![H Phone]
      !txtHPhone2 = rs![H Phone]
      !txtHPhone3 = rs![H Phone]
      !txtBranch = rs!Branch
      !txtID = rs!ID
      !txtAutoNUM = rs!AutoNUM
      !txtService = rs!Service
      !txtVisible = rs!Visibility
   End With

End Sub

Here's my loop:

Private Sub Form_Load()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryContacts WHERE Service ='" & Me.OpenArgs & "'")

  Do While (Not rs.EOF)
If rs![ID] > 75 Then Exit Do
 Fill_subform Me("Sub" & rs!ID), rs
    rs.MoveNext
  Loop
  
End Sub

Answer 1

" I have recently wanted to add multiple pages to the same form, so I added a tab control in order to not get a memory exceed error."

This won't help with the error unless you are implementing dynamic/lazy form loading.

 

Which line is highlighted when you debug the error?

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

All of the !txt in the mod. I was hoping to make the loop changes on tab change with an rs.close, recordset = nothing at the start of the code then the loop. And close each recordset and loop a new one based on different tabs. Don't know if this will work, but I need to add more subforms to my form. I tried doing this other ways without a tab control and I still got the same error. My only real hope is to try it this way and hope for the best, if not I'm quite screwed.

Answer 3

 Fill_subform Me("Sub" & rs!ID), rs

Hi Jamie,

In the Load event Me refers to the main form.

If you want to access the controls in the subform, you can use:

    Me(<subformcontrol>)("Sub" & rs!ID)

Substitute <subformcontrol> with the real name of the control.

Imb.

Answer 4

Don't know why, but I just remade the entire form and it worked without having to alter any of the code. Strange.