How do I create a search form using a query (not using filters)

[Dante]

I am working in MsAccess 2003 (though I save my Databases in Access 2000
format). I read the Allen Browne post on the search form using a filter, and
it was good. However, I started getting the error of "There isn't enough
memory to perform this operation. Close unneeded programs and try the
operation again". I have a lot of records (4,200+) and more will be inputted
into it. Is there a way to create a search form that will just query the
results and list in a list box?

It's a database that I keep for a library of books.

I have the following fields: Title, Copyright, Printing, Additional Data,
Type, Category and Box #.

Thanks!

 

[Pete D.]

4200 records is not alot, might post the code that you are using for the
search. Sounds like you have an error in it.

 

[Ken Snell \(MVP\)]

I have a sample database that shows how to build an SQL string's WHERE
clause based on values in various controls on a form:

Building SQL string based on values entered into controls
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

You could use a setup similar to this to construct the SQL statement for
your listbox.

 

[Dante]

Thanks Pete. I agree that 4,200+ is not a lot. However, I was getting that
error. I went to the Microsoft website and they had a page where there was a
module code to use, which seems to work. I've not had that message again,
however I'm still having trouble with this form. My code is below.

I would like to use SQL or a Query, as opposed to using a filter though.
But if the filter works, then that's fine.


Private Sub cmdSearch_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.txtTitle) Then
strWhere = strWhere & "([Title] Like ""*" & Me.txtTitle & "*"") AND "
End If

If Not IsNull(Me.txtCopy) Then
strWhere = strWhere & "([Copyright] Like ""*" & Me.txtCopy & "*"")
AND "
End If

If Not IsNull(Me.txtPrint) Then
strWhere = strWhere & "([Printing] Like ""*" & Me.txtPrint & "*"")
AND "
End If

If Not IsNull(Me.txtAddData) Then
strWhere = strWhere & "([AddData] Like ""*" & Me.txtAddData & "*"")
AND "
End If


If Not IsNull(Me.AddData) Then
strWhere = strWhere & "([AddData] Like ""*" & Me.txtAddData & "*"")
AND "
End If

If Not IsNull(Me.txtType) Then
strWhere = strWhere & "([Type] Like ""*" & Me.txtType & "*"") AND "
End If

If Not IsNull(Me.txtCat) Then
strWhere = strWhere & "([Cat] Like ""*" & Me.txtCat & "*"") AND "
End If

If Not IsNull(Me.txtBox) Then
strWhere = strWhere & "([Box] Like ""*" & Me.txtBox & "*"") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No text indicated to search for", vbInformation, "Error"
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub