Run-time error '3125' when running docmd.runsql

[dfeigen115]

When running the code (see below), I receive the following error (after the
msgbox displays):

Run-time error '3125':
" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.

The debug function highlights the docmd.runsql statement. Any assistance
would be appreciated (fyi, I'm running access 2003)

=====start code=====

Dim intCurrentRow As Integer
For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES
(Me.CACRNumber,[me.ExtendChargeNumber.Column(0,intCurrentRow)],Me.ExtendDate)"
End If
Next intCurrentRow

==========end code=========

Thanks in advance, Dan

 

[Douglas J Steele]

Put the values outside of quotes, or else Access is going to see them as
literal text strings:

DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES
(" & Me.CACRNumber & ", " & me.ExtendChargeNumber.Column(0,intCurrentRow) &
", " & Format(Me.ExtendDate, "\#mm\/dd\/yyyy\#") & ")"

This assumes that both CACRNumber and ExtendChargeNumber are numeric fields.
If they're text, you need to include quotes around the values:

DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES
('" & Me.CACRNumber & "', '" & me.ExtendChargeNumber.Column(0,intCurrentRow)
& "', " & Format(Me.ExtendDate, "\#mm\/dd\/yyyy\#") & ")"

Exagerated for clarity, that's

( ' " & Me.CACRNumber & " ' , ' " &
me.ExtendChargeNumber.Column(0,intCurrentRow) & " ' , ...

 

[Allen Browne]

Try concatenating the values into the string, and add the delimiters:

Dim strSql As String
strSql = "INSERT INTO ExtendChargeNumbers ([CACRNumber],
[ExtendChargeNumber], [ExtendDate]) VALUES (""" & Me.CACRNumber & """, """ &
me.ExtendChargeNumber.Column(0,intCurrentRow) & """, " &
Format(Me.ExtendDate, "\#mm/dd/yyyy\#") & ");"
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError

The Debug.Print line is just to help with debugging. If it fails, open the
Immediate Window (Ctrl+G) and see if you can see what's wrong with the
string that did not execute. You can even copy it and paste it into SQL View
of a new query to see what you need to get.

You can omit quotes if the fields are of type Number, not Text. Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If Execute is new, this might also help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[dfeigen115]

Doug, Allen;
Thanks to both for your suggestions/recommendations.
Dan

 

[dfeigen115]

Allen,
I've tried both variants offered by Doug and yourself, witht he same result,
ie run-time error 2465: database can't find the field '|' referred to in your
expression. The error is ocurring on the "strSQL = " statement. Look as I
might, I can't find the character it is flagging.

I did forget to note in the original post that the values of the 3 fields
being inserted are captured/entered on a form. The CACRNumber &
ExtendChargeNumber fields are defined as text as thay are a mix of
alphanumeric characters, the ExtendDate field is defined as a date (short
format). I've pasted the current version of the code here:

Dim intCurrentRow As Integer
Dim strSQL As String
strSQL = "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES (""" & Me.CACRNumber
& """,""" & [me.ExtendChargeNumber.Column(0,intCurrentRow)] & """,""" &
Me.ExtendDate & """);"

For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
'DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES (" & [Me.CACRNumber]
& "," & [me.ExtendChargeNumber.Column(0,intCurrentRow)] & "," &
[Me.ExtendDate] & ");"
Debug.Print strSQL
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If
Next intCurrentRow

Thanks again, Dan

Try concatenating the values into the string, and add the delimiters:

Dim strSql As String
strSql = "INSERT INTO ExtendChargeNumbers ([CACRNumber],
[ExtendChargeNumber], [ExtendDate]) VALUES (""" & Me.CACRNumber & """, """ &
me.ExtendChargeNumber.Column(0,intCurrentRow) & """, " &
Format(Me.ExtendDate, "\#mm/dd/yyyy\#") & ");"
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError

The Debug.Print line is just to help with debugging. If it fails, open the
Immediate Window (Ctrl+G) and see if you can see what's wrong with the
string that did not execute. You can even copy it and paste it into SQL View
of a new query to see what you need to get.

You can omit quotes if the fields are of type Number, not Text. Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If Execute is new, this might also help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

When running the code (see below), I receive the following error (after
the
msgbox displays):

Run-time error '3125':
" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.

The debug function highlights the docmd.runsql statement. Any assistance
would be appreciated (fyi, I'm running access 2003)

=====start code=====

Dim intCurrentRow As Integer
For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES
(Me.CACRNumber,[me.ExtendChargeNumber.Column(0,intCurrentRow)],Me.ExtendDate)"
End If
Next intCurrentRow

==========end code=========

Thanks in advance, Dan

 

[Allen Browne]

Did you open the Immediate Window (Ctrl+G), copy the SQL statement that
fails, and see if you can make sense out of that?

Mock up a query using any literal values as examples (typed into the Field
row.) Turn it into an Append query (Append on Query menu.) Then switch to
SQL View (View menu), and compare what it has with your Debug.Print'd
statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I've tried both variants offered by Doug and yourself, witht he same
result,
ie run-time error 2465: database can't find the field '|' referred to in
your
expression. The error is ocurring on the "strSQL = " statement. Look as
I
might, I can't find the character it is flagging.

I did forget to note in the original post that the values of the 3 fields
being inserted are captured/entered on a form. The CACRNumber &
ExtendChargeNumber fields are defined as text as thay are a mix of
alphanumeric characters, the ExtendDate field is defined as a date (short
format). I've pasted the current version of the code here:

Dim intCurrentRow As Integer
Dim strSQL As String
strSQL = "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES (""" &
Me.CACRNumber
& """,""" & [me.ExtendChargeNumber.Column(0,intCurrentRow)] & """,""" &
Me.ExtendDate & """);"

For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
'DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES (" &
[Me.CACRNumber]
& "," & [me.ExtendChargeNumber.Column(0,intCurrentRow)] & "," &
[Me.ExtendDate] & ");"
Debug.Print strSQL
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If
Next intCurrentRow

Thanks again, Dan

Try concatenating the values into the string, and add the delimiters:

Dim strSql As String
strSql = "INSERT INTO ExtendChargeNumbers ([CACRNumber],
[ExtendChargeNumber], [ExtendDate]) VALUES (""" & Me.CACRNumber & """,
""" &
me.ExtendChargeNumber.Column(0,intCurrentRow) & """, " &
Format(Me.ExtendDate, "\#mm/dd/yyyy\#") & ");"
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError

The Debug.Print line is just to help with debugging. If it fails, open
the
Immediate Window (Ctrl+G) and see if you can see what's wrong with the
string that did not execute. You can even copy it and paste it into SQL
View
of a new query to see what you need to get.

You can omit quotes if the fields are of type Number, not Text.
Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If Execute is new, this might also help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

When running the code (see below), I receive the following error (after
the
msgbox displays):

Run-time error '3125':
" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.

The debug function highlights the docmd.runsql statement. Any
assistance
would be appreciated (fyi, I'm running access 2003)

=====start code=====

Dim intCurrentRow As Integer
For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES
(Me.CACRNumber,[me.ExtendChargeNumber.Column(0,intCurrentRow)],Me.ExtendDate)"
End If
Next intCurrentRow

==========end code=========

 

[Douglas J Steele]

As Allen suggests, print the value of strSQL to the immediate window and see
if it looks correct.

However, one error you've made is that you're enclosing the date in quotes.
See my original response for how you have to delimit dates.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Allen,
I've tried both variants offered by Doug and yourself, witht he same result,
ie run-time error 2465: database can't find the field '|' referred to in your
expression. The error is ocurring on the "strSQL = " statement. Look as I
might, I can't find the character it is flagging.

I did forget to note in the original post that the values of the 3 fields
being inserted are captured/entered on a form. The CACRNumber &
ExtendChargeNumber fields are defined as text as thay are a mix of
alphanumeric characters, the ExtendDate field is defined as a date (short
format). I've pasted the current version of the code here:

Dim intCurrentRow As Integer
Dim strSQL As String
strSQL = "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES (""" & Me.CACRNumber
& """,""" & [me.ExtendChargeNumber.Column(0,intCurrentRow)] & """,""" &
Me.ExtendDate & """);"

For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
'DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES (" & [Me.CACRNumber]
& "," & [me.ExtendChargeNumber.Column(0,intCurrentRow)] & "," &
[Me.ExtendDate] & ");"
Debug.Print strSQL
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If
Next intCurrentRow

Thanks again, Dan

Try concatenating the values into the string, and add the delimiters:

Dim strSql As String
strSql = "INSERT INTO ExtendChargeNumbers ([CACRNumber],
[ExtendChargeNumber], [ExtendDate]) VALUES (""" & Me.CACRNumber & """, """ &
me.ExtendChargeNumber.Column(0,intCurrentRow) & """, " &
Format(Me.ExtendDate, "\#mm/dd/yyyy\#") & ");"
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError

The Debug.Print line is just to help with debugging. If it fails, open the
Immediate Window (Ctrl+G) and see if you can see what's wrong with the
string that did not execute. You can even copy it and paste it into SQL View
of a new query to see what you need to get.

You can omit quotes if the fields are of type Number, not Text. Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If Execute is new, this might also help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

When running the code (see below), I receive the following error (after
the
msgbox displays):

Run-time error '3125':
" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.

The debug function highlights the docmd.runsql statement. Any assistance
would be appreciated (fyi, I'm running access 2003)

=====start code=====

Dim intCurrentRow As Integer
For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES

(Me.CACRNumber,[me.ExtendChargeNumber.Column(0,intCurrentRow)],Me.ExtendDate
)"

 

[dfeigen115]

Allen, Doug;

It's working (probably should have cut/pasted form your original responses
vs rekeying)...thanks to both of you for your quick responses to my issue.

Dan

As Allen suggests, print the value of strSQL to the immediate window and see
if it looks correct.

However, one error you've made is that you're enclosing the date in quotes.
See my original response for how you have to delimit dates.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Allen,
I've tried both variants offered by Doug and yourself, witht he same result,
ie run-time error 2465: database can't find the field '|' referred to in your
expression. The error is ocurring on the "strSQL = " statement. Look as I
might, I can't find the character it is flagging.

I did forget to note in the original post that the values of the 3 fields
being inserted are captured/entered on a form. The CACRNumber &
ExtendChargeNumber fields are defined as text as thay are a mix of
alphanumeric characters, the ExtendDate field is defined as a date (short
format). I've pasted the current version of the code here:

Dim intCurrentRow As Integer
Dim strSQL As String
strSQL = "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES (""" & Me.CACRNumber
& """,""" & [me.ExtendChargeNumber.Column(0,intCurrentRow)] & """,""" &
Me.ExtendDate & """);"

For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
'DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES (" & [Me.CACRNumber]
& "," & [me.ExtendChargeNumber.Column(0,intCurrentRow)] & "," &
[Me.ExtendDate] & ");"
Debug.Print strSQL
DBEngine(0)(0).Execute strSQL, dbFailOnError
End If
Next intCurrentRow

Thanks again, Dan

Try concatenating the values into the string, and add the delimiters:

Dim strSql As String
strSql = "INSERT INTO ExtendChargeNumbers ([CACRNumber],
[ExtendChargeNumber], [ExtendDate]) VALUES (""" & Me.CACRNumber & """, """ &
me.ExtendChargeNumber.Column(0,intCurrentRow) & """, " &
Format(Me.ExtendDate, "\#mm/dd/yyyy\#") & ");"
Debug.Print strSql
dbEngine(0)(0).Execute strSql, dbFailOnError

The Debug.Print line is just to help with debugging. If it fails, open the
Immediate Window (Ctrl+G) and see if you can see what's wrong with the
string that did not execute. You can even copy it and paste it into SQL View
of a new query to see what you need to get.

You can omit quotes if the fields are of type Number, not Text. Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If Execute is new, this might also help:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

When running the code (see below), I receive the following error (after
the
msgbox displays):

Run-time error '3125':
" is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.

The debug function highlights the docmd.runsql statement. Any assistance
would be appreciated (fyi, I'm running access 2003)

=====start code=====

Dim intCurrentRow As Integer
For intCurrentRow = 0 To Me.ExtendChargeNumber.ListCount - 1
If Me.ExtendChargeNumber.Selected(intCurrentRow) Then
MsgBox (Me.CACRNumber & " " & Me.ExtendChargeNumber.Column(0,
intCurrentRow) & " " & Me.ExtendDate)
DoCmd.RunSQL "INSERT INTO ExtendChargeNumbers
([CACRNumber],[ExtendChargeNumber],[ExtendDate]) VALUES

(Me.CACRNumber,[me.ExtendChargeNumber.Column(0,intCurrentRow)],Me.ExtendDate
)"

End If
Next intCurrentRow

==========end code=========

Thanks in advance, Dan