Create TBL SQL error   

In the following code, I get an 'Item not found in this collection' error.
When I debug this error it points me to the SQL code. I am not sure what
specifically is causing the error. I have copied the code from SQL view and
pasted it into my overall code and added the 'rst' criteria filter code.
Thanks for the help.

Private Sub Update_Click()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dbs As DAO.Database

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Definitions-DC List")

DoCmd.OpenQuery "1a-Del Temp Sales Penetation Tbl"


With rst
        Do Until rst.EOF = True
   
        strSQL = " SELECT [Temp Product Essbase].[Fiscal Month Abbreviation],
[Temp Product Essbase].[Fiscal Month], [Temp Product Essbase].[Product
Hierarchy], [Temp Product Essbase].[Product Hierarchy Description], [Temp
Product Essbase].[Financial Metrics], [Temp Product Essbase].[Financial
Metrics Description], [Temp Product Essbase].TY AS [DC TY], [Temp Product
Essbase].LY AS [DC LY], [Temp Product Essbase].LLY AS [DC LLY], [Temp Product
Essbase].[Plan] AS [DC  Plan] INTO [1b-Demand Center Sales $ Seperation Tbl]"
& _
                   " FROM [Temp Product Essbase]" & _
                   " WHERE [Temp Product Essbase].[Financial Metrics
Description] = ""Sales $"" And [Temp Product Essbase].TY > ""0"" And [Product
Hierarchy] = " & rst![Demand Center] & """"""""
                   
        DoCmd.RunSQL strSQL

DoCmd.OpenQuery "1e-Append Sales Penetration to Temp Tbl"

Loop

DoCmd.OpenQuery "1f-Update-Sales Penetration Name"
End With

End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200807/1

date: Tue, 22 Jul 2008 19:49:32 GMT   author:   mps731 via AccessMonster.com u44852@uwe

Re: Create TBL SQL error   

Have you checked whether
field  [Demand Center]  is present in table/query "Definitions-DC List" ??
(note: check for typo's as well)

I've checked yr other criterions and they appear to be ok, apart from the 
above mentioned field.

Krgrds,
Perry

"mps731 via AccessMonster.com" <u44852@uwe> schreef in bericht 
news:878ae4671b064@uwe...
> In the following code, I get an 'Item not found in this collection' error.
> When I debug this error it points me to the SQL code. I am not sure what
> specifically is causing the error. I have copied the code from SQL view 
> and
> pasted it into my overall code and added the 'rst' criteria filter code.
> Thanks for the help.
>
> Private Sub Update_Click()
> Dim rst As DAO.Recordset
> Dim strSQL As String
> Dim dbs As DAO.Database
>
> Set dbs = CurrentDb()
> Set rst = dbs.OpenRecordset("Definitions-DC List")
>
> DoCmd.OpenQuery "1a-Del Temp Sales Penetation Tbl"
>
>
> With rst
>        Do Until rst.EOF = True
>
>        strSQL = " SELECT [Temp Product Essbase].[Fiscal Month 
> Abbreviation],
> [Temp Product Essbase].[Fiscal Month], [Temp Product Essbase].[Product
> Hierarchy], [Temp Product Essbase].[Product Hierarchy Description], [Temp
> Product Essbase].[Financial Metrics], [Temp Product Essbase].[Financial
> Metrics Description], [Temp Product Essbase].TY AS [DC TY], [Temp Product
> Essbase].LY AS [DC LY], [Temp Product Essbase].LLY AS [DC LLY], [Temp 
> Product
> Essbase].[Plan] AS [DC  Plan] INTO [1b-Demand Center Sales $ Seperation 
> Tbl]"
> & _
>                   " FROM [Temp Product Essbase]" & _
>                   " WHERE [Temp Product Essbase].[Financial Metrics
> Description] = ""Sales $"" And [Temp Product Essbase].TY > ""0"" And 
> [Product
> Hierarchy] = " & rst![Demand Center] & """"""""
>
>        DoCmd.RunSQL strSQL
>
> DoCmd.OpenQuery "1e-Append Sales Penetration to Temp Tbl"
>
> Loop
>
> DoCmd.OpenQuery "1f-Update-Sales Penetration Name"
> End With
>
> End Sub
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200807/1
>

date: Tue, 22 Jul 2008 22:33:34 +0200   author:   perry