Now asking for missing semicolon ???????? - Guidance please

  • Thread starter Thread starter Billp
  • Start date Start date
B

Billp

Just when things seemed to be going well and then a corrupt form which had to
be rebuilt along with the VBA behind it I get.
An error 3137 - missing semi colon

strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & """, """ & strLast &
""")" _
& " WHERE [CustID] = " & Me![CustID]

Immediate Print out of

INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) VALUES
("Fergie", "Fred") WHERE [CustID] = 1391


Even if I Have

strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & """, """ & strLast &
""")" _
& " WHERE [CustID] = '" & Me![CustID] & "';"

Print out
INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) VALUES
("Fergie", "Fred") WHERE [CustID] = 1391INSERT INTO [tblCUSTCONTACTS] (
[FIRST_NAME], [LAST_NAME] ) VALUES ("Fergie", "Fred") WHERE [CustID] =
'1391';

I get the same error.

I cannot use an update query as I am appending (so I believe that limits me
to an INSERT INTO) a new lot of data to a table where.

Assistance apprecaited - I read Allen Brownes esay on Quotation marks in
statements - still blind.
Cheers and best wishes
 
When you have a VALUES list for an append query, you cannot have a WHERE
clause. SQL engine has no idea which table's records are to be filtered
because you've provided the value list for the fields as the data source.
 
Oh darn.

What else can I use or a way to Append new data where a criteria is met?

Now I ma blind and lost.
Help and guidance really appreciated.

Ken Snell said:
When you have a VALUES list for an append query, you cannot have a WHERE
clause. SQL engine has no idea which table's records are to be filtered
because you've provided the value list for the fields as the data source.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


Billp said:
Just when things seemed to be going well and then a corrupt form which had
to
be rebuilt along with the VBA behind it I get.
An error 3137 - missing semi colon

strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & """, """ & strLast &
""")" _
& " WHERE [CustID] = " & Me![CustID]

Immediate Print out of

INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) VALUES
("Fergie", "Fred") WHERE [CustID] = 1391


Even if I Have

strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & """, """ & strLast &
""")" _
& " WHERE [CustID] = '" & Me![CustID] & "';"

Print out
INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) VALUES
("Fergie", "Fred") WHERE [CustID] = 1391INSERT INTO [tblCUSTCONTACTS] (
[FIRST_NAME], [LAST_NAME] ) VALUES ("Fergie", "Fred") WHERE [CustID] =
'1391';

I get the same error.

I cannot use an update query as I am appending (so I believe that limits
me
to an INSERT INTO) a new lot of data to a table where.

Assistance apprecaited - I read Allen Brownes esay on Quotation marks in
statements - still blind.
Cheers and best wishes
Click to expand...


.
Click to expand...
 
You'll need to give us a more detailed explanation about what you are
wanting to do. Then we can make suggestions.
--

Ken Snell
http://www.accessmvp.com/KDSnell/




Billp said:
Oh darn.

What else can I use or a way to Append new data where a criteria is met?

Now I ma blind and lost.
Help and guidance really appreciated.

Ken Snell said:
When you have a VALUES list for an append query, you cannot have a WHERE
clause. SQL engine has no idea which table's records are to be filtered
because you've provided the value list for the fields as the data source.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


Billp said:
Just when things seemed to be going well and then a corrupt form which
had
to
be rebuilt along with the VBA behind it I get.
An error 3137 - missing semi colon

strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) "
_
& " VALUES (""" & strFirst & """, """ & strLast
&
""")" _
& " WHERE [CustID] = " & Me![CustID]

Immediate Print out of

INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) VALUES
("Fergie", "Fred") WHERE [CustID] = 1391


Even if I Have

strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) "
_
& " VALUES (""" & strFirst & """, """ & strLast
&
""")" _
& " WHERE [CustID] = '" & Me![CustID] & "';"

Print out
INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) VALUES
("Fergie", "Fred") WHERE [CustID] = 1391INSERT INTO [tblCUSTCONTACTS] (
[FIRST_NAME], [LAST_NAME] ) VALUES ("Fergie", "Fred") WHERE [CustID] =
'1391';

I get the same error.

I cannot use an update query as I am appending (so I believe that
limits
me
to an INSERT INTO) a new lot of data to a table where.

Assistance apprecaited - I read Allen Brownes esay on Quotation marks
in
statements - still blind.
Cheers and best wishes
Click to expand...


.
Click to expand...
Click to expand...
 
If you're appending (that is, adding a new record) how can Access find
a "new" row that already contains a CustID? If you're truly appending
then the CustID becomes one of the pieces of data that you would be
writing. If instead you are adding the names to a record that already
contains the CustID then you are in fact updating.

Can you be more clear about what you are trying to do?

Regards,
RD
 
Back
Top