Solved

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

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

830 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