Array Index Out of Bounds

[OrbitMascot]

I am working with an automated ordering system. Shops place their orders via email, and the database breaks the orders down. The database is always running, but every couple of days I get an error stating "Array Index Out of Bounds" and it stops the automated system. I have included an Error trap so I am not sure why the error stops the database. Any assistance would be greatly appreciated.

Thanks,
OrbitMascot

Below is the beginning and ending code.

On Error GoTo Stop_Crash

iCount = molItems.Count
For i = 1 To iCount
If TypeName(molItems(i)) = "MailItem" Then '''''' Error highlights this line.
'run procedure
End If

Forms!Main.TimerInterval = 60000
DoCmd.Close acForm, "Automated", acSaveYes

Exit Sub

Stop_Crash:
Forms!Main.TimerInterval = 60000
DoCmd.Close acForm, "Automated", acSaveYes

End Sub

 

[PHV]

Have you tried a For Each loop instead ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[payback]

Have a look at Option Base - an array could start from 0 if not declared otherwise.

 

[nice95gle]

Well the out of bound error usually indicates that you have gone beyond the scope of the array. What is needed so this does not happen is a REDIM statement.

Go to the vb help and look up Redim.

 

[PHV]

Please, don't confuse Array and Collection ...

 

[OrbitMascot]

Thank you all for such quick responses. Your help is greatly appreciated.

 

[nice95gle]

PHV, is right you are working with a collection not an array. I'm sorry

 

[payback]

Therefore from the VBA Help file -

Count Property


Returns the number of objects in a collection.

Return Value

The return value is an Integer data type.

Remarks

Because members of a collection begin with 0, you should always code loops starting with the 0 member and ending with the value of the Count property minus 1. If you want to loop through the members of a collection without checking the Count property, you can use a For Each...Next command.

The Count property setting is never Null. If its value is 0, there are no objects in the collection.

 

[PHV]

I'm not sure that Outlook's Items collection is zero based ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[payback]

Not sure myself, but the same text suggests that For Each...Next command is best so it would not be an issue (as in your first post)

 

[payback]

A little investigation in the Microsoft Knowledgebase gives the following example for Outlook 2000 -

Set oConItems = olns.GetDefaultFolder(olFolderContacts).Items
iNumItems = oConItems.Count
For I = 1 to iNumItems
oConItems.Item(I).Birthday = "1/1/4501"
oConItems.Item(I).Close olSave
Next

therefore it appears that PHV is correct.

 

[TheAceMan1]

How are ya OrbitMascot . . .

I've been following this thread, and without going into detail, ask if you get the same error with the following changes:

[blue][purple]Change:[/purple]
   For i = 1 To iCount
[purple]To:[/purple]
   For i = 0 To iCount-1[/blue]

. . . unless you've already tried this!

See Ya! . . . . . .

 

[OrbitMascot]

I am going to put both the suggestions from PHV and TheAceMan1 into the automated system. I will let you guys know which one works the best. I probably will not know the answer for a couple of days since the error only occurs about once a week. But you all have been very helpful. Thanks for all the insight. I greatly appreciate it.

Thanks,
OrbitMascot

 

[adalger]

All the collections in Access are 0-based. Does Outlook use a different brand of VBA?

 

[PHV]

Does Outlook use a different brand of VBA?
No, same VBA I guess, but different Object model ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OrbitMascot]

Well I am still having the same problem with the array index out of bounds. I tried changing
For i = 1 To iCount
To:
For i = 0 To iCount-1

Then I tried coding the For Each...Next, but I do not believe that I am doing it correctly. Because I still left the For loop in the code as well. Any comments would be appreciated. Here is what I have coded now.

Dim i As Variant
Dim iCount As Variant
Set rst = CurrentDb.OpenRecordset("tblEmail")
Set molApp = CreateObject("Outlook.Application")
Set molNameSpace = molApp.GetNamespace("MAPI")
Set molMAPI = molNameSpace.GetDefaultFolder(olFolderInbox)
Set molItems = molMAPI.Items
Dim objOLApp As Object, objFolder As Object, objItem As Object
Dim OlMapi As Object, mydestfolder As Object, SubFolder As Object
Set objOLApp = CreateObject("Outlook.Application")
Set OlMapi = objOLApp.GetNamespace("MAPI")
Set mydestfolder = molNameSpace.GetDefaultFolder(olFolderInbox).Folders.Item("ProcessedEmails")

On Error GoTo Stop_Crash

iCount = molItems.Count
If iCount > 0 Then
For i = 1 To iCount
For Each iCount In molItems
If TypeName(molItems(i)) = "MailItem" Then
mycount = i
mylastvendor = ""
Me.Repaint
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from tmpTextLine"
DoCmd.SetWarnings True
Set molMail = molItems(i)
If molMail.SenderName Like "Store" & "*" And molMail.Body Like "*" & "Qty" & "*" And molMail.Body Like "*" & "Description" & "*" And molMail.Body Like "*" & "Date" & "*" And molMail.Body Like "*" & "Shop" & "*" Then

'more coding

End If
Exit For
End If

Next iCount
Next i


End If
rst.Close
Forms!Main.TimerInterval = 60000
DoCmd.Close acForm, "Automated", acSaveYes

Exit Sub

 

[TheAceMan1]

OrbitMascot . . .

What is the source for [blue]iCount[/blue] or where is it set?

See Ya! . . . . . .

 

[OrbitMascot]

TheAceMan1

I have iCount as a Variant

iCount = molItems.Count

OrbitMascot

 

[PHV]

Why TWO For ... Next loops ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OrbitMascot]

When I removed the For i = 1 to iCount out of the code, the code stopped working all together, so I placed the For loop back into the code.
So I think that I am going about the For Each wrong and that is why the code wouldnt work. This is my first time working with a For Each loop, so I am not confident on what the variants should be.