Where Clause in SQL Insert Statement

[koolwaters]

I am trying to insert data from a form into a table in Access. The scenario is:

I have a table that contains issues. I also have a table with assigned issues where records are entered when an issue is being assigned to an employee. There are times when multiple issues are assigned to one employee. The table with the assigned issues contains fields like Assigned Date, Assigned To, etc. that will be the same for every issue assigned. The actual issue will be different however.

From the Issues table, I need the IssueID, the OpenedDate and the Select field.
On the form, I have unbound controls where the DateAssigned, AssignedTo, AssignedBy, Priority and AssignedNotes are entered.

The form is unbound but I have a subform which is just being used to select the records to be assigned. I have additional fields on the subform so that you can see the issue name, type, etc. With the exception of the Select field, the subform controls are locked. The fields are only there so the user can actually see the details of the records to be assigned.

Now here is the issue:
I am using an SQL Insert Statement which works fine until I add the Where Clause. I have tried several ways to add the Where clause but to no avail. I need the where Clause because I only want to insert the records that have been checked (=true). Here is my code:

Dim strField1, strField2, strField3, strField4 As Integer
Dim strField5, strField6, strField7 As String
Dim strField8, strField9 As Date
Dim strSQL As String

strField1 = Nz(DMax("[AssIssID]", " tblAssignedIssue"), 0) + 1
strField2 = Field from Form 2
strField3 = Field from Form 1
strField4 = Field from Form 1
strField5 = Field from Form 2
strField6 = Field from Form 2
strField7 = Field from Form 2
strField8 = Field from Form 2
strField9 = Field from Form 1
 
    strSQL = "INSERT INTO tblAssignedIssue ([Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field]) " & _
    "VALUES ('" & strField1 & "','" & strField2 & "','" & strField3 & "','" & strField4 & "','" & strField5 & "','" & strField6 & "','" & strField7 & "','" & strField8 & "', '" & strField9 & "') &_"
      "WHERE ((([tblIssue]![Select] ='" & Forms![frmAssignIssue]![fsubIssue].Form![Select]  & "'));"

        
    DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

Without the Where clause, the code works but only the first record is inserted into the table but I need to insert all of the selected records.

I have been doing some online searching but I am still unable to figure out what is wrong with the where clause.

Thanks in advance for any assistance.

Cheers!

 

[koolwaters]

I am still getting an error. I copied your code to Word, triple checked to make sure the field names were correct and there were no extra spaces, to no avail.

I was able to accomplish what I wanted by creating a third table called tblAssIssueDetails, which I have used as the junction table.

It would be interesting to know what was the error as that seemed a simpler way than having to create a new table.

Thanks for the help though! Cheers!

 

[strive4peace]

When you construct SQL in code, it is a good idea to include:

debug.print strSQL

until the program is working -- then you can delete this or comment it

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)
(or right-click on a blank area in the query design and choose --> SQL View)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

 

[koolwaters]

Thanks for your response strive4peace.

I am trying again to see if this will work or if anyone can determine where I am going wrong. I created a new database with two tables and a form for testing. Hopefully, the “light bulb” will appear.

So I have a form opened and I want to insert values from that form into a table (tblTestSQL). These values are being pulled from tblMyTestSQL.

Private Sub cmdTest_Click()
Dim strSQL As String
Dim strRecordID, strTestID As Integer

    strRecordID= Me.RecordID
    strTestID = Forms!frmMyTestSQL!TestID
    
       strSQL = "INSERT INTO tblTestSQL ([RecordID],[TestID]) " & _
        "VALUES ('" & strRecordID& "','" & strTestID & "')" & _
         "WHERE Forms!frmMyTestSQL!ckSelect = true;"
         Debug.Print strSQL
        
        DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
End Sub

As was the case with the code posted at first, if I remove the Where clause, one record is inserted into the table. With the Where clause, I get Run-time error ‘3067’: Query input must contain at least one table or query.

When I copy the statement from the Immediate window and paste in SQL View of the query and attempt to view in datasheet, I get the same error.

Here is the statement when it is run:
INSERT INTO tblTestSQL ([RecordID],[ TestID]) VALUES ('3','1')WHERE Forms!frmMyTestSQL = true;

 

[koolwaters]

I created a query to do what I wanted and looked at the SQL behind the query. Here is the current SQL statement and it works. I will now apply it in the actual database and see what happens.

strSQL = "INSERT INTO tblTestSQL ([RecordID],[TestID]) " & _
"SELECT [tblMyTestSQL].[RecordID], [tblMyTestSQL].[TestID] " & _
        "From [tblMyTestSQL] " & _
        "WHERE (((tblTestSQL.RecordID)=[Forms]![frmTestSQL]![RecordID]) AND ((tblMyTestSQL.ckSelect)=True));"

Thanks for the help!

 

[strive4peace]

you're welcome.

You may want to, however, take the control reference out of the quoted string

"WHERE (tblTestSQL.RecordID=" & [Forms]![frmTestSQL]![RecordID] & ") ..."