Using a value from a list box in the Select statement

[DennisT]

I have a listbox called "List7" that contains the fields "tableid" and
"tablename". I am trying to use the listbox for all of my tables in the
database and then use a button to run code against each table entry as the
fields are the same for each table.

strsql2 = "select * from " & List7 returns the record number, but not the
tablename that could be used for the openrecordset statement. what do I need
to do to substitute the tablename value from the listbox for the select
statement? Thanks

 

[George Nicholson]

Me.List7.Column(0) references the first column with TableId.
Presumably this is set as the Bound column. That is why you get that value
as the default.

Me.List7.Column(1) references the second column, containing Tablename

The above assumes a single-select listbox. Referencing columns in a Multi
select listbox is a bit more involved. The following prints all columns of
SelectedItems to the Immediate window.

For Each varItm In List7.ItemsSelected
For iCol = 0 To List7.ColumnCount - 1
Debug.Print List7.Column(iCol, varItm)
Next iCol
Debug.Print
Next varItm

 

[Dale Fye]

If the "fields are the same for each table" as you state, then you have a
poorly designed database. My guess is that you are storing "data" in the
names of your tables (years, months, or productIDs, or something like that).
This type of database design is counter-productive, and requires a lot of
additional code to select the "correct" table. It also requires additional
queries, so that each query points to a different table. With a properly
normalized database (merge all of these tables), all you have to do is add a
field to your WHERE clause to extract info from the right set of data.

If the tables are truely the same, then I strongly recommend that you add a
field to one of them that will allow you to determine the difference between
the tables, then merge all of your tables into that one table, updating that
new field for each table that you have merged.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.