one of my favorite search solutions is set up as follows:
create an unbound form, and add a textbox control to it and two command
buttons. name the form frmSearch, the textbox txtLoc, and the command
buttons cmdFilter and cmdReset. create another form named frmLocations. in
Design view, click on the RecordSource property line and click the Build
(...) button at the right which opens to a view that looks like query design
view. add the locations table and add the fields you want to the grid. set
criteria on the location field, as
Like "*" & [Forms]![frmSearch]![txtLoc] & "*" or
[Forms]![frmSearch]![txtLoc] Is Null
the above goes all on one line, regardless of line wrap in this post. close
and save.
in frmSearch, add a subform control and set its' SourceObject property to
frmLocations. add code to cmdFilter, as
Me.Requery
add code to cmdReset, as
Me!txtLoc = Null
Me.Requery
that's it. when you open frmSearch, the subform will be populated with all
records from the locations table. when you enter a value in txtLoc and click
cmdFilter, the subform's recordset will be requeried to show only the
records matching the value in txtLoc. when you click cmdReset, the subform
will again show all records in the locations table.
note a couple things: if you don't ever want all 4000 records to populate
the subform at one time, then use the following query criteria instead of
the above, as
Like "*" & [Forms]![frmSearch]![txtLoc] & "*"
the subform will be empty when frmSearch is opened, and when you click
cmdReset. the other nice thing about this kind of search form is that you
can use mulitple textboxes and/or comboboxes in the main form to search,
allowing records to be filtered by as few or as many criteria as needed at
any given time.
hth
"suek" <ci*****@yahoo.com.auwrote in message
news:11**********************@e9g2000prf.googlegro ups.com...
I have a table with over 4000 records to search upon, and the users
don't like a combo box.
So what I have been trying to do for the last twelve hours is do some
code to get a text box to search. I am learning fast, but I really
don't know how to do it, and would appreciate some help.
So far I have got to this:
Private Sub btnSearch_Click()
Dim strFindLocation As String
Dim strSearch As String
Dim intAsset number As Integer
Dim lngsngkm as Long
'Has the user entered anything?.
If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
MsgBox "Please enter a value to search on, eg, location",
vbOKOnly, "Put text in the box!"
Me![txtsearch].SetFocus
Exit Sub
'Performs the search based upon the input into txtSearch
Set strSearch = (Me![txtsearch])
If Not IsNull(Me![txtsearch]) Then
(THIS IS WHERE I AM STUCK)
'select records like txtSearch from
'location name in location table
((CANNOT FIGURE OUT AN SQL WHICH WORKS
'and put them into a subform for the user to
'select the correct one (include intAssetNumber and sngkm in
subform)
CANNOT GET THE RESULTING RECORDS TO RUN AS A QUERY INTO ANOTHER FORM
'correct location = strFindLocation, correct asset number =
intAssNum
'correct km = lngSngKm
'from strFindLocation then find Asset Number and km location
'and populate new job details form
Any help greatly appreciated.
End If
End Sub
