MS Access 2003 : Error 3265 "Item not found in this collection"

R B
R B used Ask the Experts™
on
I'm running a Macro that runs the code exporting a table & a query to another newly created database.  I'm getting this error on coding that was previously working.  It's now stopping and saying:

Microsoft Office Access
3265
Item not found in this collection

Can you tell me what this means and how I should fix it?
Thanks,  R.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Ahhh ... can you post the code?

mx
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
the error means that your code tries to access a field by name that is not included in the query...
R B

Author

Commented:
Here is the code:   Can you identify what is wrong? and how to fix it?   All this is doing is catching "all" the columns from the original view - that are from Octoboer 2007 onwards to date.  There are no other fields that are involved that may be incorrect by name or from another table/view.   Please help.....  This is a really urgent fix that I need to do......   Thanking you for your timely help,  sincerely,  R.

AUDItems(9).Source = "dbo.ncv_audit_val_allocs"
AUDItems(9).Target = "_dbo_ncv_audit_val_allocs"
AUDItems(9).Query = "_AUDValAllocsQry"
AUDItems(9).Export = "Labor Distribution Output - Value Allocation"
AUDItems(9).DBFilePath = AUD_PATH
AUDItems(9).Overwrite = AUD_OVWR
AUDItems(9).Statement = _
 "SELECT " & _
 "  [" & AUDItems(9).Target & "].* " & _
 "FROM " & _
 "   " & AUDItems(9).Target & _
 " WHERE " & _
 " PPEndDate between #" & AUD_FROM_PP & "# and #" & AUD_TO_PP & "#" & _
 ";"
AUDItems(9).UserQuery = "_Audit - LD Value Allocation - Employee Hours"
AUDItems(9).UserQueryStatement = _
"SELECT " & _
" Employee, " & _
" Mid([Activity],4,1) AS Arena, " & _
" Sum(ReportedHours) " & _
"FROM " & _
" [" & AUDItems(9).Export & "] " & _
"WHERE " & _
" Employee Like [Enter Name (eg, Public,J*):] " & _
"GROUP BY " & _
" Employee, " & _
" Mid([Activity],4,1)" & _
";"
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
the only "array/collection" item used seems to be:
AUDItems(9)

so, is that array/collection item set?
R B

Author

Commented:
The  Module starts with setting up the Array for Array Items AUD(1) thru AUD(9).   1 thru 8 work ok,  but when it gets to AUD(9),  I get this error message......

Please help....  Thanks,  R.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>The  Module starts with setting up the Array for Array Items AUD(1) thru AUD(9).
can you clarify that part of the code and variable declaration?
R B

Author

Commented:
AUD(9) :  Looks to a view in the ODBC Sybase Database - and gathers all columns from that view - and brings it forth :  with one parameter:  the Pay-Period-End-Dates are between Oct 2007 thru to Current.  

If I make a Make-Table Query - it works thru.  It's just that within this Arrayed-Module,  it stops when it gets to this one - which is the last one amonst 9 such components.   I need to have it functioning directly from one Macro - which operates & exports this array from the module.

I'm not sure - if this is what Sage was asking in the previous reply....  
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
please post the relevant code.
R B

Author

Commented:
Hi, Sage...  Please look above... I had posted the coding......   Is there any more coding that is needed?

I am looking into the names of the columns - they are all valid.....

Please see if you can help me fix this.....  it's really urgent - and it's the last item that is holding up completion of this project.....

thank you.....
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>Hi, Sage...
actually, that is my rank. my EE nickname is angelIII

>Please look above... I had posted the coding......
sorry, but you still missed the part where you declare the array variable AUDItems.

anyhow, if you have any doubts, you might want to attach the db?
R B

Author

Commented:
Thanks fo ryour timely help.....   Sorry for the delay in allocating the points and closing the question.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial