473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Need to create a search facility

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

Aug 4 '07 #1
3 3947
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.com
http://www.accessmonster.com/Uwe/For...ccess/200708/1

Aug 4 '07 #2
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

Aug 4 '07 #3
missinglinq via AccessMonster.com 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 = "FieldNameToSearch = """ & Me.TextSearchVal & """
set rst = Me.recordsetclone
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.
Aug 4 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

21
by: Chris Reedy | last post by:
For everyone - Apologies for the length of this message. If you don't want to look at the long example, you can skip to the end of the message. And for the Python gurus among you, if you can...
4
by: Speaking Clock | last post by:
Hi, I have recently taken over the responsibility for a medium sized .org website that has been written entirely in .asp. Because there is a highly restrictive proprietary Content Management...
4
by: KiwiBrian | last post by:
What do you recommend for an application I can host on a web site that can provide a site search facility? I thought I read that Google had a new aplication for this but I can't find any reference...
3
by: Rajesh Sharma | last post by:
Dear all, I have a table with the names and address of all my dealers along with their telephone nos. I want to create a form, wherein I just enter the telephone no; the query/function searches...
2
by: Alan Silver | last post by:
Hello, I am in the planning stages of a new ASP.NET web site, and would like to know if it's possible to add a search facility to it. The site will be a mixture of static pages and ones...
2
by: bhv | last post by:
hello everybody. i am new to MySQL and want to build some asp pages providing search facility where search result displays from database. i have successfully created database in MySQL 5.0, but i...
1
by: Mitul | last post by:
Hi to all. I need some help for following. I am trying to do search facily for both content search and DB serach. Please reply me as soon as possible. "search facility should have the...
3
by: tonydraper | last post by:
I have built a website and for some reason the search facility has stoppped working? When I carry out a job search the page that it redirects to comes up with this. Source Error: Line...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.