INSERT INTO statement

[O Wilson]

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

 

[John Vinson]

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

You've put quotemarks around the target table name, for one thing, and
(probably) left the required square brackets off of Country/Region.
Also, if strName is a VBA variable it's not accessible to your Query.

It also appears that you're typing SQL statements directly into VBA
code. This won't work; they're different languages, and Access won't
be able to make sense of it. Es ist als ich mein Antwort auf Deutsch
schrieb.

What strikes me odd is that you seem to be pulling records from
Contacts and inserting them, verbatim, back into Contacts. At best
this will simply duplicate all of the records in Contacts for the
chosen company. What are you INTENDING to accomplish?

John W. Vinson[MVP]

 

[John Spencer]

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]

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

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

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

 

[John Vinson]

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.

I think you're making this MUCH harder than it is.

If each Company has multiple Contacts, consider using a Form based on
Companies, with a Subform based on Contacts. Use CompanyID as the
subform's Master/Child Link Field.

Set the Subform's properties to its default values - Allow Edits,
Allow Additions, Allow Deletions. You may want to make it a Continuous
form rather than the default Datasheet view.

This construct will let you enter a new contact into the Contacts
table - which it appears is what you want.

If you mean that you want to take an old contact, and insert that
person as a new record in the Contacts table, just associated with a
new company, then I guess I don't understand your table structure or
your business model... if that's the case, could you explain?

John W. Vinson[MVP]

 

[O Wilson]

You're right, I am making this way to hard and it can't be that tough. I
have set up a form based on Companies and inserted a sub form called Contact.
I also have several other forms that have two related, add to combo boxes,
cboCompany and cboContact. All I want to do is to give the user an
opportunity to add a new company and/or add a new contact to an existing
company. I have the cboCompany code working with this:

If MsgBox("Do you want to add this value to the list?", _
vbYesNo) Then
DoCmd.OpenForm "Companiesfrm", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

and this code is in the On Load procudure of Companiesfrm:
' Insert new Company from another form

If IsNull(Me.OpenArgs) = False Then
Me.CompanyName = Me.OpenArgs

End If

I cannot seem to get the Companiesfrm to open with existing company loaded
and a blank Constactssubfrm.

Any help will be greatly appreciated.