Solved

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

Posted on 2014-03-01
10
1,203 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 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 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

690 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