Convert a form into a parameter form.

[anthgav]

Hi



I have a form called 'frmProduct' which looks up data from my company's databases and is read only information. 'frmProduct' has a sub form called 'frmProduct subform' and this form is linked to an Access database table called 'tblProduct' which has editable fields.
'frmProduct' uses a select query called 'qryProductData' as it's record source to pull in about 70,000 lines of data when the form is opened. 'frmProduct' has a search box called 'productSearchBox' and we can enter a product code and search for information which populates 'frmProduct' and 'frmProduct subform'.

The downsides to this setup are:
When the form opens it takes quite a while for all 70,000 lines of info to load in the background before it opens.
When we enter a product code in 'productSearchBox' the information isn't live, it only provides info from when the form was last opened up.

What I want to achieve:
I'd like to know if I can convert this form to use a parameter form/query so that every time I search for a product in 'productSearchBox' the parameter query runs, only loads the information I require and populates the form with it.
I've experimented with adding 'productSearchBox' into the criteria of the query to make it a parameter query as a start but that makes all of 'frmProduct' boxes disappear leaving only 'productSearchBox' visible on the form.

Does anyone have any good references on how to achieve this or advise on this. I've done a lot of searching for a solution but nothing seems to help specifically with this scenario.

Thanks

[orange]

See this tutorial and Search example by Allen Browne. It should help with concepts and approach.
Good luck.

[Micron]

70,000 records isn't all that many really. Speed issues can also be network related, so if the performance of other applications is also slow, then that may mean that there's not much you can do. Obviously there isn't anything you can do about indexes in the source data, but if you don't need every source field in the form, don't base the form on a table. The search form link may help, but there is another possibility if performance remains slow regardless of filtering records for your form. That would be to load a copy of the data locally if it's not overly volatile. I did this once for a complex situation and speed increase was dramatic. Windows Task Scheduler ran updates on the local data at night, which was only a matter of expediency. If that failed, a manual update only took 2 or 3 minutes and we were good for the rest of the day.

[anthgav]

Thanks for the replies guys. Also my query has lot's of joins from various tables and databases which may also make it slow. I understand your suggestion Micron but I would really like to implement this so that each search brings live data. I'm going to attempt to make a search form using the method or something similar to what you suggested orange. I will no doubt be asking questions going forward while I'm creating this. It may take me some time to work out and I'm breaking up for Xmas in a few days for 2 weeks off so I may not reply for a while.

[orange]

Instead of loading all of your records, perhaps design a form from which you can select criteria. Then use that criteria in a query to get the subset of records you need. That is, base your form population(recordsource) on a query with your criteria.

I think readers wold like to know more about

my query has lot's of joins from various tables and databases

Have a good holiday.