Record Selector

[jones67]

Hello,
I'm building a small data base with a front-end and backend and was
wondering how to code my own record selectors, code a textbox to show the
current record, and another textbox to show the total records.

jones67

 

[BruceM]

Place two unbound text boxes on your form: txt Current and txtTotal. In
your form's current event:

Private Sub Form_Current()

'Inserts current record number and total number of records
Me.txtCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
Me.txtTotal = Me.RecordsetClone.RecordCount

End Sub

You can also combine this information into a single text box by making
txtCurrent and txtTotal hidden, and creating another (visible) unbound text
box named txtCounter. Add a line of code:
Me.txtCounter = Me.txtCurrent & " of " & Me.txtTotal

Or you could define CurrentRecord and RecordsetClone.RecordCount as strings,
and use the strings instead of the text box names for txtCounter.

 

[Guest]

Me.Recordset.Recordcount
is all that is necessary.


Also, here is a procedure that will allow you to control record selectors.
It provides four buttons. First, Previous, Next, and Last. You will have to
name the buttons to match this code or change this code to match your
buttons, or change both the code and the buttons some something entirely
different

To use it, put this in the Click event of each of the 4 buttons:
Call SetNavButtons(Me)

I put it in a standard module, so I can use it for all my forms.
One other thing, If your form contains any events that requery or reposition
your current record, you will want to include a call to it after the
reposition.

Sub SetNavButtons(SomeForm As Form)

On Error GoTo SetNavButtons_Error

With SomeForm
If .CurrentRecord = 1 Then
.cmdNextRec.Enabled = True
.cmdLastRec.Enabled = True
.cmdNextRec.SetFocus
.cmdFirstRec.Enabled = False
.cmdPreviousRec.Enabled = False
ElseIf .CurrentRecord = .Recordset.RecordCount Then
.cmdFirstRec.Enabled = True
.cmdPreviousRec.Enabled = True
.cmdPreviousRec.SetFocus
.cmdNextRec.Enabled = False
.cmdLastRec.Enabled = False
Else
.cmdFirstRec.Enabled = True
.cmdPreviousRec.Enabled = True
.cmdNextRec.Enabled = True
.cmdLastRec.Enabled = True
End If
End With

 

[BruceM]

I got the code from a newsgroup posting some while ago. I find that I do
need RecordsetClone.MoveLast, but that for RecordCount it is as you said. I
have read the Help files, but I don't grasp the difference between the two.

I use the following in the form's Current event to enable/disable navigation
buttons:

cmdFirst.Enabled = Not Me.CurrentRecord = 1
cmdPrev.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount >
1) _
Or Me.CurrentRecord < Me.Recordset.RecordCount
cmdLast.Enabled = Not (Me.cmdNext.Enabled = False)

I like the idea of a called procedure, but I would think that a called
procedure or whatever code is used to enable or disable navigation buttons
could be run from the the form's Current event rather than from each of the
individual navigation buttons.

 

[Guest]

I got the code from a newsgroup posting some while ago. I find that I do
need RecordsetClone.MoveLast, but that for RecordCount it is as you said. I
have read the Help files, but I don't grasp the difference between the two.

I think it may be that like a recordset in VBA, the form's recordset is not
fully populated when it first opens. Using the MoveLast causes full
population so the record count will be accurate on the first go 'round.

I use the following in the form's Current event to enable/disable navigation
buttons:

cmdFirst.Enabled = Not Me.CurrentRecord = 1
cmdPrev.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount >
1) _
Or Me.CurrentRecord < Me.Recordset.RecordCount
cmdLast.Enabled = Not (Me.cmdNext.Enabled = False)

I like the idea of a called procedure, but I would think that a called
procedure or whatever code is used to enable or disable navigation buttons
could be run from the the form's Current event rather than from each of the
individual navigation buttons.

Good idea. I am going to try that. I believe in a less is best concept for
coding. If I am going to use any procedure more that once, it goes in a
function or sub.

 

[Guest]

I tried out the Current event for calling the sub. It works just fine.
Thanks again for the idea.

 

[BruceM]

Thanks for the input about RecordsetClone. Its purpose remains somewhat
mysterious to me, but I think I understand a little more than I did. One
thing I noticed in my experiments is that on the line Me.Recordset.MoveLast
I ended up on the last record and couldn't navigate away from it. From that
I can figure that RecordsetClone is a sort of snapshot of the recordset, and
that moving to the end of it does not actually perform that action upon the
active recordset. Something like that.

 

[Brendan Reynolds]

You might be interested in what Michael Kaplan says about the difference
between the RecordsetClone and Recordset properties at the following URL ...

http://www.trigeminal.com/usenet/usenet022.asp?1033

 

[BruceM]

Thanks for the link. Every little bit helps.

You might be interested in what Michael Kaplan says about the difference
between the RecordsetClone and Recordset properties at the following URL
...

http://www.trigeminal.com/usenet/usenet022.asp?1033