Populating records in a table

[gavintennant]

Hi,

Can anyone tell me if the following is possible, if so, how I would go about it or any other ideas I've not thoguht of!!

Okay, I have several tables and I'm trying to build a form with a combo box on it. This combo box should contain all fields from all the tables. (or a selection of so no duplicates or info I don't need)

From this combo box, I'll be taking the selection and plugging it into a query that can then search through each of the original tables for that occurence and display the results on the form.

My original idea was to have a module that created the table and for each individual record, I'd type it directly into the module so it is created each time the db is opened (the selection of tables change regularly and is easier to keep it clear of any of my own stuff.) This table will only have one column.

Is there any way I can automatically populate this table without having to resort to typing values in by hand?

Thanks very much for any ideas you may have!

 

[AlexMidd]

Populate the table with what? i.e. where does the data come from? If from another table then you can use an update query. Alex Middleton

 

[gavintennant]

Hi,

I want to populate the new table with only the headers (fields) from the exisiting tables. Basically, I have about 20 tables each with at least 40 fields in each. It is these fields I want to put in a new table (in one column) so I can select from a combo box.

I would rather not have a table that is always there, only created when the database is opened for the first time. If this is the only way though, I'll just have to do that...

Gavin

 

[AlexMidd]

I would do this using code to loop through the tables definitions (tabledefs) in the database then SQL in the code to create the table and add the field names. This is not a trivial task and I don't have time to set out the code for it. If you search help for Tabledefs collection, then look at the example "Tabledef Object, Tabledefs Collection Example (DAO/Microsoft Jet)", this will give some ideas on how to get at the tabledefs collection.
Alex Middleton

 

[gavintennant]

Thanks, I'll give this a go!!
Much appreciated

 

[jes]

Hi,

After much faffing about, what I found best to do in this situation was to set up a recordset based on an sql statement that basically goes

Select Name From MSysObjects Where Type = 1;

This gives a list of all the tables within your database. Then you can use the tabledefs stuff mentioned in the previous response to access the field names.

As Alex said, it's quite complex but teaches you a lot about SQL and access, so worth doing yourself.

Hope it helps