The approach you are using is too inefficient for use with 500k records.
Dump the query. Instead, generate a SQL statement from only the boxes that
have a value, and assign it to the RecordSource of the search form.
The example below shows how to build the WHERE clause dynamically. Each case
tacks " AND " on the end ready for the next one, and the trailing " AND " is
chopped off at the end. This WHERE clause is then concatenated with the stub
(typically the SELECT clause) and the tail (typically the ORDER BY clause)
to form the complete SQL statement. After saving any edits in progress, this
string is then assigned to the RecordSource property of the form, so it
contains only records that match.
Don't forget that Text fields need quote delimiters around the literal
values, and date fields need the # delimiter. The example shows one of each,
but of course you can add as many as you need (theoretically up to 50
fields, or 99 in later versions of Access.)
If you have 500k records, you will want to do some tweaking to ensure this
is efficient:
1. You do not want to requery in the AfterUpdate event of each control, so I
assume you have a Search button to click to perform the search.
2. Make sure you have an index on any of the fields that will be used for
searching or sorting. (You mention a combo: if you have a relation to
another table, with referential integrity enforced, Access creates a hidden
index on the foreign key, so you do NOT index it manually as well.)
3. You probably want to initialize the form with no records, so you will
save it with the RecordSource set to a criteria that calculates for False
for all records, e.g.:
SELECT * FROM [Table1] WHERE (False) ORDER BY [SomeField];"
That should get you a search form that loads instantly, and shows search
results in a few seconds at most.
Example code for the Click event procedure of your cmdSearch command button:
Private Sub cmdSearch_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT * FROM [Table1]"
Const strcTail = " ORDER BY [SomeField];"
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Not IsNull(Me.cbo1) Then 'Number field example.
strSql = strSql & "([ID] = " & Me.cbo1 & ") AND "
End If
If Not IsNull(Me.cbo2) Then 'Text field example.
strSql = strSql & "([City] = """ & Me.cbo2 & """) AND "
End If
If Not IsNull(Me.txt3) Then 'Date field example
strSql = strSql & "([EventDate] = " & Format(Me.txt3, strcJetDate) &
") AND "
End If
lngLen = Len(strSql) - 5 'Without trailing " AND "
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & strcTail
Else
strSql = strcStub & strcTail
End If
If Me.Dirty Then 'Save First.
Me.Dirty = False
End If
'Assign the query string.
Me.RecordSource = strSql
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Steve said:
I tried finalizing my QBF by entering the applicable version of...
Forms![Form Name]![Control Name] OR Forms![Form Name]![Control Name] Is
Null
...in the query field criteria row but Access 2000 freezes once I save the
query. I was attempting to use 10 combo boxes on a form, so the various OR
statements in the query were seemingly endless when I went back in via
design
view. The original query is based on 6 different tables, and about 500,000
records are present when no criteria is set. In addition, Access would
also
freeze when selecting criteria from the form and pressing the search
command
button.
I should mention that in both instances, it was not immediately apparent
that Access was frozen. I came to this conclusion after waiting as much as
10
minutes for something to display. Ctrl-Alt-Delete and Access showed as Not
Responding.
Am I asking too much of the program or just going about it a bad way?
