Record 1 of 532

[mvaughan737]

Hello,



Newby trying to learn. I made a Access Form that contains about 530 records. When they do a search for an item, then it could come up with a variable possibilities, such as, Record 1 of 5; based on search criteria entered. I know this option exists at the bottom of the form, but the unfamiliar people with Access, they are unaware of it. So, I put my own Records 1 of 5 so that they can't miss it; right next to the NEXT button.

I can get the total records that match the search criteria with the COUNT feature. But, my problem is the first, second, third number and so on. So when they do a search and click the next button, it will show like Record 225 of 5. Yes, I have it set to the ID. So my question is, how do I fix it so that it starts with 1 of 5 and then click the next button to get 2 or 5 and so on. See example below, it works fine as long as I don't put any search criteria.



Any help would be appreciated,

Michael Vaughan

[ranman256]

all forms and tables have a record counter at the bottom as default.
The next button moves to the next record.

[isladogs]

Whilst ranman is correct, the OP has already stated why he is creating his own navigation controls

Change the textbox showing 520 to use a DCount function.
Something like this

Unfiltered

=DCount("*","YourTableName")

Filtered - update textbox record source to:

=DCount("*","YourTableName","FieldName= filter criteria")

I also suggest you disable the previous button when on the first record & disable the next button when on the last record
If only one record, disable both buttons

[ssanfu]

You might also check out these sites about Custom Record Counters:

http://www.theaccessweb.com/forms/frm0026.htm
http://www.databasedev.co.uk/navigat...ord-count.html
https://bytes.com/topic/access/answe...675-record-x-y


All seem to be about the same re: code...

[davegri]

The textbox is named txtNavStat

Private Sub Form_Current()
    Dim nCount As Integer, nPosition As Integer
    nCount = Me.Recordset.RecordCount
    If nCount = 0 Then
        MsgBox "No Records exist yet.", vbOKOnly, "  R E S T R I C T I O N  "
        Exit Sub
    End If
    nPosition = Me.CurrentRecord
    Me!txtNavStat = nPosition & " of " & nCount 
end sub

And here's the code for the buttons:

Private Sub cmdFirst_Click()
    On Error Resume Next
    DoCmd.GoToRecord , "", acFirst
End Sub
Private Sub cmdPrevious_Click()
    On Error Resume Next
    DoCmd.GoToRecord , "", acPrevious
End Sub
Private Sub cmdNext_Click()
    On Error Resume Next
    Me.AllowAdditions = False
    DoCmd.GoToRecord , "", acNext
    Me.AllowAdditions = True
End Sub
Private Sub cmdLast_Click()
    On Error Resume Next
    Me.AllowAdditions = False
    DoCmd.GoToRecord , "", acLast
    Me.AllowAdditions = True
End Sub

[isladogs]

@davegri
That's almost identical to the code I use myself and much better than my original suggestion of DCount

I would just add the code in RED to control the button enabled status

Private Sub Form_Current()
    Dim nCount As Integer, nPosition As Integer
    nCount = Me.Recordset.RecordCount

    If nCount = 0 Then
        MsgBox "No Records exist yet.", vbOKOnly, "  R E S T R I C T I O N  "
        Exit Sub
    End If

    nPosition = Me.CurrentRecord
    Me!txtNavStat = nPosition & " of " & nCount 

  'set buttons enabled by default
   cmdFirst.enabled =True  
   cmdPrev.enabled = True
   cmdNext.enabled = True
   cmdLast.enabled = True

   'disable as appropriate
   If nCount = 1 Then
        cmdFirst.enabled = False
        cmdPrev.enabled = False
       cmdNext.enabled = False
        cmdLast.enabled = False
  ElseIf nPosition = 1 Then       
       cmdFirst.enabled = False
       cmdPrev.enabled = False
   ElseIf nPosition = nCount Then
        cmdLast.enabled = False
        cmdNext.enabled = False
    End If

end sub

Alternatively you can add code to the button click event themselves but its probably more code to do so

[mvaughan737]

Well, I did something wrong. I like what you typed in above, and it kind of works and doesn't work. What I mean is, my buttons (First, Last, Prev, Next) all work good. But, the problem is, when I bring the form up, then type in a search command, let's say.. CHK. The Access counters at the bottom show 12 records that have that Match. But, my box only shows "1 of 2". Now, if I hit any left or right button, then it shows correctly. So the minute I use any record navigation button, then it shows correctly. So, in summary, initially when I do a search, it always says "1 of 2". But, when I type the next button, then it says 2 of 12. Last button, 12 of 12. First button, 1 of 12. The nav buttons work, it is just the initial search that it always says 1 of 2 until I hit a navigation button.

What did I do wrong?

Michael

[Micron]

