SQL INSERT INTO VALUE statement using a SELECT statement with a variable

Question

I have 2 tables:

Tbl_Task - Has all the tasks for a process (TaskNum is unique. TaskName is what i'm writing to Tbl_Proc)
Tbl_Proc - Will hold the status of each task for a customer

When a new customer is created, I'm using the forms on close event to write each task from Tbl_Task into Tbl_Proc. I'm using the customer name from the form to complete the record in the Tbl_Proc.

My thought is to use an integer to increment the TaskNum and assign that task numbers TaskName to the Tbl_Proc[ProcTaskName]. Then loop through all task numbers. As you can see, I'm stuck on the use of my integer variable.

Here is what I have. I can successfully write the Company name but I can't get the ProcTaskName:

Private Sub Form_Close()
    On Error GoTo Err_Form_Close

    Dim strCompany As String
    Dim sqls As String
    Dim x As Integer

    strCompany = Me![CoName]
    'MsgBox strCompany
    x = 1

    sqls = "INSERT INTO Tbl_Proc(ProcCo, ProcTaskName)" & _
                    "VALUES ('" & strCompany & "', SELECT TaskName FROM Tbl_Task WHERE TaskNum=""" & x & """)"
    DoCmd.RunSQL sqls
'This works - "VALUES ('" & strCompany & "', 'cat')"

Exit_Form_Close:
    Exit Sub

Err_Form_Close:
    MsgBox Err.Description
    Resume Exit_Form_Close

End Sub

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Jun 8, 2017 at 4:32

LordBaconPants · Accepted Answer · 2017-06-08 02:13:17Z

2

If I understand what you are asking correctly, you want your sql to look something like this:

INSERT INTO Tbl_Proc(ProcCo, ProcTaskName)
    SELECT <yourCompanyVariable>, TaskName 
    FROM Tbl_Task 
    WHERE TaskNum=<whateverItIsYouHaveHere>

The select will return all of the tasks with your given number, each marked with your company variable. All of that will be inserted into your table.

If you can define everything you want in tbl_task easily, like where defaultTasks = 1 you can insert all of the tasks you need in one query without having to loop.

edited Jun 8, 2017 at 2:13

answered Jun 8, 2017 at 2:06

LordBaconPants

1,4241 gold badge20 silver badges23 bronze badges

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

Comments

Matthew · Accepted Answer · 2017-06-08 06:14:08Z

0

Thanks for the input man of much desired pants... :) I added another column to Tbl_Tasks [TaskDefault] and made all values 1. The following code works within Microsoft Access 2013 VBA module.

sqls = "INSERT INTO Tbl_Proc(ProcCo, ProcTaskName)" & _
        "SELECT '" & strCompany & "', TaskName " & _
        "FROM Tbl_Task WHERE TaskDefault=1"
DoCmd.RunSQL sqls

answered Jun 8, 2017 at 6:14

Matthew

352 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL INSERT INTO VALUE statement using a SELECT statement with a variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related