Need to add multiple blank records in one table based on a parameterized query

kenshiro

Hi All,

I'm having 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
'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!

May 12 '06 #1

Tom van Stiphout

On 11 May 2006 19:42:31 -0700, "kenshiro" <ri**********@yahoo.com>
wrote:

The Parameters collection starts at index 0.

-Tom.

<clip>

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

<clip>

May 12 '06 #2

kenshiro

Sorry Tom, I changed the Parameters index to 0, but still get the same
error. What does the index point to exactly? I was thinking it
pointed to the second column in the query, which in my case is
AuditToolName, and then I pass the value of txtAuditTool textbox on the
Main Menu form to it.

May 12 '06 #3

kenshiro

I got some great advice and changed the qdf.Parameters line to read:

qdf.parameters("prmAuditTool") = Me.txtAuditTool

I went into my query and added a parameter called prmAuditTool to the
query, and assigned that parameter to my AuditToolName field in the
query. This did the trick.

Now, my code breaks during the code to add new records to tblAnswers.
The code will add one new record to tblAnswers and tie it successfully
to the AuditID and QuestionID, but once the code hits the ".MoveNext"
line, it breaks. It gives me the error:

"No Current Record"

Is the code looking for an existing record? I want it to add a new
record, so do I need an extra line of code to add a new record?

Thanks,

Rich

May 12 '06 #4

kenshiro

I got some great advice and changed the qdf.Parameters line to read:

qdf.parameters("prmAuditTool") = Me.txtAuditTool

I went into my query and added a parameter called prmAuditTool to the
query, and assigned that parameter to my AuditToolName field in the
query. This did the trick.

Now, my code breaks during the code to add new records to tblAnswers.
The code will add one new record to tblAnswers and tie it successfully
to the AuditID and QuestionID, but once the code hits the ".MoveNext"
line, it breaks. It gives me the error:

"No Current Record"

Is the code looking for an existing record? I want it to add a new
record, so do I need an extra line of code to add a new record?

Thanks

May 12 '06 #5

Tom van Stiphout

On 12 May 2006 05:38:01 -0700, "kenshiro" <ri**********@yahoo.com>
wrote:

No Current Record occurs when you're trying to access a record and
there isn't any. For example your code has a MoveFirst call, but you
can't MoveFirst if there are no records. You need to test for that
BEFORE you MoveFirst. You can test with .RecordCount, or .EOF.

After .Update, there is no need to .MoveNext. The record will be added
during .Update, and next time you loop around another one will be
added, without the need to "move to an empty slot" if that was your
intention.

-Tom.

I got some great advice and changed the qdf.Parameters line to read:

qdf.parameters("prmAuditTool") = Me.txtAuditTool

I went into my query and added a parameter called prmAuditTool to the
query, and assigned that parameter to my AuditToolName field in the
query. This did the trick.

Now, my code breaks during the code to add new records to tblAnswers.
The code will add one new record to tblAnswers and tie it successfully
to the AuditID and QuestionID, but once the code hits the ".MoveNext"
line, it breaks. It gives me the error:

"No Current Record"

Is the code looking for an existing record? I want it to add a new
record, so do I need an extra line of code to add a new record?

Thanks,

Rich

May 12 '06 #6

kenshiro

Thanks, I removed .MoveNext and it works :-)

May 12 '06 #7