no current record

[gc]

Hi!

I create a module to move data from a query to a temp table. one record in the query will be several records in the temp table. I did this before and every thing was fine. but this time i got an error message "No current record". There is one record in the tblTemp after run the procedure. I can't figure out what causes this error, please help and thanks a lot in advance.

George

My code are as following:

Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT * FROM [qryCompTestWrongAnswer]&quot
rst.MoveFirst

'Open tblTemp which does not have any record

Set rst1 = db.OpenRecordset("tblTemp&quot

rst1.AddNew
rst1!Question = "1. ....."
rst1!Category = "Clinical"
rst1![#Wrong] = rst.[#Clinical1]
rst1![#TotalTests] = rst.#TotalTests
rst1.Update 'procedure stopped here!!

rst1.AddNew
rst1!Question = "2. ....."
rst1!Category = "Clinical"
rst1![#Wrong] = rst.[#Clinical2]
rst1![#TotalTests] = rst.#TotalTests
rst1.Update

...
rst1.Close
rst.close

 

[klasse]

I would try including WITH recordset as follows:

Set rst = db.OpenRecordset("SELECT * FROM [qryCompTestWrongAnswer]&quot

WITH rst

Set rst1 = db.OpenRecordset("tblTemp&quot

WITH rst1

    rst.MoveFirst
  rst1.AddNew
    rst1!Question = "1. ....."
    rst1!Category = "Clinical"
    rst1![#Wrong] = rst.[#Clinical1]
    rst1![#TotalTests] = rst.#TotalTests
    rst1.Update
...

Klasse

 

[snoopy75]

Just a speculation, but it's possible that your qryCompTestWrongAnswer is a non-updatable query (for whatever reason), and so your .Update call causes an error. Try manually opening the query and adding a record. If it doesn't work, you have your answer.

If it does work, then I'll have to speculate some more. ;-)

--Ryan

 

[gc]

Ryan:
Thank you for your response.
The qryCompTestWrongAnswer has only one record and is non-updatable. So I change that query to a make-table query and create a table. But after run the code I still got same error message.
Please help and thanks again!
George

 

[RichUK]

You say you temp table does not have any records when trying to update rst to it, try adding a dummy record to the table and leaving it there.

 

[snoopy75]

Okay, here's another guess. Maybe when you open your recordset, it's opening read-only by default. Change your OpenRecordset call like this:

Set rst1 = db.OpenRecordset("tblTemp", dbOpenDynaset)

The dbOpenDynaset parameter will tell Access to let you modify the values in the recordset. Try it, and let me know if it doesn't work.

--Ryan

 

[gc]

Hi! Ryan and Rich:
Thanks again for your response. I change the recordset call but still got same error.
I debug my code, it runs until update. But the strange thing is that there is one record in the tblTemp. Does this mean the update action is executed? Why I can't get any message after update (Debg.Print " Q1 updated&quot?
Rich: Because I want update qryCompTestWongAnswer so I delete the records in the tblTemp every time when run the code. And it doesn't matter there is a rcord in the tblTemp or not.
George

 

[snoopy75]

Alright, let's start at the beginning. So, these are the fields in your tables, right?

qryCompTestWrongAnswer
#TotalTests
#Clinical1
#Clinical2
etc...

tblTemp
Question (Text-type field)
Category (Text-type field)
#Wrong (same type as #Clinical in the query)
#TotalTests (same type as #TotalTests in the query)

Okay, I'm going to redo your code a little bit:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM [qryCompTestWrongAnswer]")
Set rst1 = db.OpenRecordset("tblTemp", dbOpenDynaset)

With rst1
   .AddNew
      !Question = "1. ....."
      !Category = "Clinical"
      ![#Wrong] = rst![#Clinical1]
      ![#TotalTests] = rst![#TotalTests]
   .Update

   .AddNew
      !Question = "2. ....."
      !Category = "Clinical"
      ![#Wrong] = rst![#Clinical2]
      ![#TotalTests] = rst![#TotalTests]
   .Update

   ...

End With

Set rst1 = Nothing
Set rst = Nothing
set db = Nothing

Just a few subtle changes, but they might make a difference. Or not. I don't know, but this is how I would write it. I guess, try this and see if it works. I'm not sure what else to tell you without actually seeing your database. Good luck!

--Ryan

 

[Maquis]

Here's another idea. I don't know if this is your problem, but you definitely need to change your field names and get rid of the '#' symbol. (Maybe NumRight, NumWrong instead). Access does not like the '#' symbol in its field names especially as the first character! Maq B-)
<insert witty signature here>