Solved

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

Posted on 2014-03-01
10
1,166 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 49

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

856 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