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

2 tables:
tbl_Import_history
dbo_t_dispensing_metrics_detail_schedules

running an update query on sql table, for a specific field based on a selection of field names(field in sql table) in a list4 box.

is the following code correct ?


'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.[Forms]![frm_Choice].[list4] = "
strS = strS & "[tbl_Import_Flag].[Field2] "
strS = strS & "tbl_Import_Flag INNER JOIN dbo_t_dispensing_metrics_detail_schedules ON tbl_Import_Flag.Field1;"

Open in new window




Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
That looks much better.

/gustav
0
 
Gustav BrockCIOCommented:
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;"

Open in new window

/gustav
0
 
FordraidersAuthor Commented:
thanks will try it out !
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
FordraidersAuthor Commented:
getting error on missing operator

missing-operator.png
0
 
Gustav BrockCIOCommented:
You need two fields to match - like:

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

/gustav
0
 
FordraidersAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
No, not by itself.
But as long as your syntax is non-SQL, it will fail - as explained in my previous post.

/gustav
0
 
FordraidersAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
Please, you need two fields to match - like:

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

 /gustav
0
 
FordraidersAuthor Commented:
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
0
 
FordraidersAuthor Commented:
Gustav, Basically trying to get the FieldName from Listbox choice. ?
field name is from listbox choice
0
 
FordraidersAuthor Commented:
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 "
0
 
FordraidersAuthor Commented:
Thanks for the help !!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.