Search:     Advanced search
Browse by category:
Glossary | Contact Us

Database Knowledge Base / MS Access /

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

Add comment
Views: 280
Votes: 3
Comments: 0
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.
Others in this Category
document When compacting an Microsoft Access database how do you control the ownership of the database?
document How can I make my combo and list boxes faster?
document Where can I obtain a list of Access Basic Errors and their associated messages
document How do I access the recordset of a SUBform. I know how to clone the set of a form, but this doesn't work for subforms.
document How do I run an internal DOS command from within Access Basic, if I try x=Shell("copy file1 file2",1) I get a message "File not found".
document How we can perform MINUS (Oracle) Or EXCEPT (Maxdb) in MS Access?
document What is Data Mapping
document What is an Ad Hoc Query?



RSS