No Current Record

[Guest]

Thanks for taking the time to read my question.

I am trying to write to a table with code, but when I get to .edit, I get an
error telling me "No Current Record", and I don't know why.

Any ideas?

Brad

Public Sub TransferData()
Dim dbs As Database, rst As Recordset, qdf As QueryDef, rst2 As Recordset
Dim x As Integer
Dim CurrentPyramid As String


Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Table1")
Set rst2 = CurrentDb.OpenRecordset("tblPyramidPlanner")

rst.MoveFirst

x = 1


Do Until rst.EOF
CurrentPyramid = rst!SowBarn

With rst2
.AddNew
!SowBarn = rst!SowBarn
.Update
End With

Do Until CurrentPyramid <> rst!SowBarn Or IsNull(rst!SowBarn)
With rst2
.Edit
!Forms("Barn" & x) = rst!FeederBarn
.Update
x = x + 1
End With
rst.MoveNext
Loop
Loop

qdf.Close
rst.Close
rst2.Close
Set qdf = Nothing
Set rst = Nothing
Set rst2 = Nothing
Set dbs = Nothing

End Sub

 

[Dirk Goldgar]

Thanks for taking the time to read my question.

I am trying to write to a table with code, but when I get to .edit, I
get an error telling me "No Current Record", and I don't know why.

Any ideas?

Brad

Public Sub TransferData()
Dim dbs As Database, rst As Recordset, qdf As QueryDef, rst2 As
Recordset Dim x As Integer
Dim CurrentPyramid As String


Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("Table1")
Set rst2 = CurrentDb.OpenRecordset("tblPyramidPlanner")

rst.MoveFirst

x = 1


Do Until rst.EOF
CurrentPyramid = rst!SowBarn

With rst2
.AddNew
!SowBarn = rst!SowBarn
.Update
End With

Do Until CurrentPyramid <> rst!SowBarn Or IsNull(rst!SowBarn)
With rst2
.Edit
!Forms("Barn" & x) = rst!FeederBarn
.Update
x = x + 1
End With
rst.MoveNext
Loop
Loop

qdf.Close
rst.Close
rst2.Close
Set qdf = Nothing
Set rst = Nothing
Set rst2 = Nothing
Set dbs = Nothing

End Sub

Which record do you think you are editing with

With rst2
.Edit
!Forms("Barn" & x) = rst!FeederBarn
.Update
x = x + 1
End With

When you add a record with AddNew, after you call the Update method, you
are left back at the record that was current in the recordset before you
called AddNew. That may have been BOF or EOF, which would leave you
with no current record. If you mean to edit the record you just added,
do this:

With rst2
.Bookmark = .LastModified
End With

BTW, I think you meant to write

!Fields("Barn" & x) = rst!FeederBarn

not

 

[Guest]

Oh... I've never seen that before. Perfect!

Ya, I saw that mistake after I posted

I actually needed to put .Fields, not !Fields.

Brad

 

[Guest]

how do you move to rst2.LastModified?

Which record do you think you are editing with


When you add a record with AddNew, after you call the Update method, you
are left back at the record that was current in the recordset before you
called AddNew. That may have been BOF or EOF, which would leave you
with no current record. If you mean to edit the record you just added,
do this:

With rst2
.Bookmark = .LastModified
End With

BTW, I think you meant to write

!Fields("Barn" & x) = rst!FeederBarn

not



--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Oh... I've never seen that before. Perfect!

Ya, I saw that mistake after I posted

I actually needed to put .Fields, not !Fields.

Oops. Yeah. Seems to me I've seen that somewhere before. <g>

 

[David C. Holley]

Try putting in the .MoveFirst method right after the DO UNTIIL. Of
course if the rst2 was opened as dbForwardOnly, you won't be able to use
it. In that case, you'll need to either change the open argument or
close the recordset and then reopen it. I'm not the expert as to which
would be most efficient, but I know enough to tell you that
dbForwardOnly is more efficient when you're dealing with large numbers
of records.

David H

 

[Guest]

Thanks for the reply David,

I am currently closing and opening the recordset. I does seem to me though
that Dirks idea is a good one, I'm just not sure how to move to the
bookmarked record.

Thanks again,

Brad

 

[Dirk Goldgar]

how do you move to rst2.LastModified?

You do what I just said:

Bookmarks are how you move to specific, previously visited records in a
recordset. You save the recordset's Bookmark property in a variable,
and then later, when you want to go back to that record, you set the
recordset's Bookmark property to the value that you previously saved.
The recordset's LastModified property returns the bookmark of the last
record that was modified, so you can assign it directly to the Bookmark
property to move the recordset to that record.

 

[Guest]

Hey Dirk,

Sorry about that. I took it as that's how you set .bookmark. Thankyou for
the explanation. I haven't used bookmarks before.

Have a great weekend.

Brad