Hello,

I have a number of tables that must be used in append and update queries, one right after the other. I have set up a form to populate the names of all of the tables in a combo box. I have a query built to run the updates, but I need a way to change the source table for the updates using the combo box. I tried to reference the form field in the query but I got an error.

UPDATE [Forms]![frmSelectTable]![cbxTableName] INNER JOIN [All CRSAWs as of 1 August 2008] ON [Forms]![frmSelectTable]![cbxTableName]![artfct_stat_cd]

cbxTableName is the combo box name

You cannot specify a table or field name as a parameter in a query.

You'll have to dynamically generate the SQL and run it using the Execute method of the Database object (the preferred method), or DoCmd.RunSQL.

Of course, the SQL you have above isn't valid: your ON statement isn't rigiht, and you don't list any fields that you're updating.

I tried to set is up using the DoCmd method but it is getting hung up on tmpName. Any thoughts?

Set TableNameForm = Forms("frmSelectTable")
tmpName = TableNameForm.cbxTableName.Value

SQL = "INSERT INTO [All CRs] (artfct_id, artfct_stat_cd, artfct_ver_id, fed_supl_clas_cd, " & _
"nat_itm_id, mtl_mgt_agg_cd, cage_cd, ref_nr_id, prev_rmc, prev_rmsc, " & _
"rmc, rmsc, rmc_rmsc_exp_dt, saw_pri_cd, create_dt, created_by, create_1st_nm, " & _
"create_last_nm, create_mid_init_tx, create_ofc_smbl_id, create_org_cd, " & _
"lst_updt_usr_id, lst_updt_dt, ann_rpr_bud_val_am ) SELECT artfct_id, " & _
"artfct_stat_cd, artfct_ver_id, fed_supl_clas_cd, " & _
"nat_itm_id, mtl_mgt_agg_cd, cage_cd, ref_nr_id, prev_rmc, prev_rmsc, " & _
"rmc, rmsc, rmc_rmsc_exp_dt, saw_pri_cd, create_dt, created_by, create_1st_nm, " & _
"create_last_nm, create_mid_init_tx, create_ofc_smbl_id, create_org_cd, " & _
"lst_updt_usr_id, lst_updt_dt, ann_rpr_bud_val_am FROM & tmpName &;"
DoCmd.RunSQL SQL

Your quotes are wrong. tmpName needs to be outside of the quotes. It's also a good idea to put the table name inside square brackets, just in case it's a reserved word or has special characters (such as spaces) in it:

SQL = "INSERT INTO [All CRs] (artfct_id, artfct_stat_cd, artfct_ver_id, fed_supl_clas_cd, " & _
"nat_itm_id, mtl_mgt_agg_cd, cage_cd, ref_nr_id, prev_rmc, prev_rmsc, " & _
"rmc, rmsc, rmc_rmsc_exp_dt, saw_pri_cd, create_dt, created_by, create_1st_nm, " & _
"create_last_nm, create_mid_init_tx, create_ofc_smbl_id, create_org_cd, " & _
"lst_updt_usr_id, lst_updt_dt, ann_rpr_bud_val_am ) SELECT artfct_id, " & _
"artfct_stat_cd, artfct_ver_id, fed_supl_clas_cd, " & _
"nat_itm_id, mtl_mgt_agg_cd, cage_cd, ref_nr_id, prev_rmc, prev_rmsc, " & _
"rmc, rmsc, rmc_rmsc_exp_dt, saw_pri_cd, create_dt, created_by, create_1st_nm, " & _
"create_last_nm, create_mid_init_tx, create_ofc_smbl_id, create_org_cd, " & _
"lst_updt_usr_id, lst_updt_dt, ann_rpr_bud_val_am FROM [" & tmpName & "]"