pass through update query sql table using fieldname based on field selection in ListBox

No, the field name must be wrapped in quotes:

'build the SQL string
strS = "UPDATE "
strS = strS & "dbo_t_dispensing_metrics_detail_schedules "
strS = strS & "SET "
strS = strS & "dbo_t_dispensing_metrics_detail_schedules." & Chr(34) & [Forms]![frm_Choice].[list4] & Chr(34) & " = "
strS = strS & "[tbl_Import_Flag].[Field2] "
strS = strS & "FROM "
strS = strS & "tbl_Import_Flag INNER JOIN dbo_t_dispensing_metrics_detail_schedules ON tbl_Import_Flag.Field1;"

Select allOpen in new window

/gustav

thanks will try it out !

getting error on missing operator

You need two fields to match - like:

FROM            
   tbl1
INNER JOIN
   tbl2
   ON tbl1.SomeField = tbl2.SomeOtherField

/gustav

OK,i did still error..
strS = "UPDATE "
strS = strS & "dbo_t_dispensing_metrics_detail_schedules "
strS = strS & "SET "
strS = strS & "dbo_t_dispensing_metrics_detail_schedules.[Forms]![frm_Choice].[List4] = "
strS = strS & "[tbl_Import_Flag].[Field2] "
strS = strS & "FROM "
strS = strS & "tbl_Import_Flag INNER JOIN dbo_t_dispensing_metrics_detail_schedules.Account_Number ON tbl_Import_Flag.Field1;"

This is a pass-through query and trying to update an sql table via odbc connection..as I stated in question.
Could this be causing a problem ?

fordraiders

No, not by itself.
But as long as your syntax is non-SQL, it will fail - as explained in my previous post.

/gustav

The immediate window is showing this:
UPDATE dbo_t_dispensing_metrics_detail_schedules SET dbo_t_dispensing_metrics_detail_schedules."Disengaged" = [tbl_Import_Flag].[Field2] FROM tbl_Import_Flag INNER JOIN dbo_t_dispensing_metrics_detail_schedules.Account_Number ON tbl_Import_Flag.Field1;


The field Name from List4  has quotes around it ?

Thanks
fordraiders

Please, you need two fields to match - like:

 FROM            
    tbl1
 INNER JOIN
    tbl2
    ON tbl1.SomeField = tbl2.SomeOtherField

 /gustav

ok added this to get the field name properly.
strField = [Forms]![frm_Choice].[List4].Value

strS = "UPDATE "
strS = strS & "dbo_t_dispensing_metrics_detail_schedules "
strS = strS & "SET "
strS = strS & "dbo_t_dispensing_metrics_detail_schedules." & strField & " = "
strS = strS & "[tbl_Import_Flag].[Field2] "
strS = strS & "FROM "
strS = strS & "tbl_Import_Flag INNER JOIN [dbo_t_dispensing_metrics_detail_schedules].[Account_Number] ON [tbl_Import_Flag].[Field1];"


still getting missing operator error ?

Thanks
fordraiders

Gustav, Basically trying to get the FieldName from Listbox choice. ?

gustav, Go this to work.  casting .
strS = "Update dbo_t_dispensing_metrics_detail_schedules as Res " & _
"Inner join tbl_Import_Flag as rand  " & _
" on Res.Account_Number = Rand.Field1 " & _
"set Res." & strField & " = Rand.Field2 "

Thanks for the help !!

You are welcome!

/gustav