DoCmd.RunSQL - Help

D

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
 
D

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;"
 
D

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?


Douglas J. Steele said:
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!)


DONNA said:
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
Click to expand...
Click to expand...
 
D

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!)


DONNA said:
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?


Douglas J. Steele said:
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!)


DONNA said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

DONNA

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

Thanks,

Douglas J. Steele said:
Does table [Formated Detail Information] have other fields in it that are
marked as Required?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


DONNA said:
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?


Douglas J. Steele said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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!)


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

Thanks,

Douglas J. Steele said:
Does table [Formated Detail Information] have other fields in it that are
marked as Required?
Click to expand...
Click to expand...
 
D

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!)


Douglas J. Steele said:
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!)


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

Thanks,

Douglas J. Steele said:
Does table [Formated Detail Information] have other fields in it that
are
marked as Required?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Open AARRGGGs Help please 7
Access code error - Too few parametres. Expect 1. 7
Insert into loop not working 3
Help with Code 1
DoCmd.RunSQL 1
Need help combining related records 3
Help With SQL Code in sub 5
Lost Focus Event Need Help 1

Top