2

My goal is to change the SourceObject of a subform. I can do this manually by going into design view selecting the subform object and changing SourceObject and removing the master and child links. I save changes and reload into form view and all works well.

When I try doing this via VBA it does not seem to work and gives me a blank subform.

Here is the code that I am currently running:

Private Sub ondacapBtn_Click()
Me!DACAPRosterQ_subform.SourceObject = "ondacap_subform"
Me!DACAPRosterQ_subform.LinkMasterFields = ""
Me!DACAPRosterQ_subform.LinkChildFields = ""
Me!DACAPRosterQ_subform.Requery

I find this even crazier because this is code I've used before except the Me!subform is a different name, and it works perfectly elsewhere! I am complete baffled at how this works in the other subform but not this one.

I've tried changing the name, the tab order, made sure the subform I am changing to works, made sure the code matches the other subform shown below, I am out of ideas and I made this account just so I can ask this question. It's really upsetting me.

Private Sub FilterBtn_Click()
Me!Retrain_subform.SourceObject = "UpcomingRetrain_subform"
Me!Retrain_subform.LinkMasterFields = ""
Me!Retrain_subform.LinkChildFields = ""
Me!Retrain_subform.Requery

EDIT - 26SEP22

I found out that one of the fields in my RecordSource is causing this issue.

My subform has a RecordSource called DACAPRosterQ and in that query I've input a field that is called "SearchTerms:" [with] & [other fields] & [listed] & [to use as search bar criteria]. The table PersonnelT that the query DACAPRosterQ pulls from does not have the "SearchTerms:" field naturally. Only the query.

When I remove this field from the query the subform can be switched with no issue via VBA.

Something I noticed when trying to open Ondacap_subform individually without the parent form is it asks for input "Forms!DACAPRosterF!DummySearchTextbox" which is a control on the parent form DACAPRosterF alongside the subform.

I have a feeling I must input some code to reinitialize that field as it is not done so via VBA. I'm just not sure what it is.

I think this would also explain why I am able to switch SourceObject manually via design view because it it already being reinitialized when reopening the form in form view.

Anyone have any ideas?

Gilford423
  • 21
  • 2
  • 1
    Might want to post in a forum that allows attaching files and provide your db for analysis. – June7 Sep 24 '22 at 08:41
  • `ondacap_subform` may contain some offending code. – Gustav Sep 24 '22 at 08:44
  • I found out that the one of the fields in my RecordSource is causing this issue. My subform has a RecordSource called DACAPRosterQ and in that query I've input a field that is called "SearchTerms:" [with] & [other fields] & [listed] & [to use as search bar criteria]. When I remove this field from the query the subform can be switched with no issue. I have a feeling it has something to do with... when that subform is opened w/out parent form it asks for input Forms!DACAPRosterF!DummySearchTextbox which is why I can manually change from design view no problem... Any thoughts? – Gilford423 Sep 25 '22 at 03:16
  • Should edit question with that critical information. So that field is built with dynamic input popup prompts? I never use those. If you want dynamic parameters, they should reference controls on form for user input. So exactly how do SearchTerms and DummySearchTextbox figure into this arrangement? Again, suggest using a forum where you can provide your file. – June7 Sep 25 '22 at 17:31
  • The parent form "DACAPRosterF" has 2 search boxes. Dummy textbox that is not visible and gets refreshed by the visible user's "SearchTextbox". This dummy is used to update the search criteria in the query and the criteria is ```Like "*" & Forms!DACAPRosterF!DummySearchTextbox & "*"```. ```SearchTerms:``` basically pools together all the other fields within the query into a single field so that multiple fields can be searched via the search bar. Sadly I can't upload that database for analysis as it is locked away on secured net. – Gilford423 Sep 25 '22 at 22:44

1 Answers1

0

I figured it out... I couldn't tell you why it works or why it didn't work without this solution.

All I had to do was enter into the subforms OrderBy property, I ordered it by [Field1], [Field2]. Works like a charm now. Search bar still works and everything functions as normal.

If anyone could provide insight as to why this occurred, that would be amazing. Otherwise, problem is fixed.

Gilford423
  • 21
  • 2