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! 6 2455
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>
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.
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
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
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
Thanks, I removed .MoveNext and it works :-) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Joe Scully |
last post by:
Hi all,
I am having trouble with access adding a blank record to a subform
everytime I finish entering data and closing the form. (The form and
subform are based on the one table)
When...
|
by: John Baker |
last post by:
Hi:
I have a query which supports a form. Te form is used to edit, update and change records
in the table the query is based on. It all works fine EXCEPT that the "New" record (blank
updatable...
|
by: Mark |
last post by:
I have a table about people containing 25 fields. The table contains the usual
fields - first, last, address, city, state and zip. There is no primary key.
These fields all have data with the...
|
by: Alicia |
last post by:
Hello everyone based on the data, I created a union query which
produces this.
SELECT ,,, 0 As ClosedCount
FROM
UNION SELECT
,, 0 AS OpenedCount,
FROM
ORDER BY , ;
|
by: James Radke |
last post by:
Hello,
I have a multithreaded windows NT service application (vb.net 2003) that I
am working on (my first one), which reads a message queue and creates
multiple threads to perform the processing...
|
by: Larry Woods |
last post by:
I have a datagrid that is carrying all fields of a record...except one. Now
I want to update the underlying database via a dataadapter. The update is
working but the field that is "left out" is...
|
by: p175 |
last post by:
Hi people,
I have a stored procedure that creates many Global temporary
session tables. Into each of these tables go the results of various
processing using relational division all keyed and...
|
by: dan |
last post by:
within a loop i am building a sql insert statement to run against my
(programatically created) mdb. it works but it seems unreasonably SLOW!
Sorry, dont have the code here but the jist is very...
|
by: dlevene |
last post by:
Hi - newbie here, be gentle. In Access 2003, I've created a report (based on a query) to produce mailing labels for all records where = "Community". is a combo-box field with the values coming from...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |