Help with Runtime Error 3142

[KLahvic01]

I am recieving the following run time error when I click the 'Owned?' check box on my form.

Run-time error '3142':

Characters Found After End of SQL Statement

When I debug, this is what it highlights:

dbs.Execute strSQL, dbFailOnError

Which is part of this code string:

Private Sub Owned__AfterUpdate()
If Me![Owned?] = -1 Then 'Checks that item was selected
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim strSQL As String
strSQL = "INSERT INTO tblPersonalCollection (Description, CardNumber, CarNameID, SeriesID, YearID) " & "Values (Me!MstrDescription, Me!MstrCardNumber, Me!MstrCarName, Me!MstrSeriesName, Me!MstrProdYear);” "
dbs.Execute strSQL, dbFailOnError
Else
Exit Sub 'Exits sub if item wasn't selected
End If

End Sub

I have searched around a bit and found that an ';' might cause this error as it will stop an SQL statement, but the only ';' is at the end and taking it out only errors the database again with an expected ';' message.

Any help would be appreciated.

KJL

 

[mdlueck]

In your VALUES statement you need to use string concatenation in order to mix the static SQL with the variable values.

To do such via primitive methods, you need something along the lines of:
http://stackoverflow.com/questions/1802120/building-sql-strings-in-access-vba

Or you may use ADO objects and use this which is hardened against a dynamic value messing up the SQL string concatenation / aka SQL injection attack.

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

 

[MarkK]

It looks like you have two different types of double quotes in your string. Do you sometimes use a keyboard set for a different language? You need to use the double-quotes that are produced by chr(34). One of your double-quotes is chr(148), which will not work.

Also, SQL will not recognize the Me!NamedField constructs you are using in your string. Those are named members of a collection, which is valid in VBA, but not in SQL. Consider something like this, where the evaluation of the VBA references occurs outside the string ...

strSQL = _
    "INSERT INTO tblPersonalCollection " & _
        "( Description, CardNumber, CarNameID, SeriesID, YearID ) " & _
    "VALUES " & _
        "( '" & Me!MstrDescription & "', " & Me!MstrCardNumber & ", " & _
            Me!MstrCarName & ", " & Me!MstrSeriesName & ", " & _
            Me!MstrProdYear & " ) ;"

Do you see what's going on there?
Cheers,

 

[KLahvic01]

I do see what is happening, thank you for the updated code, I am not at all familiar with SQL. Here is what is happening now:

Run-time error 3075

Syntax Error Missing Operator in query expression

When I Debug.Print, this is what I get:

