Combo Box which selects from one table out of many

[kspabo]

So instead of having all of my data on a single table which is filtered out as I make selections in my initial 3 combo boxes, I'd like for each set of data to be on it's own table. Instead of filtering out the irrelevant data in a single table, I'd like the initial 3 combo boxes to instead filter out the irrelevant tables. My main reasoning for doing this is that I figure it would first off save me much trouble in the future when editing data within the tables and also that one huge data table would slow down Access eventually. It sort of just dawned on me that a Query may be the easiest way to do this, but I've yet to figure it out.

[SMC]

It might be helpful if you uploaded the part of the database that you need help with. It will make it more clear as to what exactly you are trying to filter out between tables.

[kspabo]

I've uploaded a barebones/very early version of my file for simplicity sake. What I'm trying to achieve is to have the selection of the Series in my third combo box filter which table appears in the fourth combo box. So a selection of let's say Sliding Series (SeriesID8) in the third combo box will now make tblData8 be my selection choices for the fourth combo box. I hope this helps.

[June7]

Why do you have 8 identical tables? Why not 1 table with another field for an identifier? Actually, think you already have that with the SeriesID field.

Multiple similar name fields suggests a non-normalized data structure.

[kspabo]

The tables are identical for the sake of simplicity, I could upload my actual file which has 17 data tables each with it's own unique set of data if it would help.

Are you suggesting that I create another table with an universal identifier from all 17 (2-8 in the case of the file I uploaded) data tables I need? That way when I make the selection in my 4th combo box it just references the actual table with the bits of information I need? My intentions here are simply to keep all the sets of data separated for ease of access/alteration and also to minimize the search load.

[June7]

Unique in what way? If the tables have the same fields, as shown in the example, then you have actually complicated the db with separate tables, not simplified.

However, the code you seek could be like:

Me.listboxLineSheet.RowSource = "SELECT * FROM [tblData" & Me.cboSeries & "]"

Of course, could be more complicated if the actual table names are much different.

[kspabo]

Here is an example of the data I'm actually trying to use. Each data table does have it's own unique name.

EDIT: I apologize for the images poor formatting, I expected it would resize to a more manageable size.

[June7]

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Those are longest table names I've ever seen!

Will need conditional structure in code.

Dim strTable As String
Select Case Me.cboSeries
Case "this value"
strTable = "this table name"
Case "another value"
strTable = "another table name"
...
End Select
Me.listboxLineSheet.RowSource = "SELECT * FROM [" & strTable & "];"

Previous comments still apply. If fields are identical, separate tables unnecessarily complicates design.

[kspabo]

Excuse the ridiculous table names it's a result of importing straight from excel without any name change. So just to clarify a question, since you're saying that tables with identical fields is essentially complicating the process, does this mean that if I were to consolidate all 17 data tables to a single table (since they have the same fields), would this reduce design complications? I just figured that separating sets of data to their own tables would simplify the process.

Would you recommend that I consolidate all data tables to one large table, or should i try to figure out the code with individual tables?

[June7]

One table. It is easier to apply filter to isolate records than it is to combine tables with UNION query. And at some point you will find a need to combine them, guarantee. In fact, a UNION query could serve as the RowSource for listbox and then simpler code to apply combobox value as filter parameter to the listbox.

Also, if you have to add a new series, separate tables means design edits to create a new table then modify queries, forms, reports, code - real headache. With 1 table a new series just means new records.

If you change the table names to something more along the lines of your original example or if the series name and table name are identical, the code could be simpler and more dynamic, as per my first example. However, a new series would still involve database design edits.