DoCmd.RunSQL - Help [WiredBox.Net - Office Newsgroups]

Group: Microsoft Access » microsoft.public.access.formscoding
Thread: DoCmd.RunSQL - Help

DoCmd.RunSQL - Help

DONNA 11/10/2008 4:03:01 PM

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

Re: DoCmd.RunSQL - Help

"Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/10/2008 4:20:40 PM

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
http://I.Am/DougSteele
(no e-mails, please!)

"DONNA" <DONNA[ at ]discussions.microsoft.com> wrote in message
news:07F48637-7402-4405-A801-946F952EC577[ at ]microsoft.com...
[Quoted Text]
>
> 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
>

Re: DoCmd.RunSQL - Help

DONNA 11/10/2008 4:31:03 PM

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" wrote:

[Quoted Text]
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "DONNA" <DONNA[ at ]discussions.microsoft.com> wrote in message
> news:07F48637-7402-4405-A801-946F952EC577[ at ]microsoft.com...
> >
> > 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
> >
>
>
>

Re: DoCmd.RunSQL - Help

"Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/10/2008 4:44:35 PM

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"DONNA" <DONNA[ at ]discussions.microsoft.com> wrote in message
news:567B3030-ADE3-47EB-8679-737C5F7B96E0[ at ]microsoft.com...
[Quoted Text]
> 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" wrote:
>
>> 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
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "DONNA" <DONNA[ at ]discussions.microsoft.com> wrote in message
>> news:07F48637-7402-4405-A801-946F952EC577[ at ]microsoft.com...
>> >
>> > 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
>> >
>>
>>
>>

Re: DoCmd.RunSQL - Help

DONNA 11/10/2008 4:53:01 PM

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

Thanks,

"Douglas J. Steele" wrote:

[Quoted Text]
> Does table [Formated Detail Information] have other fields in it that are
> marked as Required?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "DONNA" <DONNA[ at ]discussions.microsoft.com> wrote in message
> news:567B3030-ADE3-47EB-8679-737C5F7B96E0[ at ]microsoft.com...
> > 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" wrote:
> >
> >> 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
> >> http://I.Am/DougSteele
> >> (no e-mails, please!)
> >>
> >>
> >> "DONNA" <DONNA[ at ]discussions.microsoft.com> wrote in message
> >> news:07F48637-7402-4405-A801-946F952EC577[ at ]microsoft.com...
> >> >
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>

Re: DoCmd.RunSQL - Help

"Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/10/2008 5:24:45 PM

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
http://I.Am/DougSteele
(no e-mails, please!)

"DONNA" <DONNA[ at ]discussions.microsoft.com> wrote in message
news:06C45CA2-E119-46B6-9D62-020DACC98FC1[ at ]microsoft.com...
[Quoted Text]
> No. [Formated Detail Information] contains only 1 field called FIELD.
>
> Thanks,
>
> "Douglas J. Steele" wrote:
>
>> Does table [Formated Detail Information] have other fields in it that are
>> marked as Required?
>>

Re: DoCmd.RunSQL - Help

"Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/10/2008 5:27:41 PM

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
http://I.Am/DougSteele
(no e-mails, please!)

"Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote in message
news:esEl6k1QJHA.4008[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text]
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "DONNA" <DONNA[ at ]discussions.microsoft.com> wrote in message
> news:06C45CA2-E119-46B6-9D62-020DACC98FC1[ at ]microsoft.com...
>> No. [Formated Detail Information] contains only 1 field called FIELD.
>>
>> Thanks,
>>
>> "Douglas J. Steele" wrote:
>>
>>> Does table [Formated Detail Information] have other fields in it that
>>> are
>>> marked as Required?
>>>
>
>

Home | Search | Terms | Imprint Contact

Newsgroups Reader - provided by WiredBox.Net