Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 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

24 Experts available now in Live!

Get 1:1 Help Now