Record count does not show up between navigation buttons on subform.

I am designing a form which has an embedded subform. The main form does not have a recordset. Its only purpose is to provide a few buttons for actions. The subform is a datasheet. The recordset is a linked table to MS SQL Server. The subform has code in the Load event which modifies the recordset's WHERE clause based on the value of a TempVar. This works fine but it is a clue, because when this code is commented out the problem doesn't exist. If I open the subform directly the NavigationButtons show the correct record count and position. If I move to a new record using either VBA or just by clicking on a row the NavigationButtons show the correct record count and position in the recordset. If I requery the subform from VBA or by clicking Refresh All on the ribbon the NavigationButtons show the correct record count and position. If I change the subform from a datasheet to a continuous form the NavigationButtons show the correct record count and position.

This all makes me believe that I should be able to program some event to do one of the above actions. The problem is that I haven't been successful in doing so. Can anyone explain why this happens and more importantly how to resolve it?

Thanks in advance.
Brian ThorPresidentAsked:
Who is Participating?
Brian ThorConnect With a Mentor PresidentAuthor Commented:
I found this solution (edited for simplicity).

1. In the subform:

Private Sub Form_Load()
     Me.Filter = ""
End Sub

2. In the parent form:

Private Sub Form_Current()
    Me.fsubForm.Form.FilterOn = True
    Me.fsubForm.Form.FilterOn = False
End Sub

Thanks to all who contributed.

So when does the problem occur?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How do you get the record count now? Can you show the code, or explain you're doing this?

The most reliable way to get Record Counts from SQL Server is by using this sort of code:

Dim rst As DAO.Recordset
Set rst  = Currentdb.OpenRecordset"(SELECT COUNT(*) As RecCount FROM YourTable WHERE <Your Criteria>")

Me.txRecordCount = rst("RecCount")

This works every time, assuming you get <Your Criteria> right.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Brian ThorPresidentAuthor Commented:
The problem occurs when the form is first opened. I should have been more clear that the subform is bound to its recordset. Here are screenshots of when the form first opens and then after any record selection is done. Note the difference in the NavigationButtons.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I see ...

That's the nature of subforms, especially when dealing with SQL Server. Access is intelligent enough to only fetch the records needed to display until the user interacts with the form further, so you'd have to somehow simulate that - for example, set the Focus to the Subform, and to a control on the subform:

Gustav BrockCIOCommented:
I see the same with SQL Server tables. Access just doesn't do a record count on such tables until needed.
If the user needs the count, he/she can push the go-to-last-record button.

EirmanChief Operations ManagerCommented:
Hi BrianThor

Before this question disappears, and the useful solution you found disappears into the ether, could you, if possible, give us a link to where you found the solution.

Brian ThorPresidentAuthor Commented:

The solution was of my own creation. I knew from the work I had done before posting my question that it was just a matter of forcing Access to evaluate the recordset in response to some event. I went through a process of figuring which events fired and in what order. The parent form Current event was the last one and because the detail section didn't contain anything but a subform I expected it would only fire when the form opened. I kept experimenting with methods which would do something to the subform recordset until I stumbled into the filter idea. Forcing the empty filter on gave me the result I wanted. I just turn it back off because there isn't actually a filter.

EirmanChief Operations ManagerCommented:
Thanks Brian ... Copied & Pasted!
Brian ThorPresidentAuthor Commented:
This was the only solution I found that resolved my issue.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.