DoCmd.RunSQL - Help

[DONNA]

The value I need inserted into FIELD is: TRS*T3*105*081031

The value that is inserted into FIELD after running the code:
TFS*T3*"105"*"081031 (notice the extra quote marks)

Also receiving the message: Invalid use of Null

What am I doing wrong? Thanks for your help.

THE CODE:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim LineCount As Variant
Dim tmpReferenceNumber As String
Dim tmpDate As String
Dim linedata As String
Dim linedata1 As String

DoCmd.OpenQuery "Clear Formated Detail Information"

LineCount = 2
Set dbCurrent = CurrentDb

strSQL = "SELECT * FROM [Detail Table];"
Set rsTemp = dbCurrent.OpenRecordset(strSQL)
If rsTemp.RecordCount > 0 Then
Do Until rsTemp.EOF
tmpReferenceNumber = rsTemp.Fields(0)
tmpDate = rsTemp.Fields(1)

linedata = "TFS*T3*"
linedata1 = "*"
DoCmd.RunSQL "INSERT INTO [Formated Detail Information](FIELD)
SELECT (""" & linedata & """""" & tmpReferenceNumber & """""" & linedata1 &
"""""" & tmpDate & """) AS FIELD;"
LineCount = LineCount + 1
rsTemp.MoveNext

Loop

End If

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

 

[Douglas J. Steele]

The quotes are likely coming from the multiple double-quotes you've got.

Try:

