Re: INSERT INTO statement

Well, part of the problem is one of your field names. Country/Region is going to
get treated as if it were a division problem. Plus you are missing commas
between the fields and have the field list surrounded by parentheses in the
select clause of the source. finally, you need to add the value of the variable
strName to the query and surround that value with quote marks - Chr(34).

This looks as if you are trying to build the query string in VBA and then
execute it. If so, the following code would be closer to what you are doing.
What I don't understand fully is why you are duplicating the record in Contacts.

Dim StrSQL as String
Dim strName As String

'Get a value from a combobox.
'Use Value not Text.
'Text is only available for the control that has the focus
strName = cboIssuedTo.Value

'Build a good SQL string. Space underscore is continuation character(s)
'In a string the continuation character must be outside the quotes

StrSQL = "INSERT INTO Contacts (FirstName, LastName" & _
", Address1, Address2, City, StateOrProvince" & _
",[Country/Region],CompanyName,WorkPhone, FaxNumber) " & _
" SELECT FirstName, LastName, Address1, Address2, City" & _
", StateOrProvince, [Country/Region], CompanyName,WorkPhone, FaxNumber" & _
" FROM Contacts " & _
" WHERE CompanyName =" & CHr(34) & strName & Chr(34)

CurrentDb().Execute StrSQL, dbFailOnError


O Wilson wrote:

Hello,

I got the following syntax from several other online sources and I keep
getting
a "Syntax Error" message. Would someone please what I have done wrong?

Dim strName As String

strName = cboIssuedTo.Text

INSERT INTO "Contacts" (FirstName, LastName, Address1, Address2,
City, StateOrProvince,Country/Region_
CompanyName,WorkPhone, FaxNumber)
SELECT (FirstName, LastName, Address1, Address2, City,
StateOrProvince,Country/Region_
CompanyName,WorkPhone, FaxNumber)
FROM Contacts
WHERE CompanyName = strName
--
O Wilson
.

Relevant Pages

  • Re: Referencing subform during module code
    ... This would work at the form level, but not at the subform. ... Dim strName As String ...
    (comp.databases.ms-access)
  • Re: INSERT INTO statement
    ... Dim StrSQL as String ... Dim strName As String ... O Wilson wrote: ...
    (microsoft.public.access.gettingstarted)
  • Re: make backup of table before edit
    ... Assuming the table you want to copy is named "Employees", ... Dim strName as String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Name checking
    ... Dim strName As String ... MsgBox strname & " not found!" ...
    (microsoft.public.excel.programming)
  • Re: Check last character
    ... I am assuming that you are checking the last character of the docVariable ... Dim strName As String ...
    (microsoft.public.word.vba.general)