Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Record Count of a recordset 2

Status
Not open for further replies.

Rachel30

Programmer
Mar 1, 2005
95
GB
Hi,

I have a form with two navigation buttons to move to the next or previous record. I also have an unbound text box which I would like to display which record they are on. what code would I use. Thanks Rachel
 
rs.absoluteposition gives the relative position within a recordset

AbsolutePosition only works with dynasets or snapshots.

For a bound form you ca use
Me.RecordsetClone.Bookmark = Me.Bookmark
Me!txtMyRecNumControl = Me.RecordsetClone.AbsolutePosi­tion

(Change txtMyRecNumControl to your own name)
 
Following on from Lupin46's advice, the following code in the forms onCurrent event with give you the number of the current record out of however many records there are.

Me.RecordsetClone.Bookmark = Me.Bookmark
txtYourTextBoxname = Me.RecordsetClone.AbsolutePosition + 1 & " of " & Me.RecordsetClone.RecordCount
 
Hi,

Thanks for your help. I have this bit of code

Me.RecordsetClone.Bookmark = Me.Bookmark
Me.txtRecCount = Me.RecordsetClone.AbsolutePosition + 1 & " of " & Me.RecordsetClone.RecordCount

In the on current event of the form.

I get this error message when I click to open form.


You enetered an expression that has an invalid reference to the property bookmark.

I am using access 2000 any idea's why this is happening.

Thanks Rachel.
 
Try substituting the absoluteposition and bookmark thingies with:

[tt]me.currentrecord[/tt]

may perhaps throw in a test for me.newrecord to avoid displaying record 100 of 99 on new records;-)

Perhaps also issue a me.recordsetclone.movelast prior to fetching the count, in case it doesn't return the correct recordcount.

Roy-Vidar
 
Will me.recordsetclone.movelast give me the total number of records in this recordset?
 
I am sorry, i should say this post was helpful for me, but was wondering if your final statement: me.recordsetclone.movelast will give me the total number of records in this recordset?
 
The recordcount of DAO recordsets are not very reliable without doing what is often referred to as "fully populating" it. The recordcount of a forms recordsetclone is often OK, but sometimes you'll need to "fully populate" it - which is performed through a .movelast. See help files on the (DAO) .RecordCount property.

Roy-Vidar
 
thanks i am going to post a new faq which might be the same off of this...hopefully it will be ok..
 
Rachel

Iuse the following code to display the record number and count.
Code:
Private Sub Form_Current()
On Error GoTo Err_Form_Current

'Display the current record number and the
'Total number of records
    Dim recPosition As Recordset
    Dim recCount As Integer
    
    Set recPosition = Me.RecordsetClone
    recPosition.Bookmark = Me.Bookmark
    If recPosition.AbsolutePosition = 0 Then
        recPosition.MoveLast
        recCount = recPosition.RecordCount
        recPosition.MoveFirst
    End If
        
    recCount = recPosition.RecordCount
    Me![txtCount] = "Record " & (recPosition.AbsolutePosition + 1) & _
                    " of " & recCount
    recPosition.Close
    
Exit_Form_Current:
    Exit Sub
    
Err_Form_Current:
'Error 3021 means recordset is at "Add New Record"
    If Err = 3021 Then
        Resume Exit_Form_Current
    Else
        MsgBox Err.Description
        Resume Exit_Form_Current
    End If

End Sub

HTH
Lightning
 
Hi,

I have a recordset called rsAss when the form loads it loads up the recordset rsAss I have this piece of code in the form load that opens the recordset the codes is:-

Set mDB = CurrentDb()
Set mRsAss = mDB.OpenRecordset("Select * from tblPolicyAssociates Where (PolicyID = '" & [Forms]![frmMainMenu]![PoliciesForm].[Form]![PolicyID] & "' AND Cancel = FALSE)", dbOpenDynaset)


If mRsAss.RecordCount <> 0 Then

There is more code as well which I don't need to go into. Basically the code work out how many associates a person has it could be 0,1 2 or 3 etc.

I would like the form to open on the first record and I have navigation buttons that when you clcik txtRecCount displays the current record.

Everything on the form is unbound. I have put this code in the form current:-

Private Sub Form_Current()
On Error GoTo Form_Current_Err

'Display the current record number and the
'Total number of records
Dim recPosition As Recordset
Dim recCount As Integer

Set recPosition = Me.RecordsetClone
recPosition.Bookmark = Me.Bookmark
If recPosition.AbsolutePosition = 0 Then
recPosition.MoveLast
recCount = recPosition.RecordCount
recPosition.MoveFirst
End If

recCount = recPosition.RecordCount
Me.txtRecCount = "Record " & (recPosition.AbsolutePosition + 1) & _
" of " & recCount
recPosition.Close

If IsNull(LastName) = True And IsNull(txtStartDate) = True Then
txtStartDate = Date
txtEndDate = Forms![frmMainMenu]![PoliciesForm].[Form]![EndDate]
ComboPublicLiabilityCode = mintPLID
txtPublicLiability = DLookup("GrossAssociates", "tblPublicLiabilityCodes", "PublicLiabilityID=" & mintPLID)
txtNetPublicLiability = DLookup("NetAssociates", "tblPublicLiabilityCodes", "PublicLiabilityID=" & mintPLID)
End If

Exit Sub

Form_Current_Err:
MsgBox (Err & ":" & CStr(Error))
Exit Sub
End Sub

I just keep getting an error message:-

You enetered an expression that has an invalid reference to the recordsetclone property.

Any idea's what I could be doing wrong.
 
All the above suggestions take into account the usual way of working with Access, which is by using bound forms. For bound forms, the forms recordset can be accessed through the recordset or recordsetclone property of the form. Thisis not so for ubound form, which is probably what the message is telling.

In such case, I think I'd put the count thingies with the record moving event procedures, where you probably use some of the .move... methods of the recordset (I assume the mRsAss recordset is a form public or private).

[tt]Me.txtRecCount = "Record " & (mRsAss.AbsolutePosition + 1) & _
" of " & mRsAss.RecordCount[/tt]

Perhaps if there are some records, you'll need to ensure the recordset is fully populated prior to counting. Try for instance:

[tt]dim rsClone as dao.recordset
set rsClone = msrass.clone
rsClone.movelast[/tt]

Then use the cloned recordset for the recordcount.

Here's a faq btw, on how to get the most out of the membership - faq181-2886 - often if we now the relevant details, we can avoid much of the guessing;-)

Roy-Vidar
 
Hi,

Thanks for your help. I have got my code almost working. It is including a new record in the record count. which I would like to disclude. You was right Roy-Vidar the form was unbound so thanks for that.

My code I have now is:-

Private Sub cmdNext_Click()

Dim rsClone As dao.Recordset
Set rsClone = mRsAss.Clone

rsClone.MoveFirst
rsClone.MoveLast

On Error GoTo cmdNext_Click_Err

If mRsAss.EOF = False Then
mRsAss.MoveNext
If ((mRsAss.AbsolutePosition) + 1) <= mRsAss.RecordCount And ((mRsAss.AbsolutePosition) + 1) <> 0 Then
Me.txtRecCount = "Record " & ((mRsAss.AbsolutePosition) + 1) & " of " & mRsAss.RecordCount
End If
Else
Beep
End If

The rest of the code is not revelent to this procedure.

So how can I get the record count to ignore a new record ?

Thanks Rachel.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top