When I fill all fields I get error 3021: No Curent Record

2
votes

I have a table that needs to be filled with some record through a form. I am using this code: 

Set rp = CurrentDb.OpenRecordset("table1")
Do
    rp.Edit
    rp!field2 = Text22
    rp.Update
    rp.MoveNext
Loop

When the code fills the table and gets to the end of the file, I get the 3021 error.

Why is this happening?

2
ms-accessvba

2 Answers

3
votes

Try this one:

Set rp = CurrentDb.OpenRecordset("table1")
Do Until rp.EOF
    rp.Edit
    rp!Field2 = Text22
    rp.Update
    rp.MoveNext
Loop

another way would be to use something like this:

CurrentDb.Execute "UPDATE table1 SET field2='" & Text22 & "'", dbFailOnError
1
votes

It would be much, much quicker to use SQL if you wish to update every row in a table to a specific value.

For example:

sSQL= "UPDATE Table1 SET Field2=param"

Set qdf = db.CreateQueryDef("", sSQL)

qdf.Parameters!param = Trim(Me.Text22)
qdf.ReturnsRecords = False
qdf.Execute dbFailOnError

intResult = qdf.RecordsAffected

MsgBox "You updated " & intResult & " records."

You can even specify the type of parameter, for example:

sSQL= "PARAMETERS param Text(150); UPDATE Table1 SET Field2=param"

It is far safer to use parameters that to build sql strings.