Track number of records in a query

[brharrii]

I have a form based on a query. I've disabled "Navigation Buttons" on the form and am trying to recreate their functionality in a little more user friendly way. I've created next / previous record buttons and have those working great.

I'm now trying to re-create the record counter / tracker. I'd like to setup a box that shows which record I'm on (this can simply be based on the order the query returns them, the same way the navigation buttons does it when they are enabled. ), and how many records there are total in the query.

I tried messing around a little bit with =DCount but wasn't able to make it work I suspect because I'm counting the number of records returned in a query, not in a table.

Any way, if anyone has any suggestions, I'd appreciate it,

Thanks!

Bruce

 

[Catalina]

Wouldn't =Count(*) do the trick?

Catalina

 

[brharrii]

That's half of it. I was hoping that there might be a way to indicate which record is currently being viewed as well:


"4 of 26"

For example.

 

[Catalina]

I found the following code a long time ago, credit to the person who wrote it.
It should do what you are looking for.

Put a textbox named 'txtCurrent' on the form.
The following code goes in the ON Current Event of the form.

Private Sub Form_Current()
    Dim sCurrent As Integer
    Dim sTotal As Integer
    
    sCurrent = Me.CurrentRecord
    Me.RecordsetClone.MoveLast
    sTotal = Me.RecordsetClone.RecordCount
    
    If sCurrent > sTotal Then
    sTotal = sTotal + 1
    Else
    End If
     
    Me!txtCurrent = "Record " & sCurrent & " of " & sTotal
    
    'Me!txtTotal = Me.RecordsetClone.RecordCount
End Sub

Upon going to a new record the total increases by 1, although the
record has not been saved yet, this is to prevent of txtCurrent
showing something like: Record 10 of 9.

If you also want to be able to type in a new number into this text box, and then go to that record, as you could previously,
then you will need to use some code in the AfterUpdate event for the control:

Private Sub txtCurrent_AfterUpdate()
    If IsNumeric(Me!txtCurrent) Then
        If CLng(Me!txtCurrent) >= 0 And CLng(Me!txtCurrent) <= Me!txtTotal Then
            Me.RecordsetClone.AbsolutePosition = Me!txtCurrent - 1
            Me.Bookmark = Me.RecordsetClone.Bookmark
        Else
            Me!txtCurrent = Me.CurrentRecord
        End If
    Else
        Me!txtCurrent = Me.CurrentRecord
    End If
End Sub

 

[boblarson]

That's half of it. I was hoping that there might be a way to indicate which record is currently being viewed as well:


"4 of 26"

For example.

Here's a shorter method.

Put this code into a STANDARD MODULE:

Function RecPosition(frm As Form, strPKFieldName As String) As Long
    Dim rst As DAO.Recordset
    Dim lng As Long
    
    Set rst = frm.RecordsetClone
    lng = frm!OrderID
    rst.FindFirst "[" & strPKFieldName & "]=" & lng
    RecPosition = rst.AbsolutePosition + 1
    
    rst.Close
    Set rst = Nothing
End Function

And then, for example, if I had OrderID as the Primary Key, I would use:

="Record " & RecPosition([Form],"OrderID") & " of " & Count(*)

 

[boblarson]

Oh, and the [Form] part in the control source should be kept EXACTLY AS IS SHOWN.

 

[brharrii]

Hey Bob, That works great. Thanks!

The only issue I'm seing is that when I get to the last record and push next it's giving me an error:

Run-time error '94':

Invalid Use of Null

When I debug it gives me:

Option Compare Database
Function RecPosition(frm As Form, strPKFieldName As String) As Long
    Dim rst As DAO.Recordset
    Dim lng As Long
    
    Set rst = frm.RecordsetClone
    [COLOR=red]lng = frm!DiscrepancyID[/COLOR]
    rst.FindFirst "[" & strPKFieldName & "]=" & lng
    RecPosition = rst.AbsolutePosition + 1
    
    rst.Close
    Set rst = Nothing
End Function

DiscrepancyID is the primary key from my table and is declared on the form's query. I can't tell what it's angry at, can you?

Thanks,
Bruce

 

[brharrii]

One more thing:

This form uses a query that pulls all records that have a null in a specific field. Once that field is populated it no longer meets the query's specifications. Is it possible to reduce the total record count when an update to a record excludes it from the query's rules?

thanks again!

Bruce

 

[boblarson]

Well, when you get to the last record and push next, there is no next record as it would be a new record and we know that if the record is new and nothing has been entered, there would not be a DiscrepancyID yet. So, what we do is encapsulate that to handle that.

By the way you should edit the function to fix what I missed. You don't want the actual field name in the function so it is reusable. So the whole fix would be:

Function RecPosition(frm As Form, strPKFieldName As String) As Long
    Dim rst As DAO.Recordset
    Dim lng As Long
    Set rst = frm.RecordsetClone
    lng = Nz(frm(strPKFieldName).value, 0)
    If lng <> 0 Then
        rst.FindFirst "[" & strPKFieldName & "]=" & lng
        RecPosition = rst.AbsolutePosition + 1
    Else
        RecPosition = rst.RecordCount + 1
    End If
    rst.Close
    Set rst = Nothing
End Function

 

[brharrii]

Thanks again Bob

I'm still playing around with the requery idea. I'll post back if I find anything. I'm thinking i need to do the requery after the cmdNext button is pushed to move to the next record. I'm hoping that would also update the record count in my text box.

Thanks again!

 

[boblarson]

Shouldn't need a requery. Your button should just be able to use either>

DoCmd.RunCommand acCmdRecordsMoveNext

or

Me.Recordset.MoveNext

 

[Catalina]

I tried it, and it works perfectly.

Thanks (again) Bob.

Catalina

 

[brharrii]

Not sure if you saw this:

One more thing:

This form uses a query that pulls all records that have a null in a specific field. Once that field is populated it no longer meets the query's specifications. Is it possible to reduce the total record count when an update to a record excludes it from the query's rules?

thanks again!

Bruce

Wouldn't I need to do a requery if as I'm updating records they become excluded from the query specifications? Currently the records still show up after they have been updated even if they no longer match the query.

 

[boblarson]

Not sure if you saw this:




Wouldn't I need to do a requery if as I'm updating records they become excluded from the query specifications? Currently the records still show up after they have been updated even if they no longer match the query.

Ah, yes. But then "Next Record" becomes a little convoluted. Kind of a hard thing for multiple users. You could just do a requery and the record that appears would be the next record, especially if you have a way to make sure nobody else is working that record.

 

[brharrii]

I will be the only one ever working this database so hopefully that simplifies that end of things a little bit.

The concern I have with just doing a requery is, unless I'm mistaken, that it wouldn't move to a new record unless I populate the field in question to exclude it from the query. Sometimes there isn't enough data at the time to populate the field and it needs to be skipped over to be addressed at another time.

Would I be better served by setting up seperate "requery" and "next record" buttons?

 

[boblarson]

My suggestion would be, since only you will work in it, is to have your button to move to the next record but it can check to see if all fields are complete. If it is, do the requery. If not, do the move next.

 

[brharrii]

Thanks Bob!

 

[brharrii]

I think I found a simpler way to do this. A friend of mine suggest that I simply put:

[FONT=Calibri][FONT=Calibri]=[CurrentRecord] & " of " & IIf([NewRecord],[CurrentRecord],Count([DiscrepancyID]))[/FONT]
[/FONT]

I put this in an unbound text box in the header of my form and it seems to work brilliantly.

I thought I'd message back and let you guys know in case you're interested

thanks again!