DoCmd.RunSQL "INSERT INTO [Formated Detail Information](FIELD) " & _
"SELECT (""" & linedata & tmpReferenceNumber & linedata1 & tmpDate &
""") AS FIELD;"

 

[DONNA]

Thanks! That solved the problem. I still receive the following message
after the insert is complete: Invalid use of Null.

Any idea what is causing this message?

The quotes are likely coming from the multiple double-quotes you've got.

Try:

DoCmd.RunSQL "INSERT INTO [Formated Detail Information](FIELD) " & _
"SELECT (""" & linedata & tmpReferenceNumber & linedata1 & tmpDate &
""") AS FIELD;"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The value I need inserted into FIELD is: TRS*T3*105*081031

The value that is inserted into FIELD after running the code:
TFS*T3*"105"*"081031 (notice the extra quote marks)

Also receiving the message: Invalid use of Null

What am I doing wrong? Thanks for your help.

THE CODE:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim LineCount As Variant
Dim tmpReferenceNumber As String
Dim tmpDate As String
Dim linedata As String
Dim linedata1 As String

DoCmd.OpenQuery "Clear Formated Detail Information"

LineCount = 2
Set dbCurrent = CurrentDb

strSQL = "SELECT * FROM [Detail Table];"
Set rsTemp = dbCurrent.OpenRecordset(strSQL)
If rsTemp.RecordCount > 0 Then
Do Until rsTemp.EOF
tmpReferenceNumber = rsTemp.Fields(0)
tmpDate = rsTemp.Fields(1)

linedata = "TFS*T3*"
linedata1 = "*"
DoCmd.RunSQL "INSERT INTO [Formated Detail Information](FIELD)
SELECT (""" & linedata & """""" & tmpReferenceNumber & """""" & linedata1
&
"""""" & tmpDate & """) AS FIELD;"
LineCount = LineCount + 1
rsTemp.MoveNext

Loop

End If

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

 

[Douglas J. Steele]

Does table [Formated Detail Information] have other fields in it that are
marked as Required?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks! That solved the problem. I still receive the following message
after the insert is complete: Invalid use of Null.

Any idea what is causing this message?

The quotes are likely coming from the multiple double-quotes you've got.

Try:

DoCmd.RunSQL "INSERT INTO [Formated Detail Information](FIELD) " & _
"SELECT (""" & linedata & tmpReferenceNumber & linedata1 & tmpDate &
""") AS FIELD;"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The value I need inserted into FIELD is: TRS*T3*105*081031

The value that is inserted into FIELD after running the code:
TFS*T3*"105"*"081031 (notice the extra quote marks)

Also receiving the message: Invalid use of Null

What am I doing wrong? Thanks for your help.

THE CODE:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim LineCount As Variant
Dim tmpReferenceNumber As String
Dim tmpDate As String
Dim linedata As String
Dim linedata1 As String

DoCmd.OpenQuery "Clear Formated Detail Information"

LineCount = 2
Set dbCurrent = CurrentDb

strSQL = "SELECT * FROM [Detail Table];"
Set rsTemp = dbCurrent.OpenRecordset(strSQL)
If rsTemp.RecordCount > 0 Then
Do Until rsTemp.EOF
tmpReferenceNumber = rsTemp.Fields(0)
tmpDate = rsTemp.Fields(1)

linedata = "TFS*T3*"
linedata1 = "*"
DoCmd.RunSQL "INSERT INTO [Formated Detail
Information](FIELD)
SELECT (""" & linedata & """""" & tmpReferenceNumber & """""" &
linedata1
&
"""""" & tmpDate & """) AS FIELD;"
LineCount = LineCount + 1
rsTemp.MoveNext

Loop

End If

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

 

[DONNA]

No. [Formated Detail Information] contains only 1 field called FIELD.

Thanks,

Does table [Formated Detail Information] have other fields in it that are
marked as Required?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks! That solved the problem. I still receive the following message
after the insert is complete: Invalid use of Null.

Any idea what is causing this message?

The quotes are likely coming from the multiple double-quotes you've got.

Try:

DoCmd.RunSQL "INSERT INTO [Formated Detail Information](FIELD) " & _
"SELECT (""" & linedata & tmpReferenceNumber & linedata1 & tmpDate &
""") AS FIELD;"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The value I need inserted into FIELD is: TRS*T3*105*081031

The value that is inserted into FIELD after running the code:
TFS*T3*"105"*"081031 (notice the extra quote marks)

Also receiving the message: Invalid use of Null

What am I doing wrong? Thanks for your help.

THE CODE:

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim LineCount As Variant
Dim tmpReferenceNumber As String
Dim tmpDate As String
Dim linedata As String
Dim linedata1 As String

DoCmd.OpenQuery "Clear Formated Detail Information"

LineCount = 2
Set dbCurrent = CurrentDb

strSQL = "SELECT * FROM [Detail Table];"
Set rsTemp = dbCurrent.OpenRecordset(strSQL)
If rsTemp.RecordCount > 0 Then
Do Until rsTemp.EOF
tmpReferenceNumber = rsTemp.Fields(0)
tmpDate = rsTemp.Fields(1)

linedata = "TFS*T3*"
linedata1 = "*"
DoCmd.RunSQL "INSERT INTO [Formated Detail
Information](FIELD)
SELECT (""" & linedata & """""" & tmpReferenceNumber & """""" &
linedata1
&
"""""" & tmpDate & """) AS FIELD;"
LineCount = LineCount + 1
rsTemp.MoveNext

Loop

End If

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

 

[Douglas J. Steele]

Don't know why I didn't notice this before.

While you can use INSERT INTO .... SELECT, you may be better off in this
case using INSERT INTO...VALUES:

DoCmd.RunSQL "INSERT INTO [Formated Detail Information](FIELD) " & _
"VALUES (""" & linedata & tmpReferenceNumber & linedata1 & tmpDate &
""")"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

No. [Formated Detail Information] contains only 1 field called FIELD.

Thanks,

Does table [Formated Detail Information] have other fields in it that are
marked as Required?

 

[Douglas J. Steele]

Hmm. Not doing that well today!

A bigger problem is that Field is a reserved word, and you should never use
reserved words for your own purposes. (For a comprehensive list of names of
avoid, plus a link to a utility that will check your application for you,
see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html )

If you cannot (or will not) rename the field, at least put square brackets
around it:

DoCmd.RunSQL "INSERT INTO [Formated Detail Information]([FIELD]) " & _
"VALUES (""" & linedata & tmpReferenceNumber & linedata1 & tmpDate & """)"




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Don't know why I didn't notice this before.

While you can use INSERT INTO .... SELECT, you may be better off in this
case using INSERT INTO...VALUES:

DoCmd.RunSQL "INSERT INTO [Formated Detail Information](FIELD) " & _
"VALUES (""" & linedata & tmpReferenceNumber & linedata1 & tmpDate &
""")"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

No. [Formated Detail Information] contains only 1 field called FIELD.

Thanks,

Does table [Formated Detail Information] have other fields in it that
are
marked as Required?