Insert row, table to table - where append goes to correct record

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

Billp

Hi,

As I cannot use INSERT INTO with values specified to WHERE.
I have created an interim temporary table where the record contains two text
strings.Now I want to be able to take those text strings and append to the
correct table where the record is set by an ID number.

First_Name = Fred
Last_Name = Jones

Its a new contact so it is too be added to a customer with an ID = 1375

Now I can't use the following sql statement
strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & """, """ & strLast &
""")" _
& " WHERE [CustID] = '" & Me![CustID] & "';"

because "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."
from Ken Snell

so I took the text and Inserted into a temp table
strsql_Temp = "INSERT INTO [tbltempSERContact] ( [FIRST_NAME], [LAST_NAME] )
" _
& " VALUES (""" & strFirst & """, """ & strLast &
""");"

now I want to be able to take this and append to the correct customer

'now append row from tbltempSERContact into tblCustomers
strsql = "INSERT INTO [tblCUSTCONTACTS] [FIRST_NAME],
[LAST_NAME] " _
& " SELECT [FIRST_NAME], [LAST_NAME]" _
& " FROM [tbltempSERContact]" _
& " WHERE CustID = " & Me!CustID
Debug.Print strsql
which gives a print out of
INSERT INTO [tblCUSTCONTACTS] [FIRST_NAME], [LAST_NAME] SELECT
[FIRST_NAME], [LAST_NAME] FROM [tbltempSERContact] WHERE CustID = 1391

And the error
Run-Time 3134
Syntax error in INSERT INTO

I'm getting lost here - I have text derived via VBA = values which I cannot
append to a correct customer - HELP assitance would really be appreciated
 
Billp said:
As I cannot use INSERT INTO with values specified to WHERE.
I have created an interim temporary table where the record contains two text
strings.Now I want to be able to take those text strings and append to the
correct table where the record is set by an ID number.

First_Name = Fred
Last_Name = Jones

Its a new contact so it is too be added to a customer with an ID = 1375

Now I can't use the following sql statement
strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & """, """ & strLast &
""")" _
& " WHERE [CustID] = '" & Me![CustID] & "';"

because "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."
from Ken Snell

so I took the text and Inserted into a temp table
Click to expand...
[snip the stuff that went totally off the rails]

If you want to insert a new contact into the contacts table
with a foreign key to the current customer record, then use
the current customer record's primary key value in the value
list, not in a where clause.

"INSERT INTO [tblCUSTCONTACTS] " _
& "(CustID, [FIRST_NAME], [LAST_NAME]) " _
& " VALUES (" & Me.CustID & ",""" & strFirst & """, """
& strLast & """)"
 
Thank you Marrshall

Marshall Barton said:
Billp said:
As I cannot use INSERT INTO with values specified to WHERE.
I have created an interim temporary table where the record contains two text
strings.Now I want to be able to take those text strings and append to the
correct table where the record is set by an ID number.

First_Name = Fred
Last_Name = Jones

Its a new contact so it is too be added to a customer with an ID = 1375

Now I can't use the following sql statement
strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _
& " VALUES (""" & strFirst & """, """ & strLast &
""")" _
& " WHERE [CustID] = '" & Me![CustID] & "';"

because "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."
from Ken Snell

so I took the text and Inserted into a temp table
Click to expand...
[snip the stuff that went totally off the rails]

If you want to insert a new contact into the contacts table
with a foreign key to the current customer record, then use
the current customer record's primary key value in the value
list, not in a where clause.

"INSERT INTO [tblCUSTCONTACTS] " _
& "(CustID, [FIRST_NAME], [LAST_NAME]) " _
& " VALUES (" & Me.CustID & ",""" & strFirst & """, """
& strLast & """)"
Click to expand...
 
Back
Top