How to Use an Array Variant as a control name?

dpminusa

I have a Tab control with 15 tabs. I am using the TabCtl.Value in Select statements to control the ListBoxes, Reports, etc. for each tab. It occurred to me that I could simplify this greatly by defining a Variant Array of the ListBox names and report names. Then I could simply use array(TabCtl.Value) instead of the Select Statement details. The problem is that I cannot see how to make the array(TabCtl.Value) into a Control name that accepts property or method references. It seems like this should be simple, but I have tried a few things with no success. I am using Access 2007 on Vista Bus 32. Here is a sample of my code: (I have used the same names for the Queries, ListBoxes, Reports, etc. as a way of maximizing the usefulness of the one array and as an aid to reducing misspellings during coding. I find this less confusing than a lot of similar names.)

'An Array of Query Names to use as the RowSource for A Tab ListBox.
Dim lstFindRptNames As Variant

lstFindRptNames = Array("lstByCusName", \_
"lstByContact1", \_
"lstByContact2", \_
"lstByContact3", \_
    ...
"lstByProspect", \_
"lstByCustomer")

'Populate the ListBox by referencing a Query of the same name as the RowSource
lstFindRptNames(TabCtl0.Value).RowSource = lstFindRptNames(TabCtl0)

This fails because Access does not seem to substitute the String, in context, with the Control of the same name. So I tried to help it out with making a Control with a DIM first, I also tried a Generic Object. Neither of these lead to a solution. In several other languages, other than VBA, this is a non-brainer. What am I missing?

"Coding for fun and profit ... mostly fun"

Luc Pattyn

Assuming you want a solution for VB.NET, I would hold the references to the ListBoxes in a generic List (or even a Dictionary; not really an array), and most certainly not the names. You then can use indexing, and even for each In VBA, which I haven'tused for years, I would hope and use an array of ListBoxes. BTW: you're in the wrong forum, this isn't a database question. :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

dpminusa

Thanks for the hint. Not sure which Forum is correct. I tried to use the References Idea. It does not seem to apply or I am not clear on its use in this case. I see it is a way to get at Tools-> References. Tools-> References includes a reference to my open project. But I can get to these objects directly any way. Can you offer a snippet? I am also trying: TabCtl0.Pages(TabCtl0.Value).Controls but it only has count and item as properties and no methods. Hmmm...

"Coding for fun and profit ... mostly fun"

Luc Pattyn

Hi, I still don't know which language you want: VB, VB.NET, VBA. Anyway they all belong in the "Visual Basic"forum. I don't have any VBx snippets handy right now (and almost none most of the time, I'm more of a C# kind of guy). :)

Luc Pattyn [Forum Guidelines] [My Articles]

The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

dpminusa

VBA is what Access 2007 uses as default, as far as I know. I am forced to use a lot of different tools and languages in my projects. I am more comfortable with other languages as well. I will try to more it to the VBx forum. I guess that is a cut and paste unless you are a moderator. Thanks for the Hints. PS: If you need help with Perl, maybe I can reciprocate.

"Coding for fun and profit ... mostly fun"