Combo box dynamic RowSource based on Form Name and Control Name (1 Viewer)

BiigJiim

Registered User.
Local time
Today, 09:38
Joined
Jun 7, 2012
Messages
114
Hi,

I am not sure if this is possible (or even if it is a good idea!) but is it possible to use the form name and control name to dynamically build the RowSource for a combo box?

I have a client who wants to be able to copy a combo box from a default template form to new forms in design view without having to set the RowSource individually for each combo. The idea is to have a Lookup_Variables table, which has 3 fields; FormName, ControlName and Item. This would contain all the list items for every combo box in the application.

I know it would be possible to add a Form_Open event procedure to every form which looped through all the combo boxes on the form and set the RowSource property at that point, but the client wants to be able to create a new form, copy in the default combo box multiple times, name each one and then add the ListItems to the Lookup_Variables table for each one without adding any new code.

Possibly a strange request? Any pointers would be appreciated!

Jim
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,175
How many Columns does each combo has?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:38
Joined
May 7, 2009
Messages
19,175
you can Copy the Row Source of the combobox.
 

Attachments

  • ComboSource.accdb
    436 KB · Views: 311

BiigJiim

Registered User.
Local time
Today, 09:38
Joined
Jun 7, 2012
Messages
114
How many Columns does each combo has?
Each combo will have the same number of columns. The Item will always be the same data type, ie text.

So if the Lookup_Table looked like this:

FormNameControlNameItem
frm_Xctl_ABCItem A
frm_Xctl_ABCItem B
frm_Xctl_ABCItem C
frm_Yctl_DEFItem D
frm_Yctl_DEFItem_E
frm_Yctl_GHIItem_F

If the combo box was placed in form frm_Y and called ctl_DEF it would automatically have Item D and Item E in its dropdown.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:38
Joined
Sep 21, 2011
Messages
14,050
Surely the code would be in the form load?, So you would want to copy a template form?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:38
Joined
Oct 29, 2018
Messages
21,358
I know it would be possible to add a Form_Open event procedure to every form which looped through all the combo boxes on the form and set the RowSource property at that point, but the client wants to be able to create a new form, copy in the default combo box multiple times, name each one and then add the ListItems to the Lookup_Variables table for each one without adding any new code.
Unfortunately, Access (or specifically, your Form) can't do anything you haven't told it to do. So, if the user wants to create a new form and have it do something, it would have been setup to know what it was supposed to do. Besides, why is the user creating new forms? It should be the developer doing that.

One idea I could think of is like when you provide a "query builder" for the user to create Ad Hoc queries. So, rather than the user creating new forms using the regular UI in Access, you could maybe create a custom UI for them to do that. After they finished filling out all the requirements for the new form in your custom UI, your code would then build the form and attach all the appropriate row sources to all the comboboxes they added to the new form.

Just a thought...
 

BiigJiim

Registered User.
Local time
Today, 09:38
Joined
Jun 7, 2012
Messages
114
you can Copy the Row Source of the combobox.
Thanks arnelgp .

This is clever - I never realised you could use a function as the RowSourceType of a combo. However, it still doesn't achieve what the client wants, as if using the same function for every combo box, it would require a way of passing the control name into the function.

The client wants to be able to just name the control and the form and that is all. I thought it might be possible with some formula in the RowSource, but I wonder if its just not possible>
 

BiigJiim

Registered User.
Local time
Today, 09:38
Joined
Jun 7, 2012
Messages
114
Unfortunately, Access (or specifically, your Form) can't do anything you haven't told it to do. So, if the user wants to create a new form and have it do something, it would have been setup to know what it was supposed to do. Besides, why is the user creating new forms? It should be the developer doing that.

One idea I could think of is like when you provide a "query builder" for the user to create Ad Hoc queries. So, rather than the user creating new forms using the regular UI in Access, you could maybe create a custom UI for them to do that. After they finished filling out all the requirements for the new form in your custom UI, your code would then build the form and attach all the appropriate row sources to all the comboboxes they added to the new form.

Just a thought...
Agree it is a very odd request. And I think I am going to have to tell them its not possible. I just wondered if there was a way of referencing the control name (and form name) in a dynamic SQL string or formula in the control properties.

I think they will have to accept a standard SQL string in the RowSource which they will have to tweak for each combo box that they want to add, e.g. SELECT dbo_Lookup_Variables.Item FROM dbo_Lookup_Variables WHERE ListType="XXXX")) ORDER BY SortOrder;
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:38
Joined
Sep 21, 2011
Messages
14,050
If he was prepared to set the tag, you might be able to do it, regardless of how strange this request sounds.?
However, I would be wary of clients creating their own forms? What control are you hoing to have?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:38
Joined
Oct 29, 2018
Messages
21,358
Agree it is a very odd request. And I think I am going to have to tell them its not possible. I just wondered if there was a way of referencing the control name (and form name) in a dynamic SQL string or formula in the control properties.
Hi. I think there is a way to do that. However, as I was going through the scenario of how the user might use something like this, I ran into a sort of a chicken and egg situation.

1. If the user creates a new form and adds a combobox to it, then for that form to automatically assign a rowsource to it, that information must already be available in the lookup table. Which means, creating a new form and then updating the table to accommodate the new form and comboboxes might be too late. Unless, the form autoupdates itself when it opens.

2. If the user user updates the lookup table first, then it's possible to autoassign a row source to the combo, but the user "must" save the form first (because the name of a new form is not assigned until it is saved), which brings us back to point number 1 above of basically creating the form first.

In any case, I believe there is a way to provide this functionality, but the user must follow specific steps for using and executing it. This would mean you'll have to train them to do it correctly. Just a thought...
 

BiigJiim

Registered User.
Local time
Today, 09:38
Joined
Jun 7, 2012
Messages
114
If he was prepared to set the tag, you might be able to do it, regardless of how strange this request sounds.?
However, I would be wary of clients creating their own forms? What control are you hoing to have?
I did think about using the Tag property, with a template form that runs code when it opens to loop through all controls and set the Rowsource for the ones with the right Tag - that would be easy enough to do because the code would "know" the name of each control and of the form. But that relies on the client using the template form as a starting point each time (not guaranteed!)
 

BiigJiim

Registered User.
Local time
Today, 09:38
Joined
Jun 7, 2012
Messages
114
Hi. I think there is a way to do that. However, as I was going through the scenario of how the user might use something like this, I ran into a sort of a chicken and egg situation.

1. If the user creates a new form and adds a combobox to it, then for that form to automatically assign a rowsource to it, that information must already be available in the lookup table. Which means, creating a new form and then updating the table to accommodate the new form and comboboxes might be too late. Unless, the form autoupdates itself when it opens.

2. If the user user updates the lookup table first, then it's possible to autoassign a row source to the combo, but the user "must" save the form first (because the name of a new form is not assigned until it is saved), which brings us back to point number 1 above of basically creating the form first.

In any case, I believe there is a way to provide this functionality, but the user must follow specific steps for using and executing it. This would mean you'll have to train them to do it correctly. Just a thought...
The idea was that the form would autoupdate itself when it opened. If it was possible in the control properties window to set a property based dynamically on other properties of the same control, this would be easy, but sounds like it is not possible to do that.

I think I am just going to have to convince the client it is a bad idea (which it is!) and more trouble that it is worth!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:38
Joined
Oct 29, 2018
Messages
21,358
The idea was that the form would autoupdate itself when it opened. If it was possible in the control properties window to set a property based dynamically on other properties of the same control, this would be easy, but sounds like it is not possible to do that.

I think I am just going to have to convince the client it is a bad idea (which it is!) and more trouble that it is worth!
Most of the time, for something to perform a function automatically, we need to use some code. For a form to automatically adjust when it opens, we can use the Open event to execute that code.

So, if the user will be designing some new forms by themselves, they would, at the very least, be able and willing to add a function in the Open event of that new form. If your client is willing to do that, then what they are asking for is very possible.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:38
Joined
Feb 19, 2002
Messages
42,981
Here is a better option. I created this mini-app years ago and I use it in all my applications. It is an easy way to allow users to manage lookup tables rather than forcing the programmer to make all changes. If you use the suggested security or something similar, you can include all lookups in the management scheme, not just the ones you want users to manage.

I'm pretty sure I already posted this for you but do look at it.
 

Attachments

  • TableMaintExampleWithSecurity20210812.zip
    448.3 KB · Views: 313
  • Code Table Maintenance20210901PPT.zip
    182.8 KB · Views: 315

Users who are viewing this thread

Top Bottom