Item Not found in collection

[Anthony Viscomi]

I receive the "Item not found in this collection " Run-time error when I
execute the following:

Function RunUpdate()
DoCmd.SetWarnings False
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim sSQL As String
Dim db As Database
Dim parSupplier As Parameter
Dim rs As Recordset
sSQL = "SELECT [Vendor].*"
sSQL = sSQL & " FROM [Vendor];"
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
For Each qdfCurr In db.QueryDefs
Set parSupplier = qdfCurr.Parameters!Supplier

If InStr(qdfCurr.Name, "849 Pull Vendor") = 1 Then
DoCmd.OpenQuery qdfCurr.Name
End If
Next qdfCurr

.MoveNext
Loop
End If
End With
rs.Close
Set rs = Nothing

End Function

Any thoughts?

Thanks!
ANthony

 

[Brendan Reynolds]

This code seems to assume that every query in your database will have a
parameter with the name 'Supplier'. That would seem a very unusual
situation. But if all of your explicitly saved queries really do have a
parameter with this name, then perhaps the problem is with the hidden
queries that Access creates for SQL statements used in form and report
Record Source and combo and list box Row Source properties. These queries
can be identified by the fact that their names begin with the characters
"~sq_". So you could add a test for ...

Left$(qdfCur.Name, 4) <> "~sq_"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Anthony Viscomi]

Actually, only one of the queries have the parameter; but even if I direct
the statement to that query I have another issue. I am trying to use the
values from the Vendor Table as the criteria for a mktblquery then export
each of the tables to a seperate XLS. My main problem is that I am not quite
sure how use the value(s) from the Vendor table as my query parameters via
VBA.

Thanks!

This code seems to assume that every query in your database will have a
parameter with the name 'Supplier'. That would seem a very unusual
situation. But if all of your explicitly saved queries really do have a
parameter with this name, then perhaps the problem is with the hidden
queries that Access creates for SQL statements used in form and report
Record Source and combo and list box Row Source properties. These queries
can be identified by the fact that their names begin with the characters
"~sq_". So you could add a test for ...

Left$(qdfCur.Name, 4) <> "~sq_"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I receive the "Item not found in this collection " Run-time error when I
execute the following:

Function RunUpdate()
DoCmd.SetWarnings False
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim sSQL As String
Dim db As Database
Dim parSupplier As Parameter
Dim rs As Recordset
sSQL = "SELECT [Vendor].*"
sSQL = sSQL & " FROM [Vendor];"
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)

With rs
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
For Each qdfCurr In db.QueryDefs
Set parSupplier = qdfCurr.Parameters!Supplier

If InStr(qdfCurr.Name, "849 Pull Vendor") = 1 Then
DoCmd.OpenQuery qdfCurr.Name
End If
Next qdfCurr

.MoveNext
Loop
End If
End With
rs.Close
Set rs = Nothing

End Function

Any thoughts?

Thanks!
ANthony