error 3265, item not found in collection issue (1 Viewer)

[thwy31]

Hi Guys, please help to have a look at the code below, keeps coming up
with error 3265, item not found in this collection.


Markets Query (the data source for the recordset)
------------------------------------------------
[/SQL]
SELECT a.State_ID, a.State_Name, b.LGA_ID, b.LGA_NAME, c.Market_ID, c.Market_Name, c.MarketDay_Interval, c.Last_Market_Day
FROM state AS a, LGA AS b, Markets AS c
WHERE (((a.State_ID)=b.State_ID And (a.State_ID)=28) And ((b.LGA_ID)=c.LGA_ID));
[SQL\]



------------------------------------
[/Code]

Public Sub testing2()

Dim wrkCurrent As DAO.Workspace
Dim dbsMarketDays As DAO.Database
Dim rstMarketDays As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim Date_Check
Dim MDI
Dim Ldy As Date
Dim Month_Part As String
Dim Day As String
Dim Jan_Days As Variant
Dim Feb_Days As Variant
Dim Mar_Days As Variant
Dim Apr_Days As Variant
Dim May_Days As Variant
Dim Jun_Days As Variant
Dim Jul_Days As Variant
Dim Aug_Days As Variant
Dim Sep_Days As Variant
Dim Oct_Days As Variant
Dim Nov_Days As Variant
Dim Dec_Days As Variant
Dim Interval_Type As String
Dim strSQL As String
Dim i As Integer
Interval_Type = "d"
Ldy = #12/31/2014#
i = 0

'Set wrkCurrent = DBEngine.Workspaces(0)
Set dbsMarketDays = CurrentDb
Set rstMarketDays = dbsMarketDays.OpenRecordset("Markets Query")

On Error GoTo ErrorHandler

'wrkCurrent.BeginTrans
With rstMarketDays

Do Until rstMarketDays.EOF
.MoveFirst
Date_Check = rstMarketDays!Last_Market_Day
MDI = rstMarketDays!MarketDay_Interval

'Do While i <= Reports!Market_Days_Report.Count


Jan_Days = ""
Feb_Days = ""
Mar_Days = ""
Apr_Days = ""
May_Days = ""
Jun_Days = ""
Jul_Days = ""
Aug_Days = ""
Sep_Days = ""
Oct_Days = ""
Nov_Days = ""
Dec_Days = ""


Do While Date_Check <= Ldy

Date_Check = DateAdd(Interval_Type, MDI, Date_Check)
If Date_Check > Ldy Then Exit Do

Month_Part = DatePart("m", Date_Check)
Day_Part = DatePart("d", Date_Check)

Select Case Month_Part

Case "1"

Jan_Days = Jan_Days & " , " & Day_Part

Case "2"
Feb_Days = Feb_Days & " , " & Day_Part

Case "3"
Mar_Days = Mar_Days & " , " & Day_Part

Case "4"
Apr_Days = Apr_Days & " , " & Day_Part

Case "5"
May_Days = May_Days & " , " & Day_Part

Case "6"
Jun_Days = Jun_Days & " , " & Day_Part

Case "7"
Jul_Days = Jul_Days & " , " & Day_Part

Case "8"
Aug_Days = Aug_Days & " , " & Day_Part

Case "9"
Sep_Days = Sep_Days & " , " & Day_Part

Case "10"
Oct_Days = Oct_Days & " , " & Day_Part

Case "11"
Nov_Days = Nov_Days & " , " & Day_Part

Case "12"
Dec_Days = Dec_Days & " , " & Day_Part

End Select

'Reports!Market_Days_Report.Controls!Jan.Value = Jan_Days
'Reports!Market_Days_Report.Controls!Feb.Value = Feb_Days
'Reports!Market_Days_Report.Controls!Mar.Value = Mar_Days
'Reports!Market_Days_Report.Controls!Apr.Value = Apr_Days
'Reports!Market_Days_Report.Controls!May.Value = May_Days
'Reports!Market_Days_Report.Controls!Jun.Value = Jun_Days
'Reports!Market_Days_Report.Controls!Jul.Value = Jul_Days
'Reports!Market_Days_Report.Controls!Aug.Value = Aug_Days
'Reports!Market_Days_Report.Controls!Sep.Value = Sep_Days
'Reports!Market_Days_Report.Controls!Oct.Value = Oct_Days
'Reports!Market_Days_Report.Controls!Nov.Value = Nov_Days
'Reports!Market_Days_Report.Controls!Dec.Value = Dec_Days

Loop

strSQL = "INSERT INTO Temp_Days VALUES (" & rstMarketDays.Fields(State_ID) & ", " & rstMarketDays.Fields(State_Name) & ", " & rstMarketDays.Fields(LGA_ID) & ", " & rstMarketDays.Fields(LGA_NAME) & ", " & rstMarketDays.Fields(Market_ID) & ", " & rstMarketDays.Fields(Market_Name) & ", " & rstMarketDays.Fields(MarketDay_Interval) & ", " & rstMarketDays.Fields(Last_Market_Day) & ", " & Jan_Days & ", " & Feb_Days & ", " & Mar_Days & ", " & Apr_Days & ", " & May_Days & ", " & Jun_Days & ", " & Jul_Days & ", " & Aug_Days & ", " & Sep_Days & ", " & Oct_Days & ", " & Nov_Days & ", " & Dec_Days & ");"

Set qdf = dbs.CreateQueryDef("", strSQL)

With qdf

qdf.Execute

End With

rstMarketDays.MoveNext

Loop

rstMarketDays.Close

End With

dbsMarketDays.Close
'wrkCurrent.Close

 

[Mihail]

Remove this line
On Error GoTo ErrorHandler
from your code.

Run the code again
Now, either you will be able to debug either you will know the exact point where the error raise.

If still yo are not able to debug, edit your first post and highlight the line with problems.

 

[Galaxiom]

With rstMarketDays

Do Until rstMarketDays.EOF
.MoveFirst
 
etc
rstMarketDays.MoveNext

Loop

Although it isn't the problem you are looking for, this code is an infinte loop.

 

[Galaxiom]

The problem you are chasing could be caused by this:

strSQL = "INSERT INTO Temp_Days VALUES (" & rstMarketDays.Fields(State_ID) & ", " etc

rstMarketDays.Fields(State_ID)

Unless State_ID is a variable representing a valid fieldname in rstMarketDays the error you named will be thrown. Likewise the rest of the references to the fields in the recordset.

Your SQL string also neglects to include the quote marks required as string delimiters on the values to be inserted in text fields.

The code is also extremly clumsy. Repeating groups of variables should be implimentd as arrays rather than a plethora of variables.

Finally posting it here without code tags destroys any formatting that might have made it easier to read.