Search Form

[Falcon88]

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]

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]

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.

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.

 

[AccessBlaster]

You could consider this by John Big Booty: https://www.access-programmers.co.uk/forums/threads/dynamically-search-multiple-fields.188663/

 

[MajP]

You could consider this by @John Big Booty: https://www.access-programmers.co.uk/forums/threads/dynamically-search-multiple-fields.188663/

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.

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]
                                             & "*"
                 ) );

 

[AccessBlaster]

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]

There are no canned solutions that fit all situations.

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]

MajP, that's pretty cool.

 

[MajP]

@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