Field Mapping

[CBRAY]

Hi
I am trying to use a form to append data in several tables to one main table by mapping field names and feeding them into SQL. My data is supplied in one format but the column order will always vary.

On my form I select a table name [Points] from a list box 'lsttables' which uses MSysObjects as row source to show all the tables in the database. The On Click event for lsttables sets the row source of a combo box 'cmbid' to lsttables.value, the row source type is field list and this then allows me to dropdown the field names for the table selected [ID]. In my main table [All Points] I have a field [obsId]. The SQL for a one off append query looks like this:

INSERT INTO [All Points] ( obsId ) SELECT Points.ID FROM Points;

I want to be able to replace 'points.ID From Points' with the values selected in 'lsttables' and 'cmbid'.

Is this possible?

Many thanks

 

[workmad3]

Dim sql as String

sql = "INSERT INTO [All Points](obsID) SELECT " & cmbid & " FROM " & lsttables

then just give sql as the argument for whatever you are using to execute the sql

 

[CBRAY]

Thanks WorkMad3 it works a treat