Run-time error '3134' (1 Viewer)

AndyCabbages · Mar 1, 2010

I am getting the following error:

Run-time error '3134'

Syntax error in INSERT INTO statement

This is the piece of code I am directed to:

Private Function addSuite()

If suiteName = "" Or Description = "" Then
MsgBox "Please enter a name and description for this suite!"
Else
If equip1 = "" Or equip2 = "" Then
MsgBox "Please make sure that the first two items are selected!"
Else
If DayPrice = "" Or WeekPrice = "" Then
MsgBox "Make sure you have entered a day price and a week price for this suite!"
Else
addSQL = "INSERT INTO suites (Name, Description, Day_Price, Week_Price, Item1, Item2, Item3, Item4, Item5, Item6, Insurance_Value, Weight) Values ('" & suiteName & "', '" & Description & "', " & DayPrice & ", " & WeekPrice & ", '" & equip1 & "', '" & equip2 & "', '" & equip3 & "', '" & equip4 & "', '" & equip5 & "', '" & equip6 & "', '" & IValue & "', '" & GrossWeight & "')"
DoCmd.RunSQL (addSQL)
DoCmd.Close
End If
End If
End If

End Function

The debugger suggests that the error is on the line:

DoCmd.RunSQL (addSQL)

Any suggestions? I really dont know much about visual basic and cant make such sense of the above piece of code.

Andy

SOS · Mar 1, 2010

Well, one thing jumps out at me right away. You have a field name (NAME) which is an Access Reserved Word and that is not good. You WILL have to use square brackets around it like [Name] EVERY PLACE you use it.

I would suggest renaming that field and going on as NAME can be a very bad, very bad reserved word to use.

ghudson · Mar 1, 2010

Try printing the sql and see what you get when you paste that into a query. Add this to the end of your code and see what is printed in the Immediate window.

Debug.Print addSQL

AndyCabbages · Mar 1, 2010

SOS,

thanks for that advice. I have been having a play around and the error seems to only occur when one of the fields is not entered. Is this something that is never going to work because the way the INSERT INTO statement is written its checking for ALL values? Or is it something that can be changed somewhere to 'trick' the code into working without all the fields being entered?

ghudson · Mar 1, 2010

Then you need to check for nulls in all of the fields and stop the user before they can run the sql if any of the fields are IsNull or an empty string = "".

MSAccessRookie · Mar 1, 2010

AndyCabbages said:
I am getting the following error:

Run-time error '3134'

Syntax error in INSERT INTO statement

This is the piece of code I am directed to:

Private Function addSuite()

If suiteName = "" Or Description = "" Then
MsgBox "Please enter a name and description for this suite!"
Else
If equip1 = "" Or equip2 = "" Then
MsgBox "Please make sure that the first two items are selected!"
Else
If DayPrice = "" Or WeekPrice = "" Then
MsgBox "Make sure you have entered a day price and a week price for this suite!"
Else
addSQL = "INSERT INTO suites (Name, Description, Day_Price, Week_Price, Item1, Item2, Item3, Item4, Item5, Item6, Insurance_Value, Weight) Values ('" & suiteName & "', '" & Description & "', " & DayPrice & ", " & WeekPrice & ", '" & equip1 & "', '" & equip2 & "', '" & equip3 & "', '" & equip4 & "', '" & equip5 & "', '" & equip6 & "', '" & IValue & "', '" & GrossWeight & "')"
DoCmd.RunSQL (addSQL)
DoCmd.Close
End If
End If
End If

End Function

The debugger suggests that the error is on the line:

DoCmd.RunSQL (addSQL)

Any suggestions? I really dont know much about visual basic and cant make such sense of the above piece of code.

Andy

One thing that stands out the most for me is that you never declare or define any of your variables, so it is not possible to verify whether all of the data types being used in the definition are correct. Unless you left it out of the sample code (perhaps because your variables are globally defined), You might want to consider defining them.

AndyCabbages · Mar 1, 2010

Ghudson, I dont quite follow what you mean by suggesting that I print the SQL or how I would get the system to check for nulls

The values have already been globally declared so this is not an issue.

SOS · Mar 1, 2010

He's saying to put

Debug.Print addSQL

Before this line:

DoCmd.RunSQL (addSQL)

and then put a breakpoint on the

DoCmd.RunSQL (addSQL)

line so that you can see what the actual SQL string is. You may be trying to pass NULL Values (just because they are declared doesn't mean that they have a value).

MSAccessRookie · Mar 2, 2010

AndyCabbages said:
Ghudson, I dont quite follow what you mean by suggesting that I print the SQL or how I would get the system to check for nulls

The values have already been globally declared so this is not an issue.

Please understand that I was not referring to whether or not the variables had been defined, as much as HOW they were defined. As a matter of fact, I assumed you had either defined them in another module, or left them out of the example. Since you did not include your shared Global definitions in your example, I was pointing out the fact that it was up to you to verify the type of each of the variables that were being used. If you are satisfied that they are OK, then we move on. If the problem persists, check them again.

Run-time error '3134' (1 Viewer)

AndyCabbages

Registered User.

SOS

Registered Lunatic

ghudson

Registered User.

AndyCabbages

Registered User.

ghudson

Registered User.

MSAccessRookie

AWF VIP

AndyCabbages

Registered User.

SOS

Registered Lunatic

MSAccessRookie

AWF VIP

Similar threads

Users who are viewing this thread