Recordset AbsolutePosition

[MilaK]

Hello,

I use the following subroutine to populate record number into a field on a continuous form. I've tried to put this code behind a button on the form and test it as I add more records to the form. It seems not to work well. Could someone please suggest another method?

Dim rs As DAO.Recordset
        Set rs = Me.Recordset
        Do While Not rs.EOF
          rs.Edit
          Debug.Print rs.AbsolutePosition
          rs!supporting_text = rs.AbsolutePosition + 1
          'Debug.Print rs.AbsolutePosition + 1
          rs.Update
          rs.MoveNext
        Loop
        rs.MoveFirst

thanks,

Mila

 

[theDBguy]

Hi Mila,

What is it doing or not doing? What do mean by “not work well?”

 

[MilaK]

The code is numbering each record on continuous sub-sub form. I put this code after-update event of text control so it would update the row number as a new row is entered. It doesn't always update right away (sometimes after entered several records).

 

[theDBguy]

Hmm, maybe try refreshing or requerying the form as well.

Just a thought...

 

[MilaK]

perhaps it was how I was referencing the form in the code:

This seems to work:

[Forms]![frm_total_report]![frm_reportable_comments].[Form]![frm_citations].[Form].Requery

vs.

Me.requery

 

[theDBguy]

Hi,

Glad to hear you got it sorted out. Good luck with your project.

 

[MilaK]

Never mind...it doesn't always populate the number. Any suggestion which event should fire the code?

 

[theDBguy]

Sorry to hear it. I was going to suggest the Dirty event but not really sure about it. Are you able to post a sample db, so we can give it a try?

 

[arnelgp]

use a counter instead:

Private m_bolLineUpdated As Boolean

Private Sub Form_AfterUpdate()
    Dim rs As DAO.Recordset
    Dim i As Long
    With Me.RecordsetClone
        Set rs = .openrecordset
        If Not (.bof And .EOF) Then .movefirst
            
        While Not .EOF
            i = i + 1
            .edit
            !supporting_text = i
            .update
            .movenext
        Wend
        
        Set rs = Nothing
    End With
    m_bolLineUpdated = True
End Sub

Private Sub Form_Current()
If Not m_bolLineUpdated Then Call Form_AfterUpdate
End Sub

EDIT: putting the code in the AfterUpdate of any control won't show the Numbering on New Record since the record is not yet saved.

 

[Pat Hartman]

You do realize that the sort order of the recordset affects the sequence number so if today the recordset is sorted by last name a record might be #286 but tomorrow if it is sorted by first name the record number might be #22.

What exactly is the purpose of renumbering the records on the fly? Records should be numbered as they are entered if you want to assign your own sequence number rather than use an autonumber. What is the point of constantly renumbering the records?