Re: INSERT INTO statement

Thanks for all the help. On this question and previous ones. What I am
trying to accomplish is this:

I have two related combos, both are add to's, one for company name and one
for contact person. The company name one works great. ( received help on
that one from Mr. Kallal). The code opens Contactsfrm, puts in the new name
and the user fills out the rest. I'm trying to get a new Contact name, from
an existing company, to be added. I cannot get the Contactsfrm to open, with
a new record and a the new contact person in it. I've spent about two weeks
looking at answers in this forum, and other sources on the web, (with some
time out for banging my head against the wall) and just cannot get it. So I
thought I might use an INSERT INTO statement to get new record in.
--
O Wilson


"John Spencer" wrote:

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