Question Combo Box to Return Table Name

[rgreene]

In my database I'm comparing data in 2 tables and returning the difference. For example Table1 will have Joe Blow $75 Table2 will have Joe Blow $50 then my query will return Joe Blow $75 $50 ($25). So in year 1 Joe spent $75 with us. In year 2 he spent $50 with us. so the comparison shows he spent $25 less with in year 2.

What I want to do is on a form have a Combo Box that lists the table names so the user doesn't have to try to remember the names scheme of the tables. So I want the combo box to display "Table1, Table2, Table3..." not the contents inside the tables..

Thanks in advance

Rick

 

[ghudson]

Odd request since programmers do not give the users access to the tables. If you are using a query to populate the combo box then you can define one of the fields to display what ever you want and set the combo box to display that value in one of the fields. Remember field 1 is really 0 when working with combo boxes.

 

[rgreene]

I'm not really giving the users access to the table I just want the user to be able to choose 2 tables to compare. The number of tables will continually increase (probably monthly) and in order to cut down on possible user input errors if they can see May10, June09, July09.... that is a lot easier for the user then trying to remember what each table is named.
Maybe when they import the data they goof on the name and call it April 10 and everything else is May10 (no space between month and year) If they have to type in the table name then April10 will never work because the table name has a space between the month and year...

 

[ghudson]

You have a design flaw if you are allowing the user to manually name a table from a file import. Using a standard naming convention is a must for any programmer.

 

[dkinley]

You can use something like this in the Row Source of your combo box ....

SELECT MsysObjects.[Name] 
FROM MsysObjects 
WHERE ((MsysObjects.[Type] IN (1, 4, 6)) AND ([Name] Like "t*")) 
ORDER BY MsysObjects.[Name];

To populate with the list of table names.

You will need to use standard SQL syntax to narrow the list of the tables you want and probably to exclude the table selected in the first combo box if you want to prevent the users from selecting the same two tables.

For the notes .... the 1 & 6 are for tables and attached tables and 4 is for ODBC linked tables so these may or may not be necessary for you (but should not throw an error).

I included the 2nd part of the Where statement so if you have a standard naming convention, you can limit the list to those fitting a specific structure.

-dK

 

[rgreene]

Thanks dK that will give me a direction.