Solved

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

Posted on 2013-11-10
5
367 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
[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
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

738 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