Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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

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
peispud
Asked:
peispud
1 Solution
 
ButlerTechnologyCommented:
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
 
Al_KCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
IrogSintaCommented:
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
 
peispudAuthor Commented:
Great feedback.  I will use the Data Entry property to instead of relying on filters. Excellent suggestion!

Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now