Item not Found in this Collection error

Hi All,

I'm new to the forums, and have a problem with some VBA code in one of my Access 2003 databases. I'm getting the following error when running code behind a command button on a form: "Item not found in this collection".

I suspect it has something to do with parameters in the query I'm calling. I need to pass an object on the form to a parameter in the query, and the query is quite complex as well. Here is the query:

SQL for qryQuestions:

SELECT tblQuestions.QuestionID, tblAuditTool.AuditToolName
FROM tblQuestions INNER JOIN (tblAuditTool INNER JOIN tblQuestionsAuditToolLink ON tblAuditTool.AuditToolID = tblQuestionsAuditToolLink.AuditToolID) ON tblQuestions.QuestionID = tblQuestionsAuditToolLink.QuestionID
ORDER BY tblQuestionsAuditToolLink.SortOrder;

*********************************************************
and here is the VBA code running behind the "Open Audit Form" command button on a form called "frmMainMenuNew":

'***** Add Answer Records for each Question on the Audit Tool *****
'
'This code will cycle through each question
'on the audit tool chosen by the user on the Main Menu
'and add an Answer record for each of those questions
'
'******************************************************************


Dim MyDB As Database
Dim MyTable As Recordset
Dim rstAnswers As Recordset
Dim rstQuestions As Recordset
Dim lngAuditID As Long
Dim qdf As QueryDef

Dim strSQL As String


'establish DB, workspace and open Audit table
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("tblAudit", dbOpenDynaset)
Set qdf = MyDB.QueryDefs("qryQuestions")

MyTable.MoveLast
lngAuditID = MyTable![AuditID]


qdf.Parameters(1).Value = Forms!frmMainMenuNew!txtAuditTool

'put a chunk of the answers table data into memory
'so recs can be added

Set rstAnswers = MyDB.OpenRecordset(("Select * From tblAnswers Where AuditID = " & lngAuditID), dbOpenDynaset)

'Put the questions table data (only questions pertaining to the Audit Tool
'entered by the User) into memory so recs can be added using this table as a guide...
'This way, if the questions ever change -- if one is added, if
'one is dropped -- the app will adjust automatically

Set rstQuestions = qdf.OpenRecordset(dbOpenDynaset)


'move to the first record (the first question) in rstQuestions
rstQuestions.MoveFirst

'and now move through each question one by one,
'in sync with the answers recordset,
'adding a new answer record for each question

Do Until rstQuestions.EOF 'run loop until the End Of File

With rstAnswers
.AddNew
'NOTE: [QuestionID] is a field in two tables --
'tblQuestions, tblAnswers
![QuestionID] = rstQuestions![QuestionID]
![AuditID] = lngAuditID
![TotalYes] = "0"
![TotalNo] = "0"
.Update
.MoveNext
End With

rstQuestions.MoveNext

Loop 'go back and do it again

'done -- so close everything and free the RAM
rstAnswers.Close
Set rstAnswers = Nothing

rstQuestions.Close
Set rstQuestions = Nothing

'recalc so new match records display in subform
Me.Recalc

**********************************************************

Can someone please help me? I need to create new records in tblAnswers for each record in tblQuestions, but only if those question records belong to the Audit Tool that the User selects on the Main Menu form.

Hope this makes sense.

Thanks for any help in advance!

On what line does the error occur?
In addition, the expression "DBEngine.Workspaces(0).Databases(0)" can be replaced with "CurrentDb", which is a method of the application object.

lagbolt said:

On what line does the error occur?
In addition, the expression "DBEngine.Workspaces(0).Databases(0)" can be replaced with "CurrentDb", which is a method of the application object.

Click to expand...

Thanks for the tip! I'm rather new to VBA coding so any help is appreciated

I believe the error is occurring at this line:

Set rstQuestions = qdf.OpenRecordset(dbOpenDynaset)

It seems as though the code may be having a difficult time passing the value in "forms!frmMainMenuNew!txtAuditTool", which is the Audit Tool name, to the query called "qryQuestions". qryQuestions has some inner joins since the relationship between tblQuestions and tblAuditTool is many-to-many, and I have a junction table called "tblQuestionsAuditToolLink" to make the connection.

Here's some more background on what the app should do:

Basically, I'm creating an Audit database where a users from different Facilities will be filling out their own Audit Forms. Each audit form will point to one record in the table called tblAudit. The new tblAudit record is created when the user picks their Facility and what Audit Type/Audit Tool they want to use on the Main Menu form.

The Main Menu form is where the user picks their Facility and Audit Type/Audit Tool from combo box drop-downs. After picking these items, they click a button called "Open Audit Form."

The Audit Form will have a form with a subform. The form will link to tblAudit and be passed the Facility and Audit Tool that the user chose on the main menu. These will be passed to tblAudit as foreign keys. The subform will need to display the questions that belong to the particular Audit Tool that the user picked on the Main Menu form. Each Audit Tool has a set of Questions associated with it. However, each question in tblQuestions can also appear in many different Audit Tools, so there is a many-to-many relationship there.

So, the subform will display only the questions associated with the Audit Tool. Each question will need an Answer record associated with it and the Audit, but those answer records are not created....yet. What I want to do in the code is create one new answer record for each question record that appears in the subform, and associate each new answer record with the question that goes next to it, and with the Audit itself. So, tblAnswers has two foreign keys in it: QuestionID and AuditID, to associate it with both a question and an audit.

I'm more suspicious of
But to resolve the error you need to know what is causing. Do you get a dialog showing the error message and buttons End, Debug, Help. If so click Debug and this should highlight the line where the error occurs.

Nope, I just get an "OK" in the dialog error.

In a code window go to the Menu->Tools->Options->General Tab->Error Trapping section and select the "Break On All Errors" option. Then re-run your code. This should break into code on the line where the error is.

Another option is click in the left margin of the procedure at the first line of code. A burgundy circle should appear in the margin and the line will be hilighted. Then run your code and the debugger should break into code at this line. Then on the debug toolbar you can "step into" code line by line, or hit F8 for the same effect. Keep stepping until the error occurs.

Great tips, I'll give the debugger a try. Thanks lagbolt...brb.

Ok, found the culprit line:

qdf.Parameters(1).Value = Forms!frmMainMenuNew!txtAuditTool

What I'm wanting to do here is to add a parameter to my SQL querie's "AuditToolName" field. The parameter is whatever value is in the textbox: "txtAuditTool" that's on the frmMainMenuNew form (which is the same form that the code is running behind.

I put a "1" in the index for qdf.Parameters because the AuditToolName field is in the second position in my query. If it was in the first position, I would think that it should be "0", right?

A query has a parameters collection, and its members are indexed starting with zero. In addition, the Value property is the default property of a Parameter so it can be dropped. Finally, use the Me keyword to return a reference to the object in which code is currently running. This yields...
so see if that runs.
If not, you may not have correctly specified the parameter in the query. In that case, open the query in design view, goto Menu->Query->Parameters and specify the name of the parameter and its data type. Then type the name of the parameter inside square prackets in the criteria line of the query field you want to apply it to.
Then you might also reference the parameters collection of the query using the name of the parameter, as follows...

It worked!! Thanks a million!

Now the code gets passed that line, but breaks at the ".MoveNext" line in this code section:

With rstAnswers
.AddNew
'NOTE: [QuestionID] is a field in two tables --
'tblQuestions, tblAnswers
![QuestionID] = rstQuestions![QuestionID]
![AuditID] = lngAuditID
![TotalYes] = "0"
![TotalNo] = "0"
.Update
.MoveNext
End With

**********************************************************
Any ideas?

edit: the error message I get at this line is "No Current Record"

I realized that I didn't need a .MoveNext in the code, since there is no record there and a new one will get created. The code works now. Thanks for all of your help!

You're welcome.