INSERT INTO tblPersonalCollection ( Description, CardNumber, CarNameID, SeriesID, YearID ) VALUES ( 'Orange, w/Silver 'Viper' on sides, Silver & White stripes across top, Chrome Malaysia Base, w/OH5SP's ', 168, ’06 Dodge Viper , Treasure Hunts , 2008 ) ;

Any ideas as to why this would happen?

 

[MarkK]

Yeah, the delimiters in your value list don't look matched. Strings in SQL need to be delimited using double or single quotes, and for each opening quote there needs be a closing quote.

 

[KLahvic01]

I see, I reviewed my SQL string and it doesnt look like I am missing any quotes (single or double). Here is the code string, I hate to be a bother, but do you see anything?

strSQL = _
    "INSERT INTO tblPersonalCollection " & _
        "( Description, CardNumber, CarNameID, SeriesID, YearID ) " & _
    "VALUES " & _
        "( '" & Me!MstrDescription & "', " & Me!MstrCardNumber & ", " & _
            Me!MstrCarName & ", " & Me!MstrSeriesName & ", " & _
            Me!MstrProdYear & " ) ;"

Thanks for your help.

 

[MarkK]

This is the string that ends up being processed, right ....?

INSERT INTO tblPersonalCollection ( Description, CardNumber, CarNameID, SeriesID, YearID ) VALUES ( 'Orange, w/Silver 'Viper' on sides, Silver & White stripes across top, Chrome Malaysia Base, w/OH5SP's ', 168, ’06 Dodge Viper , Treasure Hunts , 2008 )

If CardNumber is string field in the table, data needs to be enclosed in quotes. My code doesn't do that for that field. You need to determine what type the fields are in your tables and delimit your input data appropriately, but it's not hard to see, in the quoted text above, that the quotes aren't going to work ...

INSERT INTO tblPersonalCollection ( Description, CardNumber, CarNameID, SeriesID, YearID ) VALUES ( 
'Orange, w/Silver '
Viper
' on sides, Silver & White stripes across top, Chrome Malaysia Base, w/OH5SP'
s 
', 168, ’
06 Dodge Viper , Treasure Hunts , 2008 )

... see how your quotes break up that string?

 

[KLahvic01]

This is the string that ends up being processed, right ....?

Yes that is correct.

If CardNumber is string field in the table, data needs to be enclosed in quotes. My code doesn't do that for that field. You need to determine what type the fields are in your tables and delimit your input data appropriately, but it's not hard to see, in the quoted text above, that the quotes aren't going to work ...

This is where I am lost, CardNumber and MstrCardNumber are both categorized as 'Text' in the tables but their values are numeric. Do I need to change that?

... see how your quotes break up that string?

I'm sorry, I really am trying to understand this, I figured it was over my head, but in the past I have been able to meddle through this stuff and get it figured out, this has stymied me and I am not grasping this as well as I think I should. I dont know where to put the quotes or remove quotes etc. Just keep spinning my wheels...

I appreciate your help and I appologize for being an annoying noob!

I hope you can help.
KJL

 

[MarkK]

Read up on 'data types'. The concepts are fundamental, and without understanding them you will not be able to have control of the process of manipulating data.

Generally though, to tell a windows computer that you are dealing with strings, you enclose them in quotes. If you add strings, they concatenate ...

"123" + "345" = "123345"

If your table has a text field, then data you push in there will need to be delimited with quotes, so look at this SQL. The different data types are delimited differently.

INSERT INTO Table ( TextField, NumberField, DateField )
VALUES ( 'TextValue', NumericValue, #DateValue# )

Now, if I want to parameterize pushing the date data into that SQL, and SQL is a string command, it'll have to work like ...

dim sql as string
sql = _
   "INSERT INTO Table ( TextField, NumberField, DateField ) " & _
   "VALUES ( 'TextValue', NumericValue, #" & Date() & "# )"

... so that's parameterized to always use today's date, but see how the date delimiters are still present in the SQL string?
And if you wanted to parameterize a text field ...

dim tmp as string
dim sql as string
tmp = "This is a test"
sql = _
   "INSERT INTO Table ( TextField  ) " & _
   "VALUES ( '" & tmp & "' )"

Hope that helps,

 

[KLahvic01]

That makes more sense now. I will read up on that stuff and make another crack at this.

Thank you for your help!

 

[KLahvic01]

Hope that helps,

This has been phenomonal advice, and I think I am almost there. After reading up on data types and all, I tried to write my own SQL string and after a few revisions, I think I am close.

Here is the string that populates with the debug.print:

INSERT INTO tblCollection ( CollProdYear, CollSKUNumber, CollCardNumber, CollCarName, CollSeriesName, CollDescription ) VALUES ( 2008, , 168, '’06 Dodge Viper ', 'Treasure Hunts ', 'Spectraflame Copper, w/Silver 'Viper' on sides, Silver & White stripes across top, Chrome Malaysia Base, w/RR5SP's ' )

Here is the code I am using that gets me there:

Private Sub Owned__AfterUpdate()
If Me![Owned?] = -1 Then
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim sql As String
sql = _
    "INSERT INTO tblCollection ( CollProdYear, CollSKUNumber, CollCardNumber, CollCarName, CollSeriesName, CollDescription ) " & _
    "VALUES ( " & Me!MstrProdYear & ", " & Me!MstrSKUNumber & ", " & Me!MstrCardNumber & ", '" & Me!MstrCarName & "', '" & Me!MstrSeriesName & "', '" & Me!MstrDescription & "' )"
 
Debug.Print sql
 
dbs.Execute sql, dbFailOnError
Else
Exit Sub
End If
End Sub

What is happening now, which makes me feel I am a step closer is I get the following Runtime error:

Run Time Error 3134
Syntax Error in INSERT INTO Statement

I have checked to see if my table name and field names are all spelled correctly and they are, I checked to make sure that the order is the same for the INSERT INTO and VALUES section and they are. What else can it be?

Thanks again!

KJL

 

[mdlueck]

Your Me!MstrDescription appears to have invalid quote characters included in the string.

That is what I meant about suggesting the use of adoCMD / adoParameters objects to protect against such.

With passing in the values in the SQL string, one must be sure SQL + the data are still syntactically correct in the eyes of the SQL interpreter. You have quote errors.

You could correct this via using the post suggestion I listed above.

 

[MarkK]

Can you see what might fail about this?

'Spectraflame Copper, w/Silver 'Viper' on sides, Silver & White stripes across top, Chrome Malaysia Base, w/RR5SP's '

See how the string is both delimited with single quotes, and it contains single quotes? Of course the parser encounters one of the contained quotes, expects the string is done, looks for a comma to start processing the next field, doesn't find one, fails.
There three things I can think to discuss.
1) Why do you have to so much manual insertion of data? An Access.Form has a RecordSource property that allows you, pretty much automatically, to bind table data to a form, and each Access.Control object has a ControlSource property that you can use to bind to a field in the Form's RecordSource, and in that scheme you don't need to delimit the data at all, and users can use delimiters in the data.

2) You can open a recordset and assign delimited data to a field without a problem.

3) You can create a Query that takes Parameters that will allow you to push delimited data into the INSERT statement.

Number 1) you can consider.