I thought that except for forms based on tables (or the record is forward only, or a dynaset, or based on a query), the count wouldn't be correct until the entire set was accessed. I also thought that this would occur any time you navigate from one record to another (it would be how Access knows where the beginning and end is, thus enabling the correct function of the built in buttons), in which case that would explain the behaviour.

Methinks that referencing the .CurrentRecord of .RecordsetClone.RecordCount would be the way to go. Referencing the RecordsetClone automatically forces the form to access all of the records; as in nCount = Me.RecordsetClone.RecordCount

[mvaughan737]

Thank you everyone for helping me out. I finally got everything working correctly. Thanks to ssanfu for providing the links above. I actually took that information and combined the two together to get the results that I needed. Now the counters work perfectly, plus I am still able to retain my Pop Up Box for No Match, and the nulling of the navigation buttons when I am at the beginning and the end. Here is what I ended up with:

Private Sub Form_Current()


' Provide a record counter for using with
' custom navigation buttons (when not using
' Access built in navigation)
Dim rst As DAO.Recordset
Dim lngCount As Long
Dim nCount As Integer, nPosition As Integer

nCount = Me.Recordset.RecordCount
nPosition = Me.CurrentRecord
If nCount = 0 Then
MsgBox "No Records match your Entry.", vbInformation, " Whoops! "
Exit Sub
End If

Set rst = Me.RecordsetClone
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

'Show the result of the record count in the text box (txtRecordNo)


Me.txtNavStat = "Record " & Me.CurrentRecord & " of " & lngCount


'set buttons enabled by default
cmdFirst.Enabled = True
cmdPrev.Enabled = True
cmdNext.Enabled = True
cmdLast.Enabled = True
'disable as appropriate
If nCount = 1 Then
cmdFirst.Enabled = False
cmdPrev.Enabled = False
cmdNext.Enabled = False
cmdLast.Enabled = False
ElseIf nPosition = 1 Then
cmdFirst.Enabled = False
cmdPrev.Enabled = False
ElseIf nPosition = nCount Then
cmdLast.Enabled = False
cmdNext.Enabled = False
End If

End Sub

[Micron]

So nCount = Me.RecordsetClone.RecordCount didn't work and you had to create and move through a recordset clone to get it to work??

P.S. you'll be OK with what you've got (integer) as long as you don't load 33,000 records.

[mvaughan737]

Yes, that is correct. It didn't work. But, when I combined the two, it works fine now. My Database won't be that large, so I should be okay. I will only have about 1,500 records at the max.

[Micron]

Interesting. Worked for me.

[isladogs]

I thought that except for forms based on tables (or the record is forward only, or a dynaset, or based on a query), the count wouldn't be correct until the entire set was accessed. I also thought that this would occur any time you navigate from one record to another (it would be how Access knows where the beginning and end is, thus enabling the correct function of the built in buttons), in which case that would explain the behaviour.

Methinks that referencing the .CurrentRecord of .RecordsetClone.RecordCount would be the way to go. Referencing the RecordsetClone automatically forces the form to access all of the records; as in nCount = Me.RecordsetClone.RecordCount

With one exception, all my examples use a table or query so code like '.CurrentRecord of .Recordset.RecordCount' works perfectly
The exception uses a listbox which can be filtered. In that case I use Me.listboxname.ListCount for the total

Just tested using a form based on a query as recordsource with various comboboxes used to filter the recordset
Neither .Recordset.RecordCount nor .RecordsetClone.RecordCount worked until I first selected the last record
Once I added .MoveLast then both .Recordset.RecordCount and .RecordsetClone.RecordCount worked

However I wouldn't use Form_Current to update the list count as that is likely to produce flickering
I've separated out that to a separate function GetListTotal which means it is flicker free

Private Function GetListTotal()

On Error GoTo Err_Handler
    
Dim rst As DAO.Recordset, strRecordsource As String
Set rst = Currentdb.OpenRecordset(strRecordSource, dbOpenSnapshot)

If Not (rst.BOF Or rst.EOF) Then
    rst.MoveLast
    GetListTotal = rst.RecordCount
Else
    GetListTotal = 0
End If

rst.Close
Set rst = Nothing

Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox "Error " & err.Number & " in GetListTotal procedure"
    Resume Exit_Handler
    
End Function

[davegri]

However I wouldn't use Form_Current to update the list count as that is likely to produce flickering
I've separated out that to a separate function GetListTotal which means it is flicker free

Where is the function called from?

[isladogs]

Where is the function called from?

Thought someone might ask that...

Private Sub Form_Current()  Me.lblCount.Caption = "Total records = " & GetListTotal
End Sub

Doing it 'indirectly' like that is flicker free yet putting the rs.Last ...rs.First code into Form_Current direct was unuseable!