473,587 Members | 2,321 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3964
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/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.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

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

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

Similar topics

21
2976
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 spare the time, I would appreciate any comments (including words like evil and disgusting, if you think they are applicable :-}) on the example here....
4
1552
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 System in place, I have only limited control over the site - I can't even create a new page without asking the webhost to do it for me (for which...
4
2259
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 to it now. Thanks Brian Tozer
3
1598
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 the string in the table and throws the name and address details of that respective dealer back on to the form. Please give me a detailed guidance...
2
1710
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 generated from database queries. Ideally I would like something that will search the pages generated by following links as that way it will index the whole...
2
1502
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 can't show them in asp pages. the browser gives me following error: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Access...
1
1160
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 flexibility to identify commonly miss-spelt words and re-direct users accordingly"
3
1363
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 107: protected string formatSalary(object Salary, object jobType) Line 108: { Line 109: int iSalary = Convert.ToInt32(Salary);
0
7843
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8340
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7967
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8220
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6621
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5713
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5392
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3840
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1452
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.