subform bookmark

[nhtraven]

Hi all,

I have a subform that i am inserting a new record into with code. It inserts the record into the number 2 position on the subform < it's a continuous form > not at the end of the recordset. In the table that it inserts the new record in it is in the last position. I know i have to use a bookmark, attempted it and it still inserts into the number 2 position on the subform. here is the code:

Function AddBalance(rstTemp As DAO.Recordset, Bal As Double, CID As Long)
'Adds a new record to the recordset using the balance for previous balance

With rstTemp
.AddNew
!ClientID = CID
!BeginBalance = Bal

.Update
.Requery
.Bookmark = .LastModified

End With

End function


i have tried .movelast and a few other things it just isnt working. Also, surprisingly, my Que Access reference book doesnt explain bookmarks at all, just defines it. I am not sure where to set, or how to set a bookmark on continuous subform, or datasheet subform. Any help is greatly appreciated!

THanks in advance

Raven

 

[RickSpr]

It's an axiom with relational databases that you only get records in a particular order if you ask for that order. If you don't use an ORDER BY or equivalent, the order of the records in your recordset is undefined.

I suspect that the new record appears in the number 2 position either because that's where it belongs according to your subform's ORDER BY (or Sort property), or because you don't specify an ORDER BY and that's just where it happens to end up.

If you need to have new records stay at the end, you should add a Date/Time field to the table, probably making its default value Now(), and then use that as the first field in your ORDER BY clause. Rick Sprague

 

[nhtraven]

Thanks Rick,

Actually i figured it out, i did not have the code in correct order or the enough of it. Here is what i did, if any inquiring minds want to know = )

Function AddBalance(rstTemp As DAO.Recordset, Bal As Double, CID As Long)
'Adds a new record to the recordset using the balance for previous balance

Dim varBookmark As Variant


rstTemp.MoveLast
varBookmark = rstTemp.Bookmark




With rstTemp
.AddNew
!ClientID = CID
!BeginBalance = Bal
.Update
.Bookmark = varBookmark

End With

End Function



And if i told you how many gray hairs that gave me, whew < 3 lines of code>
I am hoping that the aged receivables report that goes in this database goes alot EASIER = )

Raven