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]

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

Form 1 is the main form
Form 2 is the suubform

 

[james_lankford]

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.

really ?
am I missing something ?

because it looks like you're missing a closing parenthesis

WHERE ((([tblIssue]![Select] ='" & Forms![frmAssignIssue]![fsubIssue].Form![Select] & "'));"

and you really have a field named Select in your table ?

I'm shocked it works at all

and is
strField1
a primary key ? a unique index ?

because if it is then it makes sense that only one record would be inserted

 

[Norie]

The code doesn't compile, try this.

  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] & "';"

Also, before you execute the SQL take a look at it in the debug window.

 

[koolwaters]

Thanks for the responses. I will try in an hour and respond.

 

[koolwaters]

Norie, I tried your suggestion but I get an error message- Compile error: Expected: end of statement

This line is highlighted:

"WHERE  [tblIssue]![Select] ='"

james_lankgord, the field is actually called ckSelect. Yes, strField1 is a primary key.

 

[koolwaters]

Here's what I get after debugging:

debug.Print strsql
 INSERT INTO tblAssignedIssue ([Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field])  VALUES ('1','2014-001','3','','12/24/2014','1','2','55', ' Test')  WHERE [tblAssignedIssue]![Select] ='-1';

 

[Norie]

Did you copy the code I posted, without any changes?

 

[james_lankford]

Here's what I get after debugging:

debug.Print strsql
 INSERT INTO tblAssignedIssue ([Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field])  VALUES ('1','2014-001','3','','12/24/2014','1','2','55', ' Test')  WHERE [tblAssignedIssue]![Select] ='-1';

if that's what your debug statement says then there is no way it works -- even for one record

these
([Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field],[ Field])
have to be actual field names
not, Field, Field, Field
they have to be the actual names of the columns in the table

and this
'12/24/2014'
needs to be
#12/24/2014#

because dates are supposed to be surrounded by hash marks, not single quotes

and you said the actual name of the field was chkSelect
but you're still using Select as the field name
WHERE [tblAssignedIssue]![Select] ='-1';

and if its a true/false or number field then it should just be
= -1
with no quotes

 

[koolwaters]

Hi norie, I did. The only changes I made was to put in the actual fields names. I checked to make sure they were no extra spaces in the field names either but I will try it once more.