Solved Dynamically Change Textbox Control Source (1 Viewer)

pooldead

Registered User.
Local time
Today, 03:33
Joined
Sep 4, 2019
Messages
136
I’m posting from my phone so I don’t have code on me at the moment, but here’s the situation. I have a form that contains two sub forms, two combo boxes, and a text box. Combo box A selects an application, which after update will populate sub form A with data (displayed in database view). Whatever value is in column A in sub form A will be sent to the text box as the control source. The text box is then used as the Master field for sub form B. (I hope this made sense, and if not I can get an image).

All of that works fine. I’m trying to introduce Combo Box B into the workflow, so that it acts as the text box control source when required. I’d like to utilize the same text box, as I need the data displayed in sub form B still.

How can I dynamically change the text box control source between SubFormA.ColumnA and ComboBoxB.Value?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:33
Joined
Feb 28, 2001
Messages
26,996
Here is the problem - you need to use some VBA to do this. TECHNICALLY you can't change the source of the text box source to anything other than a computation or a field in the .RecordSource for the form.

One solution is the third possibility for the control, which is to make the textbox unbound and load that box when you make the change from A to B or B to A. The catch is that if you then do something to change the content of A (without switching ownership) you will need to make A do the update of something that isn't local to A (because A is a sub-form and the box is in the parent). When you change B (by making a selection), the text box cannot see the selection (because it didn't have an event) - but the event related to making a selection in B - the click event - can see the box.

In summary, you can't (mechanically speaking) tie the box to A or B, but you can tie both A and B to the box through VBA code and burden them with the update. you want

To see the box (let's call it C just to minimize typing), from sub-form A, that box is Me.Parent.C and you can load it at will. So if there is a relevant event in A, that event can, as a side-effect, take care of C. To see the box from the combo box B, that box is Me.C, and you can load it at will while still in the B_Click event that is associated with a selection.

Then the only trick will be to assure that A and B have a way to "know" which of them "owns" the box. If you prevent A and B from being both visible/active at the same time, this might be workable.

NOTE that if C has to be stored or user-editable, it SHOULD be possible to bind it "backwards" as I described. But be aware that making C editable and bound will have implications for "dirtying" the form, which will cause it to be stored when you change focus from the parent to the child form or vice-versa.
 

pooldead

Registered User.
Local time
Today, 03:33
Joined
Sep 4, 2019
Messages
136
Considering I’m self taught, I’m excited (and terrified) to say that actually made sense! I’ll give it a shot when I get back to my PC tonight and provide an update. Thanks Doc_Man!
 

Users who are viewing this thread

Top Bottom