Multi Field search Form

[ged]

I have the following Tables and a search engine form to suite but its
slow as hell!!!

Table 1: (2.7million items)
FirstName
LastName
Address(Street)
Customer(Boolean)
PCID (REL)

Table2:
PCId (REL)
Postcode
SuburbName
State

Now the Form has a subform showing the full listing of Table1 and
whatever matching from Table2. The main form also has 6 text boxes
that are able to be used to narrow the search using Like Queries
linked to the text boxes value and a requery button to fire this off

My problem is 2fold at loading time that the load time for the Subform
is incredible about 3 minutes but then if the window losses focus then
the load time near doubles(Im not sure if that is due to system
resources but hell it takes ages if you open other windows whilst it
loads)

1. Can I get the Initial querey of the subform to only happen on the
search button press to kill the forms load time

2. Is there any way to exclude the like statements in the query if
nothing is added to the textbox if(Form.Textbox.value <> "",Like "*" &
Form.Textbox.value & "*", NO FRIGGIN CRITERIA) oh i have tried many
combos but hell non seem to work?? Should this be done in code and not
within a querey??? if so please help my SQL is like noahs pair of
hookers on the ark
3. When Loading a continuous form that is so long is there any way to
limit the amount of the form that is loaded so I could like say query
only get first 100k records Search for next 100k once person starts
scrolling???

4. Am I doing this the total wrong way about or if this does not make
sense then Slap me and ill re-explain

Any help would be appreciated !! I am currently putting up with the
load times but really want some decent performance from it !!!

 

[Allen Browne]

Take a look at:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

It contains a downloadable sample datbase that illustrates how to create the
search form, using only the boxes where the user actually entered something.
It loads almost instantaneously, and (depending on indexes and pattern
matches) can returns results in seconds from a table with hundreds of
thousands of clients.

To suppress all records from Table1 until the user enters some criteria, you
can set the form's filter so no records match, e.g.:
Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

The Filter string is just a WHERE clause.
A WHERE clause is an expression that is True or False.
So, setting the Filter to "(False)" is a condition that nothing matches, and
so no records load.

 

[Marshall Barton]

I have the following Tables and a search engine form to suite but its
slow as hell!!!

Table 1: (2.7million items)
FirstName
LastName
Address(Street)
Customer(Boolean)
PCID (REL)

Table2:
PCId (REL)
Postcode
SuburbName
State

Now the Form has a subform showing the full listing of Table1 and
whatever matching from Table2. The main form also has 6 text boxes
that are able to be used to narrow the search using Like Queries
linked to the text boxes value and a requery button to fire this off

My problem is 2fold at loading time that the load time for the Subform
is incredible about 3 minutes but then if the window losses focus then
the load time near doubles(Im not sure if that is due to system
resources but hell it takes ages if you open other windows whilst it
loads)

1. Can I get the Initial querey of the subform to only happen on the
search button press to kill the forms load time

2. Is there any way to exclude the like statements in the query if
nothing is added to the textbox if(Form.Textbox.value <> "",Like "*" &
Form.Textbox.value & "*", NO FRIGGIN CRITERIA) oh i have tried many
combos but hell non seem to work?? Should this be done in code and not
within a querey??? if so please help my SQL is like noahs pair of
hookers on the ark
3. When Loading a continuous form that is so long is there any way to
limit the amount of the form that is loaded so I could like say query
only get first 100k records Search for next 100k once person starts
scrolling???

4. Am I doing this the total wrong way about or if this does not make
sense then Slap me and ill re-explain

Your best bet is to remove the form's Record Source. Then
after a meaningful set of criteria is specified, construct
an SQL statment with just the specified criteria and set the
form's RecordSource property to the SQL statement. Take a
look at the Search Code section at
http://allenbrowne.com/ser-62.html
and see if it makes a big enough difference.