/ /

How do I emulate the "Record X of Y" that Access displays in the navigation buttons

Access 2.0: The following function will do this for a form, just send any string and the form object as the parameters,
e.g. in a ControlSource use

=RecordNumber("Item",[Form])

For code, use:
strVariable = RecordNumber("Item",Me)
or
strVariable = RecordNumber("Item",Forms!MyForm)

This will return something like "Item 4 of 899", if the form is on a new record it will return the string "New Record".

------- begin cut here -----------
Function RecordNumber (pstrPreFix As String, pfrm As Form) As String
    On Error GoTo RecordNumber_Err
    Dim rst As Recordset
    Dim lngNumRecords As Long
    Dim lngCurrentRecord As Long
    Dim strTmp As String
    
    Set rst = pfrm.recordsetclone
    rst.MoveLast
    rst.bookmark = pfrm.bookmark
    lngNumRecords = rst.recordcount
    lngCurrentRecord = rst.absoluteposition + 1
    strTmp = pstrPreFix & " " & lngCurrentRecord & " of " & lngNumRecords
RecordNumber_Exit:
    On Error Resume Next
    RecordNumber = strTmp
    rst.Close
    Set rst = Nothing
    Exit Function
RecordNumber_Err:
    Select Case Err
        Case 3021
            strTmp = "New Record"
            Resume RecordNumber_Exit
        Case Else
            strTmp = "#" & Error
            Resume RecordNumber_Exit
    End Select
End Function

------- end cut here -----------

Access 97: Same but for the fact that the trap for new record doesn't work so it displays "Record 0 of xxx", I haven't investigated this yet but I would assume that checking for rst.AbsolutePosition returning -1 could prove fruitful, then again you might like the Record 0 approach.

Email to friend
* Your name:
* Your email:
* Friend's email:
Comment: