Need to create a search facility

[suek]

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

[missinglinq via AccessMonster.com]

Re: Need to create a search facility

Never have figured out why people would rather type in an entire search term,
then click on a command button, instead of starting to type a search string
into a combobox, having it bring up the search term, often before they're
thru typing it, and then simply hitting <Enter>. Makes no sense! But just
because your users won't use comboboxes doesn't mean you can't!

Place a combo box on the form. When the Combobox Wizard comes up:

Select "Find a record on my form based on the value I've selected..."
Next
Select the appropriate identifying field to appear in the Combo box
Next
Size the Combo box column
Next
Name the combo box
Finish

Now, go into the code editor and find the Update event for your combobox.
Copy the relevent search code and paste it in after your code to check that
the search box isn't empty. Once you've checked that the search box routine
works, delete your combobox.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200708/1

[tina]

Re: Need to create a search facility

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" <cirsoid@yahoo. com.auwrote in message
news:1186226128 .503292.159070@ e9g2000prf.goog legroups.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
>

[Salad]

Re: Need to create a search facility

missinglinq via AccessMonster.c om wrote:

Never have figured out why people would rather type in an entire search term,
then click on a command button, instead of starting to type a search string
into a combobox, having it bring up the search term, often before they're
thru typing it, and then simply hitting <Enter>. Makes no sense! But just
because your users won't use comboboxes doesn't mean you can't!

It depends. 4K of records is a bunch of rows. Datatables grow and
what's now 4K may soon be 5-10-15K. I'd hate to be the user that has to
go thru muck like that.

Anyway, to the OP. You could have a text box to search. Maybe a
dropdown of the field to search and a text box of the value. This is an
example of a search from the AfterUpdate event of the value.
Dim strSearch As String
Dim rst As Recordset
strSearch = "FieldNameToSea rch = """ & Me.TextSearchVa l & """
set rst = Me.recordsetclo ne
rst.findfirst strSearch
if not rst.NoMatch then
Me.Bookmark = rst.Bookmark
Else
msgbox "Not Found. Try again"
Endif
set rst = Nothing

Search help for properties/methods you aren't familiar with.

In my columnar continuous forms I create a text box over each column. I
use the same general logic above using a generic find routine except I
use the word "Like" instead of the = sign when I search for the record
if its a character string. Then I have a routine that uses the F3 key
to skip through the records. Of course I permit them to search for
"starting with" or "contains" when using a text search.

>
Place a combo box on the form. When the Combobox Wizard comes up:
>
Select "Find a record on my form based on the value I've selected..."
Next
Select the appropriate identifying field to appear in the Combo box
Next
Size the Combo box column
Next
Name the combo box
Finish
>
Now, go into the code editor and find the Update event for your combobox.
Copy the relevent search code and paste it in after your code to check that
the search box isn't empty. Once you've checked that the search box routine
works, delete your combobox.
>