Determining Total # of Records and Absolute Position of Current Record on linked Subform

[Ryan]

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.recordsetclo ne

if not (rs.bof and rs.eof)then
me!txtAbsoluteP osition = RS_Temp.Absolut ePosition + 1
RS_Temp.movelas t
me!txtRecordCou nt = RS_Temp.RecordC ount
end if
'-----------------------

The + 1 in the line "me!txtAbsolute Position =
RS_Temp.Absolut ePosition + 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?

Thanks!!
Ryan

[Trevor Best]

On 15 Jan 2004 17:54:59 -0800 in comp.databases. ms-access,
ry************@ kp.org (Ryan) wrote:

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.recordsetclo ne

if not (rs.bof and rs.eof)then
me!txtAbsoluteP osition = RS_Temp.Absolut ePosition + 1
RS_Temp.movelas t
me!txtRecordCou nt = RS_Temp.RecordC ount
end if
'-----------------------

The + 1 in the line "me!txtAbsolute Position =
RS_Temp.Absolu tePosition + 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?

I generally set a textbox's (txtRecordNumbe r) controlsource to:

=RecordNumber(" Item",Form)

Then in Form_Current()
txtRecordNumber .Requery

Then in a global module:

Function RecordNumber(ps trPreFix As String, pfrm As Form) As String
On Error GoTo RecordNumber_Er r
Dim rst As DAO.Recordset
Dim lngNumRecords As Long
Dim lngCurrentRecor d As Long
Dim strTmp As String

Set rst = pfrm.RecordsetC lone
rst.MoveLast
rst.Bookmark = pfrm.Bookmark
lngNumRecords = rst.RecordCount
lngCurrentRecor d = rst.AbsolutePos ition + 1
strTmp = pstrPreFix & " " & lngCurrentRecor d & " of " &
lngNumRecords

RecordNumber_Ex it:
On Error Resume Next
RecordNumber = strTmp
rst.Close
Set rst = Nothing
Exit Function

RecordNumber_Er r:
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_Ex it
Resume

End Function

--
A)bort, R)etry, I)nfluence with large hammer.

[Rich]

Me.txtCurrRec = Form.CurrentRec ord
Me.txtTotalRecs = Form.RecordsetC lone.RecordCoun t + IIf(Form.NewRec ord, 1, 0)