Number 2) works like this ...

Private Sub DoRecordsetInsert
[COLOR="Green"]'  Note how there is no worry of delimiters here[/COLOR]
   Dim rst As DAO.Recordset

   Set rst = CurrentDb.OpenRecordset("tblCollection")
   With rst
      .AddNew
      !ProdYear = Me.ProdYear
      !SKU = Me.SKU
      !Card = Me.Card
      ...
      !Description = Me.Description
      .Update
      .Close
   End With
End Sub

Number 3) works like this...

Private Sub DoQueryDefInsert
[COLOR="Green"]'  this creates a temp parameter query, and data type delimiters
'  are handled automatically by the query
[/COLOR]   dim qdf as dao.querydef

   set qdf = currentdb.createquerydef("", _
      "INSERT INTO tblCollection " & _
         "( ProdYear, SKU, Card, Car, Series, Description ) " & _
      "VALUES " & _
         "( p0, p1, p2, p3, p4, p5 )")
   wth qdf
      .parameters("p0") = Me.ProdYear
      .parameters("p1") = Me.SKU
      .parameters("p2") = Me.Card
      .parameters("p3") = Me.Car
      .parameters("p4") = Me.Series
      .parameters("p5") = Me.Description
      .execute
      .close
   end with
end sub

Hope that helps,

 

[KLahvic01]

I understand how the examples you provided work. The question is, can I use this code on a check box for an individual record from table A (search form) and move it to Table B (collection form).

This is a hot wheels collection database and I may have more than 1 of a specific car and they might be in different conditions and valued differently. I want to be able to add an individual car from the master hot wheels list and its standard attributes, (year, series, car name, description, etc) to my collection table and there be able to grade the vehicles condition using other parameters unique to that car which will help me to figure the actual value.

Maybe I am going about this the wrong way.