472,967 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,967 software developers and data experts.

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

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?

Thanks!!
Ryan
Nov 12 '05 #1
2 7501
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.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?


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.
Nov 12 '05 #2
Me.txtCurrRec = Form.CurrentRecord
Me.txtTotalRecs = Form.RecordsetClone.RecordCount + IIf(Form.NewRecord, 1, 0)
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: deko | last post by:
I have a form with a subform datasheet - I need code behind the OnDelete event of the subform: Private Sub Form_Delete(Cancel As Integer) 'do something that depends on which record is deleted...
3
by: Anthony Kroes | last post by:
I have a subform on a form and they are not linked. On the main form is a text box where the user types in a number. When that number changes, I have some code to make the corresponding text...
16
by: ken | last post by:
I have a formA and subformB subformB is a continous form with a txtTotal in form footer =Sum() This works fine as long as there are records in form but if form is null I get Error I would like...
3
by: Rashapoo | last post by:
I have just completed a basic course in Access. I have some relational b/g in DB2 and Cobol. I have a question my instructor couldn't answer (or maybe I didn't explain myself) I design a...
4
by: sparks | last post by:
I am trying to fix a database that someone did about 4 yrs ago in access97. The main table just contains demographics and is on the main form of the database. It has a subform on a tab that...
4
by: Macbane | last post by:
Hi, I have a 'main' form called frmIssues which has a subform control (named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs. A control button on the main form opens a pop-up form...
3
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we...
3
by: paquer | last post by:
On my Main form I have a Command Button that opens a Subform in order to create a new Subform record. At this point I want the subform to show only the new record being created. Not all the...
3
by: Gord | last post by:
If I have a form open with a subform control on it in datasheet view that has its record source set to a query or a table, is it possible to determine which record the user has clicked into with...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.