INSERT INTO Syntax Error

[Microsoft Newsgroups]

I have the following code attached to a command button on a form. When I
click on the button, I get an error message that says: Syntax error in
INSERT INTO statement. I've checked everything I can and can't find the
error. Please help.

Thanks

Dave
----

CODE:

Private Sub cmdGetDefaultMaterials_Click()
On Error GoTo Err_cmdGetDefaultMaterials_Click

Dim strSQL As String

strSQL = "INSERT INTO tblHouseOptions (Option) VALUES ('BASE HOUSE')"
DoCmd.RunSQL (strSQL)

Exit_cmdGetDefaultMaterials_Click:
Exit Sub

Err_cmdGetDefaultMaterials_Click:
MsgBox Err.Description
Resume Exit_cmdGetDefaultMaterials_Click

End Sub

 

[Wayne Morgan]

Verify that the table name (tblHouseOptions) and field name (Option) are
spelled correctly. Also, make sure that there aren't any required fields in
the table. You're only adding a value to one field of the record, if one of
the other fields is required, you're not going to be able to create the new
record (there is a way to do this, but it sort of defeats the "Required"
purpose). Is Option a Text data type field?

If that doesn't do it, we could try a couple of items that shouldn't be the
problem.

1) A SQL statement usually ends with a semicolon (.
2) Try double quotes instead of single quotes. You'll have to double them so
that VBA knows there are to be included instead of them being the end of the
string.

strSQL = "INSERT INTO tblHouseOptions (Option) VALUES (""BASE HOUSE"");"

 

[Microsoft Newsgroups]

Thanks for the reply.

1. tblHouseOptions and Option are valid names and Option is a text data
type.
2. There is a required field in the table -- HouseOptionID which is an
AutoNumber type.
3. Tried your suggested sysntax -- same error.

The table is related to two other tables.

tblHouse:
HouseID AutoNumber --> Primary key related to HouseID in
tblHouseOptions (One to Many)
Other text & date fields

tblHouseOptions:
HouseOptionID AutoNumber --> Primary key related to HouseOptionID in
tblHouseMaterials (One to Many)
HouseID Long Integer
Option Text
Remarks Memo

tblHouseMaterials:
HouseMaterialID AutoNumber
HouseOptionID Long Integer
Other text & date fields

Any other ideas?? Thanks.

Dave

 

[Wayne Morgan]

I suspect you will also need to fill in the HouseID field to link the record
to a record in the tblHouse table. Try creating a new record by manually
filling in just the Option field in tblHouseOptions and see what error you
get. That may give you some more information. The autonumber field being
required isn't a problem, it will generate a value automatically.

 

[Dave - MS Newsgroups]

When I try adding a value to Option in tblHouseOptions manually, I get the
following:

You cannot add or change a record because a related record is required in
tblHouse.

I also tried these lines of code:

strSQL = "INSERT INTO tblHouse(HouseID,BidName) VALUES(10,'TEST')"
DoCmd.RunSQL (strSQL)

strSQL = "INSERT INTO tblHouseOptions(HouseID,Option) VALUES
(10,'TEST')"
DoCmd.RunSQL (strSQL)

The first works fine. The second gives me the syntax error.

Going to sleep to dream about this one. Thanks for your help. If you think
of anything else, please advise.

Dave

 

[Wayne Morgan]

The error you received is what I suspected. The table is linked to tblHouse.
You will need a value in the field HouseID that exists in tblHouse so that
the link can be enforced.

Be careful with that first SQL statement. You are manually adding a value to
an autonumber field. This is sometimes useful as a repair option, but I
don't recommend it otherwise.

 

[Ron Weiner]

Try

DoCmd.RunSQL strSQL

No parens!

 

[Dave - MS Newsgroups]

Thanks -- tried your suggestion -- still get error.

Dave

 

[Ron Weiner]

strSQL = "INSERT INTO tblHouseOptions (Option) VALUES ('BASE HOUSE')"
DoCmd.RunSQL strSQL

Works just fine on my system. If you are having problems than the problem
has got to be a problem with the name of the table "tblHouseOptions" or a
problem with the name of the column "Option". You can test by Pasting the
Sql text into a blank query and executing the query. Perhaps the error
message will give you a hint of what the problem is.

 

[Comcast Newsgroup]

Thanks for the suggestion. Will try it.

I think it has something to do with the fact that this table is related to a
parent table (on the field HouseID) and Access won't let me add a record to
this table without a matching record in the parent table. But, I've tried
to use the INSERT statement with a matching record that exists in the parent
table -- still get error.

I'm going to try some variations tonight and see what happens.

Dave

 

[Dave - MS Newsgroups]

Well, after hours of staring at a computer screen, I finnally got my routine
to do what I wanted. The problem seems to have been threefold:

1. A record had to be present in the parent table before I could enter one
in the child table and the IDs had to be the same (not surprising).
2. I was using the name Option for one of my fields and with some
constructs in the SQL statement, Access asked me for a parameter when I
executed the SQL statement. I think it was because Option is a reserved
word.
3. The syntax for a SQL statement is not straight forward.

Dim strSQL As String
Dim strNewItem As String
strNewItem = a_varSelectedOptions(varItem)

' This does not work
strSQL = "INSERT INTO tblProductionOptions (ProdOrdID, ProdOption)
VALUES (1, strNewItem)"

' This works fine
strSQL = "INSERT INTO tblProductionOptions (ProdOrdID, ProdOption)
VALUES (1, 'NEXT')"


' This is what I needed -- took me forever to figure out the "'&
strNewItem & "' construct
strSQL = "INSERT INTO tblProductionOptions (ProdOrdID, ProdOption)
VALUES (1,'" & strNewItem & "')"

DoCmd.RunSQL strSQL

Thanks for your help.

I learned a great deal from this exercise -- even if it was frustrating

Dave