Search Form (1 Viewer)

F

Falcon88

Registered User.
Local time
Today, 15:26
Joined
Nov 4, 2014
Messages
299
Hi all
I have a form to use for search . Because there a hundreds of thousands of records , i prefer to use a listbox than a continous form .

How to use that as like the code that provided by allen browne:
http://allenbrowne.com/ser-62.html
 
isladogs

isladogs

MVP / VIP
Local time
Today, 13:26
Joined
Jan 14, 2017
Messages
18,219
Suggest you use cascading comboboxes instead to make it manageable. For example, see

Grouping & Filtering #1 – Cascading combo boxes example

This is intended as the first in a series of examples showing different ways of filtering and grouping data in Access This example focuses on the use of cascading combo boxes. This method is particularly useful where you have a very large dataset that would be impossibly slow to search using a...
www.access-programmers.co.uk
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,527
This has 10,000 records but you can search all filters almost instantly, so curious if it will work with 100k. It uses a class module and it takes two lines of code to make it work.
Code: 
Public FAYT_List As FindAsYouTypeListBox
Private Sub Form_Load()
  Set FAYT_List = New FindAsYouTypeListBox
  FAYT_List.InitializeList Me.lstAllRecords, Me.txtSearch, , AnywhereInString
End Sub
That is all the code the user has to provide.
 

Attachments

  • Large FAYT.zip
    612.5 KB · Views: 92
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,527
You could consider this by @John Big Booty: https://www.access-programmers.co.uk/forums/threads/dynamically-search-multiple-fields.188663/
Click to expand...
I would caution anyone from seriously considering that solution. This is the query the OP had to make to do this. I think I would cut my own throat first, and it is only a one time only solution. IMO there are so many better ways to do that.

Code: 
SELECT tbl_wine.wine,
       tbl_grape.grape,
       tbl_vintage.vintage,
       tbl_winery.winery,
       tbl_region.region,
       tbl_uncountry.countryname,
       tbl_states.state,
       tbl_title.title,
       tbl_winemake.fname,
       tbl_winemake.sname,
       tbl_wine.wineid,
       tbl_winery.wurl,
       tbl_wine.colour,
       tbl_wine.flat,
       tbl_wine.closure
FROM   tbl_states
       INNER JOIN (((((((tbl_wine
                         INNER JOIN tbl_grape
                                 ON tbl_wine.grapeid = tbl_grape.grapeid)
                        INNER JOIN tbl_vintage
                                ON tbl_wine.vintageid = tbl_vintage.vintageid)
                       INNER JOIN tbl_winery
                               ON tbl_wine.wineryid = tbl_winery.wineryid)
                      INNER JOIN tbl_region
                              ON tbl_wine.regionid = tbl_region.regionid)
                     INNER JOIN tbl_uncountry
                             ON tbl_winery.countryid = tbl_uncountry.countryid)
                    INNER JOIN tbl_winemake
                            ON tbl_wine.winemakeid = tbl_winemake.winemakeid)
                   INNER JOIN tbl_title
                           ON tbl_winemake.titleid = tbl_title.titleid)
               ON tbl_states.stateid = tbl_region.stateid
WHERE  ( ( ( tbl_wine.wine ) LIKE "*" & [forms] ! [frm_searchmulti] ! [srchtext]
                                  & "*"
                )
         AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                        [frame50] &
                                        "*" )
         AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                      [frame43] &
                                      "*"
             )
         AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                         [frame36]
                                         & "*"
             ) )
        OR ( ( ( tbl_grape.grape ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                        [srchtext]
                                        &
                                        "*"
             )
             AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [frame50] &
                                            "*" )
             AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [frame43]
                                          &
                                          "*"
                 )
             AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                             [frame36]
                                             & "*"
                 ) )
        OR ( ( ( tbl_vintage.vintage ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [srchtext]
                                            &
                                                  "*" )
             AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [frame50] &
                                            "*" )
             AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [frame43]
                                          &
                                          "*"
                 )
             AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                             [frame36]
                                             & "*"
                 ) )
        OR ( ( ( tbl_winery.winery ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [srchtext] &
                                                "*" )
             AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [frame50] &
                                            "*" )
             AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [frame43]
                                          &
                                          "*"
                 )
             AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                             [frame36]
                                             & "*"
                 ) )
        OR ( ( ( tbl_region.region ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [srchtext] &
                                                "*" )
             AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [frame50] &
                                            "*" )
             AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [frame43]
                                          &
                                          "*"
                 )
             AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                             [frame36]
                                             & "*"
                 ) )
        OR ( ( ( tbl_uncountry.countryname ) LIKE
               "*" & [forms] ! [frm_searchmulti] !
                     [srchtext] & "*" )
             AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [frame50] &
                                            "*" )
             AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [frame43]
                                          &
                                          "*"
                 )
             AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                             [frame36]
                                             & "*"
                 ) )
        OR ( ( ( tbl_states.state ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                         [srchtext]
                                         &
                                         "*"
             )
             AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [frame50] &
                                            "*" )
             AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [frame43]
                                          &
                                          "*"
                 )
             AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                             [frame36]
                                             & "*"
                 ) )
        OR ( ( ( tbl_winemake.fname ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                           [srchtext] &
                                                 "*" )
             AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [frame50] &
                                            "*" )
             AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [frame43]
                                          &
                                          "*"
                 )
             AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                             [frame36]
                                             & "*"
                 ) )
        OR ( ( ( tbl_winemake.sname ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                           [srchtext] &
                                                 "*" )
             AND ( ( tbl_wine.colour ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                            [frame50] &
                                            "*" )
             AND ( ( tbl_wine.flat ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                          [frame43]
                                          &
                                          "*"
                 )
             AND ( ( tbl_wine.closure ) LIKE "*" & [forms] ! [frm_searchmulti] !
                                             [frame36]
                                             & "*"
                 ) );
 
A

AccessBlaster

Registered User.
Local time
Today, 05:26
Joined
May 22, 2010
Messages
5,948
I have employed both John's and Allen's solutions on a few projects with more than 40,000 rows of data. Yes I heavy modified both of them. There are no canned solutions that fit all situations.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,527
There are no canned solutions that fit all situations.
Click to expand...
The solution I provided is about as far as you can get to a universal canned solution. It works on any listbox with a single line of code to implement. Additional bells and whistles are provided to do more functionality. The purpose it to provide this functionality without having to do overly complicated queries and requiring "heavy modification."
 
Isaac

Isaac

Lifelong Learner
Local time
Today, 05:26
Joined
Mar 14, 2017
Messages
8,777
MajP, that's pretty cool.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:26
Joined
May 21, 2018
Messages
8,527
@pisorsisaac@gmail.co
There are similar approaches used here you may find of interest

Developing Custom Classes to Simulate User Controls

Access OOP examples wanted Some people have shown interest in custom classes lately, and was asked for some more examples. I use custom classes extensively, but the most important use in Access is the ability to extend control functionality and basically simulate building a user control. Once...
www.access-programmers.co.uk
 
You must log in or register to reply here.

Similar threads

T
Highlight matches
Replies
2
Views
275
tihmir
T
E
Solved Listbox search not working with aggregated query
Replies
7
Views
262
elannesse
E
M
Duplicate main form and subforms
Replies
14
Views
1,089
Pat Hartman
P
M
Solved Search and find records on a continuous subform
2
Replies
33
Views
1,192
Gasman
Gasman
F
Search form
Replies
4
Views
597
bastanu
bastanu

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom