Solved

Don't need the whole table in continuous form on form load

Posted on 2013-11-10
5
355 Views
Last Modified: 2013-11-11
Using  Access 2013

I am using a  continuous form that loads up a table with several thousand records.

In practice, the user never needs to see all the records at once.    They will access the form and enter criteria in the appropriate textboxes above the related fields.  The contents of these textboxes are used to build criteria for a filter to display record(s).
Nothing new here, I'm sure.

When the form is accessed, the record source for the form is the entire table.   I know that this is not optimal.    

I could create an filter that would result in no records displayed  when the form is accessed first.   When the user enters search criteria, that  filter would be replaced by the filter created by the user.  My gut tells me that this is an incorrect approach.

I would appreciate some advice.

Thanks
0
Comment
Question by:peispud
5 Comments
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39636904
I tend to create a second form that allows the user to select the criteria for the records that they want to display.  This is assuming that users have the potential to filter on several columns.  One of the main reasons that I do this is I have seen users scroll though a list of thousands of entries to find the one they are looking for and complain that the system is not efficient.

Tom
0
 

Expert Comment

by:Al_K
ID: 39636948
I think it's the correct approach. When you load the form you shouldn't load all the records. Have some controls to enter the search criteria and a button to submit them. Opening a form to set the criteria, or a dialog which asks criteria before the form opens, is also an acceptable way, but only if the criteria are changed rarely and you want to have a clean form with the records only. If the user is going to change the criteria many times then I suggest that you have them in the same form as you said, to be easier for the user to change them quickly.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39637048
<<My gut tells me that this is an incorrect approach.>>

 Yes and no.  Certainly you don't want to display all the records unless you have to, but if this is a JET backend, it's pretty smart about how many records it really needs to fetch.   In many cases, it will stop populating the recordset and display what it has if the recordset is large.

 However there is a better  way to do what you want, which is to have the forms DataEntry property set to true by default.   In this case, no recordset processing takes place.

 You could also have the forms recordsource set to:

  .....   WHERE 1=0

  This gives you a blank recordset.  Then modify the forms recordset property as needed.

You also could use the Filter property the same way.  Both of these methods though have some overhead attached.   But unless you had a considerable number of records, I doubt you'd see a major difference.

Jim.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39637078
I'm in agreement with Jim.  You can have a recordsource with millions of records and the form will load just as quick since only a small subset will be fetched for display.  Now if you try to navigate to the last record, that's when you'll see a delay.  

Now if you did want to keep the continuous form blank until the user enters a criteria, another way is to add a default criteria (using the Default property of your search textbox) using a value that doesn't exist in the recordsource such as "[Enter item to search for]."  Then your recordsource could have a Where condition such as: WHERE NameOfField LIKE Forms!FormName!NameOfSearchTextbox

In the above scenario, you would also have a Me.Requery in the AfterUpdate event of the textbox.  To clear the textbox default value when you enter it, just add the following in its OnEnter event property:  Me.NameOfTextbox=""

Ron
0
 

Author Closing Comment

by:peispud
ID: 39638396
Great feedback.  I will use the Data Entry property to instead of relying on filters. Excellent suggestion!

Thanks
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now