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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
FordraidersAuthor Commented:
getting error on missing operator

missing-operator.png
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
Gustav BrockCIOCommented:
That looks much better.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks for the help !!
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.