Change subforms source object and goto record

I am trying to change the source object of a subform on a mainform but need to go to a specific record when the source object has changed. The event is fired from the subform when a user clicks on a field I would like the subform source object to change and the record associated with that field to be shown on the changed subform.

So far I have used the following and I have tried everything this evening to get it working with little success. At the moment it throws up a error message method or data not found on Me.Subform which is the name of the unbound subform.

Any help would be much appreciated

   Me.Subform.SourceObject = "subfrmGRMachineryDetails"
   Me.Subform.Filter = "UnitID=" & Me![UnitID]
   Me.Subform.FilterOn = True

Open in new window

Who is Participating?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Change the Name property of the actual subform Control on the Main form ... to something like

fsub01 ... then try this:

   Me.fsub01.SourceObject = "subfrmGRMachineryDetails"
   Me.fsub01.Filter = "UnitID=" & Me![UnitID]
   Me.fsub01.FilterOn = True

MGardnerAuthor Commented:
Thanks for that mx but it is still coming up with method or data member not found on Me.fsub01
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Can you Compact & Repair (to shrink the size), then zip up the MDB, then attach the file for upload here  ... removing any sensitive data of course? And please give a clear exlpanation of exactly how to reproduce the problem.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

MGardnerAuthor Commented:
Will do but might take a little time
MGardnerAuthor Commented:
Hi mx

sorry for the delay but unfortunately some presing personal matters to attend to this evening but here is the slimmed down db
Log in as user: Admin Password: Admin and hit the equipment tab.
Say yes to view the machine service dues
Click on the machine name/description to take you to the machine details and this is where it throws up the error message.

Thanks for your help

MGardnerAuthor Commented:
Any thoughts on this mx? or am I asking the impossible. I did wonder if the problem was with changing the source object before the code has time to fully work its way to the end.
MGardnerAuthor Commented:
I have managed to work around the problem with adding a second sub form where the objects source is called from the parent form and is then made visible. The second form I named fsub02. This was the only way that I could think of to get around the problem though not as elegant as I would have hoped but thanks for what help you gave in any case mx
Dim lngUnitID
Dim sFrm As Form
lngUnitID = Me.txtUnitID
Forms.swbEquipment.fsub02.SourceObject = "subfrmGRMachineryServicefrm"
Set sFrm = Forms.swbEquipment!fsub02.Form
sFrm.Visible = True
sFrm.RecordsetClone.FindFirst "[UnitID]= " & lngUnitID
If Not sFrm.RecordsetClone.NoMatch Then
      sFrm.Bookmark = sFrm.RecordsetClone.Bookmark
ElseIf sFrm.RecordsetClone.NoMatch Then
Forms.swbEquipment.fsub02.SourceObject = "subfrmGRMachineryServicefrmAddNew"
Forms.swbEquipment!fsub02.Visible = True
End If
Forms.swbEquipment!fsub01.Form.Visible = False

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.