John-
Thanks a lot.
Best Regards,
Kevin
在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2016年12月20日 21:52写道：
Kevin-
You could try SELECT TOP 20 … ORDER BY
.. but keep in mind that the database is going to have to search the entire table first, and then return the top 20. If your ORDER BY uses the Primary Key of the table, that might be a skosh faster. But then your "next page" code won't work anymore because there will only be 20 records. To get to the "next" page, you would need:
SELECT TOP 20 ..
WHERE Pkey > <PKey value of the 20th record>ORDER BY PKey
You would need to keep modifying the Record Source for each "page", and each query might run as long as fetching all the records in the first place.
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On Dec 20, 2016, at 2:10 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-It works great! Thanks. Now comes to another problem. How can I limit the matched records to 20 at first after running the name search.Best Regards,Kevin
Date: 2016-12-20 20:54Subject: Re: [MS_AccessPros] How can I display the searching result page by page?
That code needs to be in a "Next Page" command button on the form.
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On Dec 20, 2016, at 1:52 PM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-I simply added the two lines in between like these, after hit the enter, the form got stuck. What shall I do? Thanks.
Private Sub OPName_AfterUpdate()
On Error GoTo ErrHandle
DoCmd.ShowAllRecords
Me.Filter = "OPName Like ""*" & Me.OPName & "*"""
Me.FilterOn = True
Me.OPName.SetFocus
Me.Recordset.AbsolutePosition = Me.Recordset.AbsolutePosition + 20
Me.SelTop = Me.Recordset.AbsolutePosition + 1
ErrExit:
Exit Sub
ErrHandle:
Resume ErrExit
End Sub
Thanks,Best Regards,Kevin
Date: 2016-12-20 19:43Subject: Re: [MS_AccessPros] How can I display the searching result page by page?
That's what I suspected. When you do a Like "*something*", you force the database to do a full table scan. An index won't help at all! After the first "page" loads, you can skip down pages by setting the AbsolutePosition property. Assuming your form is designed to display the first 20 records, you can skip forward like this:
(Code running in the subform.)
Me.Recordset.AbsolutePosition = Me.Recordset.AbsolutePosition + 20
If you find that doesn't actually move the display, you can force it by using the form's SelTop property:
Me.SelTop = Me.Recordset.AbsolutePosition + 1
You add 1 because SelTop starts at 1 and AbsolutePosition starts at 0.
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On Dec 20, 2016, at 2:10 AM, 'zhaoliqingoffice@163.com' zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-Here's the code:
Private Sub OPName_AfterUpdate()
On Error GoTo ErrHandle
DoCmd.ShowAllRecords
Me.Filter = "OPName Like ""*" & Me.OPName & "*"""
Me.FilterOn = True
Me.OPName.SetFocus
ErrExit:
Exit Sub
ErrHandle:
Resume ErrExit
End Sub
Thanks.Best Regards,Kevin
Date: 2016-12-20 06:39Subject: Re: [MS_AccessPros] How can I display the searching result page by page?
Probably a ton of examples online, but it would be more useful to know the filter you are trying to apply. What does your code look like that responds to what the user types in txtName Search?
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On Dec 19, 2016, at 10:50 PM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
Is there any online examples with the indexes applied in code? Thanks.
Best Regards,
Kevin
在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2016年12月19日 23:23写道：
Indexes are still important, even if the data is in linked tables.
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On Dec 19, 2016, at 3:55 PM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
I got it. Thanks.
The backend is stored in the sever, this may take some time to load from the frontend.
Best Regards,
Kevin
在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2016年12月19日 22:20写道：
How are you applying the filter, and is the field you're filtering indexed? The key is to really speed up the search to that it doesn't matter if there are thousands of records. You could do "pages", but it's not likely to be any faster (it would still need to find all the records before displaying the first 20), and it would take some fancy code to manipulate the recordset.
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On Dec 19, 2016, at 2:53 PM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John-
For example, if I type"abc" in the txtNameSearch, after hitting the enter, there might be thousands of matched records displaying in the subResult, which makes the searching process slow down. Is it possible to display 20 records at a time? and if I want see the rest, I can simply hit a command button called "NextPage"...and It is better to show How many pages left somewhere in form. Thanks.
Best Regards,
Kevin
在 "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>，2016年12月19日 20:28写道：
Please explain what you mean by a "page".
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On Dec 19, 2016, at 1:10 PM, zhaoliqingoffice zhaoliqingoffice@163.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Dear All,
I have a textbox in a form called txtNameSearch, and a subform called subResult to display the searching result. My question is how I can display the searching result page by page, instead of showing all results at once? Thanks in advance!
Best Regards,
Kevin
__._,_.___
Posted by: zhaoliqingoffice <zhaoliqingoffice@163.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (17) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar