Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Run - time error 3625: item not found in this collection

  1. #1
    New Member
    Join Date
    Feb 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Run - time error 3625: item not found in this collection

    Hi Guys,

    First time poster on these forums, unfortunately I don't have a great deal of VBA experience.

    I've basically just took over a project from somebody and I'm looking to add a field called "Change" into the database. The database then populates an Excel spreadsheet which I want the new column to be added into column "W".

    I've amended the VB code as below (The relevant lines are underlined and in italics) and added the field "Change" into a table called AL Override.

    However when I run the macro to populate the spreadsheet it throws up "Run-time error 3625: Item not found in this collection".

    Code:
    Private Function GenerateTREADataSheet(WEA)
    Dim xlApp As New Excel.Application
        Dim xlWrkBk As Excel.Workbook
        Dim xlSht, xlShtOOS As Excel.Worksheet
        Dim myRec As DAO.Recordset
        Dim rowNo, rowNoOOS As Integer 'stores the row number to write data to
        Dim password As String
        Dim DomainStart As String 'stores the start of the domain name
        rowNo = 4 ' Data row for incident data enty
        rowNoOOS = 5 ' Data row for incident data enty
        Dim SQLStart, SQLEnd As String
        Dim WEAStart As Date ' stores the start date of the WEA for SQL query
        Dim WEAEnd As Date ' stores the end date of the WEA for SQL query
        Dim qdf, qdfSL9 As QueryDef
        
        'Obtain the Query to Use
        Set qdf = CurrentDb.QueryDefs("TREA TLA Performance Report Query")
    '''''''''''''''''''''
        ' Get the WEA dates
        '''''''''''''''''''''
        Set WEARec = CurrentDb.OpenRecordset("WEAs")
         WEARec.MoveFirst
         With WEARec
          Do While Not .EOF
            If CInt(WEARec.Fields("WEA #").Value) = CInt(WEAName.Column(1)) Then
                WEAStart = WEARec.Fields("WEA Start Date") ' Start Date
                WEAEnd = WEARec.Fields("WEA End Date") ' End Date
                Exit Do
            Else
                .MoveNext
            End If
          Loop
          End With
        Set xlWrkBk = xlApp.Workbooks.Open(Application.CurrentProject.Path & "\Template\Incident Data v4.0 2007.xlsx")
        Set xlSht = xlWrkBk.Worksheets("Raw Incident TLA Data")
        Set xlShtConfig = xlWrkBk.Worksheets("Config")
        Set xlShtOOS = xlWrkBk.Worksheets("SL4-7  Out of Scope Incidents")
        Set xlshtReport = xlWrkBk.Worksheets("TLA Data")
        Set xlShtSL9 = xlWrkBk.Worksheets("SL9 - Availability")
        Set xlShtSL8 = xlWrkBk.Worksheets("SL8 - Aged Incidents")
        
        xlApp.Application.DisplayAlerts = False
        xlApp.Application.ScreenUpdating = True
        
        'get the password for the sheet
         password = xlShtConfig.Cells(2, "C")
         
        ' Show spreadsheet on screen
        xlApp.Application.Visible = True
        'xlApp.Parent.Windows(0).Visible = True
        'xlWrkBk.Application.Visible = True
        
        xlSht.Unprotect password
        'Clear the template fields to ensure no hangover from last report
         xlSht.Range("B4", "F1000") = ""
         xlSht.Range("H4", "J1000") = ""
         xlSht.Range("L4", "M1000") = ""
         xlSht.Range("O4", "Q1000") = ""
         
      
         'Set the WEA
         xlshtReport.Unprotect password
         xlshtReport.Cells(1, "B") = WEA
         xlshtReport.Protect password:=password
         
         ''''''''''''''''''''
         'Start Update the TLA Times
         ''''''''''''''''''''
         
         
         'unprotect the sheet
         xlShtConfig.Unprotect password:=password
         
         'Get data from Database to calculate times
         Set appRec = CurrentDb.OpenRecordset("Applications")
         appRec.MoveFirst
         With appRec
          Do While Not .EOF
            If appRec.Fields("appName").Value = "TREA" Then
                xlShtConfig.Cells(7, "C") = 19 ' Sev 2 - 19 hours
                xlShtConfig.Cells(8, "C") = 2.5 * appRec.Fields("ServiceDay") ' Sev 3 - 3 Service Says
                xlShtConfig.Cells(9, "C") = 6 * appRec.Fields("ServiceDay") ' Sev 4 - 7 Service Days
                
                Exit Do
            Else
                .MoveNext
            End If
          Loop
          End With
         'Protect the sheet
          xlShtConfig.Protect password:=password
        
        ''''''''''''''''''''
         'End Update the TLA Times
         ''''''''''''''''''''
         
         'calculate start and end dates
        qdf.Parameters("WEA Start") = Format(WEAStart, "dd/mm/yyyy")
        qdf.Parameters("WEA End") = Format(WEAEnd, "dd/mm/yyyy")
        
        Set myRec = qdf.OpenRecordset
        
        txtSQL.Value = qdf.SQL
        
        If myRec.RecordCount <> 0 Then
             myRec.MoveFirst
             
             With myRec
               Do While Not .EOF
               
                  If ((IsNull(myRec.Fields("OOS")) Or myRec.Fields("OOS") = False)) Then
                    xlSht.Cells(rowNo, "B").FormulaR1C1 = myRec.Fields("Inc No") 'Incident Number
                    xlSht.Cells(rowNo, "C").FormulaR1C1 = myRec.Fields("App") 'App
                    xlSht.Cells(rowNo, "D").FormulaR1C1 = myRec.Fields("First Severity") 'Original Severity
                    xlSht.Cells(rowNo, "E").FormulaR1C1 = myRec.Fields("Severity") ' Severity at closure
                    xlSht.Cells(rowNo, "F").FormulaR1C1 = myRec.Fields("Tower Start") ' Date
                    xlSht.Cells(rowNo, "H").FormulaR1C1 = myRec.Fields("Brief Description") ' Headline
                    xlSht.Cells(rowNo, "I").FormulaR1C1 = myRec.Fields("Closure Tower") ' Closure Tower
                    xlSht.Cells(rowNo, "J").FormulaR1C1 = myRec.Fields("Date Closed") ' Closure Date
                    
                    xlSht.Cells(rowNo, "L").FormulaR1C1 = myRec.Fields("hours") ' Hours in Domain
                    xlSht.Cells(rowNo, "M").FormulaR1C1 = myRec.Fields("infonaf_hours") ' Hours in Domain
                    xlSht.Cells(rowNo, "O").FormulaR1C1 = myRec.Fields("Pass") ' TLA
                    xlSht.Cells(rowNo, "W").FormulaR1C1 = myRec.Fields("Change") ' Pass/Fail Change
                    
                    'Add tests for the overide values
                    
                    If (myRec.Fields("OR Result") <> "") Then
                      xlSht.Cells(rowNo, "P").FormulaR1C1 = myRec.Fields("OR Result") ' TLA
                      xlSht.Cells(rowNo, "L").FormulaR1C1 = myRec.Fields("orHours") + myRec.Fields("infonaf_hours") ' Hours in Domain plus infonaf
                      xlSht.Cells(rowNo, "M").FormulaR1C1 = myRec.Fields("infonaf_hours") ' Hours in Domain INFONAF
                      xlSht.Cells(rowNo, "Q").FormulaR1C1 = myRec.Fields("Reason") ' override reason
                      xlSht.Cells(rowNo, "W").FormulaR1C1 = myRec.Fields("Change") ' Pass/Fail Change
                      
                    Else
                      xlSht.Cells(rowNo, "P").FormulaR1C1 = myRec.Fields("Pass") ' TLA
                    End If
                    rowNo = rowNo + 1
                    
                  Else
                  'populate the Out of scope tab
                    xlShtOOS.Cells(rowNoOOS, "B").FormulaR1C1 = myRec.Fields("Inc No") 'Incident Number
                    xlShtOOS.Cells(rowNoOOS, "C").FormulaR1C1 = myRec.Fields("App") 'App
                    xlShtOOS.Cells(rowNoOOS, "D").FormulaR1C1 = myRec.Fields("Severity") ' Severity at closure
                    xlShtOOS.Cells(rowNoOOS, "E").FormulaR1C1 = myRec.Fields("Tower Start") ' Date
                    xlShtOOS.Cells(rowNoOOS, "F").FormulaR1C1 = myRec.Fields("Brief Description") ' Headline
                    xlShtOOS.Cells(rowNoOOS, "G").FormulaR1C1 = myRec.Fields("Closure Tower") ' Closure Tower
                    xlShtOOS.Cells(rowNoOOS, "H").FormulaR1C1 = myRec.Fields("Date Closed") ' Closure Date
                    xlShtOOS.Cells(rowNoOOS, "I").FormulaR1C1 = myRec.Fields("Reason") ' Reason
                    rowNoOOS = rowNoOOS + 1
                    
                  End If
                  
                  .MoveNext
                  
               Loop
            End With
        End If
        Set qdfSL9 = CurrentDb.QueryDefs("TREA SL9 Stats")
        'calculate start and end dates
        qdfSL9.Parameters("Week Start") = Format(WEAStart, "dd/mm/yyyy")
        qdfSL9.Parameters("Week End") = Format(WEAEnd, "dd/mm/yyyy")
        
        Set myRec = qdfSL9.OpenRecordset
        rowNo = 5
        If myRec.RecordCount <> 0 Then
             myRec.MoveFirst
             
             With myRec
               Do While Not .EOF
               
                  'populate the SL9 tab
                    xlShtSL9.Cells(rowNo, "B").FormulaR1C1 = myRec.Fields("Inc Ref") 'Incident Number
                    xlShtSL9.Cells(rowNo, "C").FormulaR1C1 = myRec.Fields("Business Unit") ' Application
                    xlShtSL9.Cells(rowNo, "D").FormulaR1C1 = myRec.Fields("Start Date & Time") ' Start Date
                    xlShtSL9.Cells(rowNo, "E").FormulaR1C1 = myRec.Fields("Resolution Date & Time") ' Headline
                    xlShtSL9.Cells(rowNo, "F").FormulaR1C1 = myRec.Fields("DownTime") ' Downtime
                   
                    rowNo = rowNo + 1
                         
                  .MoveNext
                  
               Loop
            End With
        End If
        
        rowNo = 5
        'Update SL8 Stats
        Set Sl8Rec = CurrentDb.OpenRecordset("TREA SL8 Stats")
         Sl8Rec.MoveFirst
         With Sl8Rec
          Do While Not .EOF
                xlShtSL8.Cells(rowNo, "B") = Sl8Rec.Fields("Inc No") ' Incident number
                xlShtSL8.Cells(rowNo, "C") = Sl8Rec.Fields("Severity") ' Severity
                xlShtSL8.Cells(rowNo, "D") = Sl8Rec.Fields("Incident Open") ' Date Raised
                xlShtSL8.Cells(rowNo, "E") = Sl8Rec.Fields("Tower Start") ' Date 1st Logged
                xlShtSL8.Cells(rowNo, "F") = Sl8Rec.Fields("Domain") ' Domain
                xlShtSL8.Cells(rowNo, "G") = Sl8Rec.Fields("Brief Description") ' Description
                xlShtSL8.Cells(rowNo, "H") = Sl8Rec.Fields("Hours") ' Service Hours in Domain
                xlShtSL8.Cells(rowNo, "I") = Sl8Rec.Fields("infonaf_hours") ' infonaf Hours in Domain
                'xlShtSL8.Cells(rowNo, "K") = Sl8Rec.Fields("Working Days") ' Service Days
                rowNo = rowNo + 1
                .MoveNext
          Loop
          End With
        
        
        xlWrkBk.SaveAs Application.CurrentProject.Path & "\Reports\TREA_IncidentData_" & Left(WEA, 5) & "_" & Format(Now, "yymmdd") & ".xlsx", FileFormat:=51
        xlWrkBk.Close SaveChanges:=False
        ' Turn prompting OFF and save the sheet with original name
        xlApp.Application.DisplayAlerts = True
        xlApp.Application.Quit
        
        ' Release objects
        Set xlSht = Nothing
        Set xlWrkBk = Nothing
        Set xlApp = Nothing
       
        
        
    End Function
    Looking through the internet/ rest of the database I think I need to add this new field to a stored query within the database also?

    I've found the relevant 2 queries below however they are in SQL and I'm unsure how to add them:

    Code:
    SELECT Mid([TREA Raw Incident Data].Domain,16,4) AS Application, [TREA Raw Incident Data].Severity, RIGHT([TREA Raw Incident Data].[TREA SL4] & [TREA Raw Incident Data].[TREA SL5] & [TREA Raw Incident Data].[TREA SL6] & [TREA Raw Incident Data].[TREA SL7] & [AL Override].[OR Result],4) AS Pass, Count([TREA Raw Incident Data].[Inc no]) AS incidents
    FROM [TREA Raw Incident Data] LEFT JOIN [AL Override] ON ([TREA Raw Incident Data].[Inc no]=[AL Override].[OR Inc No]) AND (Mid([TREA Raw Incident Data].Domain,16,4)=[AL Override].[Application])
    WHERE ([TREA Raw Incident Data].Tower = "TREA Accenture" And [TREA Raw Incident Data].[Date Closed] > [Week Start] And [TREA Raw Incident Data].[Date Closed] < [Week End] And ([AL Override].OOS = False Or IsNull([AL Override].OOS)))
    GROUP BY Mid([TREA Raw Incident Data].Domain,16,4), [TREA Raw Incident Data].Severity, RIGHT([TREA Raw Incident Data].[TREA SL4] & [TREA Raw Incident Data].[TREA SL5] & [TREA Raw Incident Data].[TREA SL6] & [TREA Raw Incident Data].[TREA SL7] & [AL Override].[OR Result],4), [TREA Raw Incident Data].Severity, Mid([TREA Raw Incident Data].Domain,16,4);
    &

    Code:
    SELECT Mid([TREA Raw Incident Data].Domain,15,3) AS Application, [TREA Raw Incident Data].Severity, RIGHT([TREA Raw Incident Data].[Accenture SL5] & [TREA Raw Incident Data].[Accenture SL6] & [TREA Raw Incident Data].[Accenture SL7] & [TREA Raw Incident Data].[Accenture SL8] & [AL Override].[OR Result],4) AS Pass, [TREA Raw Incident Data].[Inc no] AS incident, [TREA Raw Incident Data].[Closure Tower], [TREA Raw Incident Data].[Date Closed]
    FROM [TREA Raw Incident Data] LEFT JOIN [AL Override] ON (Mid([TREA Raw Incident Data].Domain,15,3)=[AL Override].[Application]) AND ([TREA Raw Incident Data].[Inc no]=[AL Override].[OR Inc No])
    WHERE ([TREA Raw Incident Data].Tower="TREA Accenture" And [TREA Raw Incident Data].[Date Closed]>[Week Start] And [TREA Raw Incident Data].[Date Closed]<[Week End] And ([AL Override].OOS=False Or IsNull([AL Override].OOS)));
    Any ideas on where I'm going wrong would be greatly appreciated as I'm at a sticking point now.

    Many Thanks,
    Brian.

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Chicago, IL
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    Several people have looked at this but nobody has replied.

    So I'll tell you that you are on the right track. You need to add the new field you added in the database to the stored query.

    Without more information on how your query is stored and what table you added the field to, it is hard to tell you how to change your query.

    But you need to add a field to the query that is called "change" and contains the field you added.

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,624
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    Is there a field called 'Change' in any of the tables referenced in the query?
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    Feb 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    Hi Guys,

    Many thanks for the replies.

    I've created the field called "Change" in the AL Override table which is referenced in the query.

  5. #5
    New Member
    Join Date
    Feb 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    So I've gone into the "Design View" of the stored queries and tried adding the "Change" field from the AL Override table however I'm still getting the same message. I'm assuming I need to add it to the "Select" part of the query somehow.

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    Chicago, IL
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    yes. It should go right before the FROM

    Something like:
    Count([TREA Raw Incident Data].[Inc no]) AS incidents, [AL Overide].[Change]

  7. #7
    New Member
    Join Date
    Feb 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    Quote Originally Posted by par60056 View Post
    yes. It should go right before the FROM

    Something like:
    Count([TREA Raw Incident Data].[Inc no]) AS incidents, [AL Overide].[Change]
    Thanks, I've selected it on the "Design View" table as "Count", which has added it into the SQL view right before FROM as:

    Count([TREA Raw Incident Data].[Inc no]) AS incidents, Count([AL Override].Change) AS CountOfChange

    I won't be able to test till tomorrow when I'm on the required directory.

    Could I just ask why we're using the count function? I'm sure there's a simple reason for it but it's confusing me and I'd like to get the logic as well rather than just fixing it. Do I need the bit in bold of the statement?

    I was always under the impression AS was an alias of a table name, so I'm not quite sure where they've got "Count of Change" from unless you can also alias specific fields of tables.

    Many Thanks.

  8. #8
    New Member
    Join Date
    Feb 2013
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    Ignore the above post just me being a clown.

    I've added [AL Overide].[Change] onto the end of the query however it's now coming up with a new error message of:

    Run-Time error 3061 - Too Few Parameters. Expected 3

    This is happening at the start of the VB code on the following section of code:

    Code:
         Set slaRec = qdf.OpenRecordset
         slaRec.MoveFirst
         With slaRec
          While Not .EOF
            If (slaRec.Fields("Pass") = "Pass") Then
                Col = "AS"
            Else
                Col = "AT"
            End If
           
                If (slaRec.Fields("Severity") = "1") Then
                    xlSht.Cells(20, Col) = xlSht.Cells(20, Col) + slaRec.Fields("Incidents")
                ElseIf (slaRec.Fields("Severity") = "2") Then
                    xlSht.Cells(23, Col).FormulaR1C1 = xlSht.Cells(23, Col) + slaRec.Fields("Incidents")
                ElseIf (slaRec.Fields("Severity") = "3") Then
                    xlSht.Cells(26, Col).FormulaR1C1 = xlSht.Cells(26, Col) + slaRec.Fields("Incidents")
                Else
                    xlSht.Cells(29, Col).FormulaR1C1 = xlSht.Cells(29, Col) + slaRec.Fields("Incidents")
                End If
           
            
            .MoveNext
          Wend
          End With
    I've had a browse of the internet and can't really fathom why it's doing this, could it be something to do with the field type? "Change" is just set as text at present.

    Any idea's would be greatly appreciated.

    Thanks.
    Last edited by BayEnder111; Feb 25th, 2013 at 10:56 AM.

  9. #9
    Board Regular
    Join Date
    Jul 2012
    Location
    Chicago, IL
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    I do not think you want to use the COUNT function. Count is going to tell you how many records it found. It is unlikely you need 2 counts in the same query as they will be the same. You probably want the value from the "Change" field. This means you need to add it to the field selection list and the Group By list.

    You really need to discuss this with somebody that knows your database structure and what this query is supposed to be telling them.

    The "AS" is an alias to assign a name to a field. If the field in the query is simply a field as in "[AL Override].[Change]" the AS is not required and the field will have the name of the field from the table. If the field is a function as in "count([AL Override].[Change])" there is no field name and you need to use "AS" to specify the name you will use to access the field in the result set. As far as I know it is not required. You could access the field as myRec.Fields(22) rather than myRec.Fields("CountOfChange"). But referring to it by number would lead to problems if anybody changed the query and you didn't update the code.

  10. #10
    Board Regular
    Join Date
    Jul 2012
    Location
    Chicago, IL
    Posts
    1,571
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run - time error 3625: item not found in this collection

    If you added it just they way you typed it here you misspelled the table name. The rest of the query refers to [AL Override] you are missing an R. You also need to add it to the Group By clause.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •