?
Solved

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

Posted on 2014-03-01
10
Medium Priority
?
1,220 Views
Last Modified: 2014-03-11
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.
0
Comment
Question by:BrianThor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 39898168
So when does the problem occur?
0
 
LVL 85
ID: 39898390
How do you get the record count now? Can you show the code, or explain where.how 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.
0
 

Author Comment

by:BrianThor
ID: 39898502
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.

Before
After
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 85
ID: 39898614
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:

Me.YourSubformControl.Form.SetFocus
Me.YourSubformControl.Form.SomeControl.SetFocus
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39899691
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.

/gustav
0
 

Accepted Solution

by:
BrianThor earned 0 total points
ID: 39911430
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.

Brian
0
 
LVL 24

Expert Comment

by:Eirman
ID: 39912381
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.

Thanks
0
 

Author Comment

by:BrianThor
ID: 39912399
Eirman,

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.

Brian
0
 
LVL 24

Expert Comment

by:Eirman
ID: 39912493
Thanks Brian ... Copied & Pasted!
0
 

Author Closing Comment

by:BrianThor
ID: 39919990
This was the only solution I found that resolved my issue.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question