Selasa, 20 Desember 2016

Re: Re: [MS_AccessPros] How can I display the searching result page by page?

 

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:43
Subject: Re: [MS_AccessPros] How can I display the searching result page by page?
 

Kevin-


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, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building 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:39
Subject: Re: [MS_AccessPros] How can I display the searching result page by page?

Kevin-


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, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building 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写道：



Kevin-


Indexes are still important, even if the data is in linked tables.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building 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写道：



Kevin-


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, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building 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写道：



Kevin-


Please explain what you mean by a "page".

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building 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@163.com" <zhaoliqingoffice@163.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (13)
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.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)