Solved

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

Posted on 2014-03-01
10
1,177 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 84
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 84
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 50

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 23

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 23

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 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