Update or insert field from table into another table VBA sql

Question

First this is my first post so I apologize for the layout or any other errors with presentation.

I have two tables.

tempWkEndHrs: Is created when the user selects a weekending date and their name from two comboboxs. The result is 5 fields: Name1, Task, Closed Date, Initiated Date and #xx/xx/xxxx# <--the same date as chosen by the user from the combobox to create the table. This is done to limit the number of date fields returned and built into the temp table.

The purpose of this table is so the user can populate as many values under the #xx/xx/xxxx# field/column to show/see the distribution of hours taken on tasks for that week. (its also used as a check to make sure entered values sum up as expected)

I would like to save the new values entered underfield #xx/xx/xxxx# in tempWkEndHrs to the same field #xx/xx/xxxx# in tblHrs.

tblHrs: has fields... Task , #xx/xx/xxxx#, #xx/xx/xxxx#, #xx/xx/xxxx#, #xx/xx/xxxx#, etc.. <--Same dates available within combobox.

I have having difficulty finding an example of updating multiple records from one table to another to fit my situation.

Below is my last attempt from several variations.

Dim strITEM As String
Dim strWkEnd As String

strWkEnd = Me.cmbWkEnd
strSQL = "UPDATE tblHrs SET " & _
        "[" & strWkEnd & "] = [" & strWkEnd & "] " & _
        "FROM tempWkEndHrs " & _
        " WHERE [Task] = [Task]"

*Update 10-24-2017 5:37pm

Dim strITEM As String
Dim strWkEnd As String

strWkEnd = Me.cmbWkEnd
strSQL = "UPDATE tblHrs " & _
       "SET tempWkEndHrs.[" & strWkEnd & "] = tblHrs.[" & strWkEnd & "] "& _
       "FROM tempWkEndHrs " & _
       " WHERE tempWkEndHrs.[Task] = tblHrs.[Task]"

Result: UPDATE tblHrs SET tempWkEndHrs.[9/30/2017] = tblHrs.[9/30/2017] FROM tempWkEndHrs WHERE tempWkEndHrs.[TASK] = tblHrs.[TASK] ***Run-Time error '3075 ***Syntax error(missing operator) in query expression 'tblHrs.[9/30/2017] FROM tempWkEndHrs'.

If this is clear as mud please let me know.

enter image description here

Ben.Name · Accepted Answer · 2017-10-25 01:59:22Z

0

BIG SHOUT OUT TO

Oscar Anthony and Parfait

https://stackoverflow.com/a/34910889/8797058

5.You don't need the FROM tblTemp clause in the SQLReplace String.

6.EDIT: As @Parfait pointed out, tblTemp does not exist in scope of the SQLReplace statement. You should do an INNER JOIN to fix that: <--AWESOME!!

strWkEnd = Me.cmbWkEnd
strSQL = "UPDATE tblHrs INNER JOIN tempWkEndHrs ON tblHrs.TASK = tempWkEndHrs.TASK SET tblHrs.[" & strWkEnd & "] = tempWkEndHrs.[" & strWkEnd & "] "

Works! >:P

I didn't receive any answers to this post so obviously I need to write better questions, I figured it was mud...Thanks for the ~21 Views ?lol.

answered Oct 25, 2017 at 1:59

Ben.Name

591 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update or insert field from table into another table VBA sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related