On 15 Jan 2004 17:54:59 -0800 in comp.databases.ms-access,
ryan.d.rembaum@kp.org (Ryan) wrote:
[color=blue]
>Access97
>
>I hope someone out there can help. This is driving me crazy.
>Basically, I am trying to recreate VBA's built in navigation feature
>(The one that allows you to move first, next, previous, and last and
>also tells you that you are on Record # or [Total # of Records]. The
>navigation part was easy. My problem is determing the what record I
>am on and the total number of records.
>
>I put the following code into the my subforms on Current event:
>
>'------------------------
>dim RS_Temp as Recordset
>set RS_Temp = me.recordsetclone
>
>if not (rs.bof and rs.eof)then
> me!txtAbsolutePosition = RS_Temp.AbsolutePosition + 1
> RS_Temp.movelast
> me!txtRecordCount = RS_Temp.RecordCount
>end if
>'-----------------------
>
>The + 1 in the line "me!txtAbsolutePosition =
>RS_Temp.AbsolutePosition + 1"
>is because Absolute position is zero based.
>
>The problem is that this code is never run because recordset clone
>constantly returns an empty Recordset.
>
>I have even tried putting the code in the MainForms on Current event,
>also with no luck.
>
>If I open the subform alone, the code seems to work fine. Does anyone
>have a solution?[/color]
I generally set a textbox's (txtRecordNumber) controlsource to:
=RecordNumber("Item",Form)
Then in Form_Current()
txtRecordNumber.Requery
Then in a global module:
Function RecordNumber(pstrPreFix As String, pfrm As Form) As String
On Error GoTo RecordNumber_Err
Dim rst As DAO.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 7951
strTmp = "No Record Source"
Case 3021
strTmp = "New " & pstrPreFix
Case Else
strTmp = "#" & Err.Number
MsgBox Err.Description, 16, "Error " & Err & " in
RecordNumber()"
End Select
Resume RecordNumber_Exit
Resume
End Function
--
A)bort, R)etry, I)nfluence with large hammer.
