473,626 Members | 3,294 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

search form

57 New Member
Hi,

I'm kinda stuck right now with one of my forms and wondering if someone could help me with this. Basically what I need to create is a search form for users to be able to search using part Id or part Description. for example if user type in 10* in the part ID text box, then it should show all parts with part ID started with 10 in a data grid on the same form
I have a form with txtpartID, txtDescription and search button. My question is how can I display the results? do I need to create subform for it? I'm using access 2003. Your help in this is very much appreciated.
Jan 21 '08 #1
10 1647
jyoung2
32 New Member
Yes you should use a subform
If you are creating a seperate search form with "txtpartID, txtDescription and search button" then you can have your query look to txtpartID and txtDescription and have the button call "me.subformname .requery" and it will display the results.
Jan 21 '08 #2
ConfusedMay
57 New Member
How can I do that? should I put that code on the search button?
Jan 21 '08 #3
jyoung2
32 New Member
Create a query in design mode,
you place [forms]![YourFormName]![YourFieldName] into the criteria fo your query

requery the subform with your button on click event using this code.

me.subformname. requery
Jan 21 '08 #4
jaxjagfan
254 Recognized Expert Contributor
In the criteria of your query put something like this

Like "*" & [Forms]![frmSearch]![txtVIN] & "*"

This is a quote from something I do here - modify to suit your needs.

You no longer have to add the *. It is assumed. And this criteria would return all with a 10 anywhere in the partnumber whether beginning, middle, or end.

You can do this via an unbound textbox and a command button. If the form is based on the query with the criteria above then all the command button needs for code is

Me.Requery

You would enter a value and click the button.

Wildcards like you are using have to use the word "Like"
Jan 21 '08 #5
ConfusedMay
57 New Member
In the criteria of your query put something like this

Like "*" & [Forms]![frmSearch]![txtVIN] & "*"

This is a quote from something I do here - modify to suit your needs.

You no longer have to add the *. It is assumed. And this criteria would return all with a 10 anywhere in the partnumber whether beginning, middle, or end.

You can do this via an unbound textbox and a command button. If the form is based on the query with the criteria above then all the command button needs for code is

Me.Requery

You would enter a value and click the button.

Wildcards like you are using have to use the word "Like"
Thank you for the reply and I'm almost there. The only problem I have right now is if there's a part ID start with 10 and there's no description for that part, it won't show up on the search. I need for users to be able to search by part id or description. Is there a way for access to show all data with part id 10 no matter if the description is null or show all part id if the description is null?
Jan 22 '08 #6
jyoung2
32 New Member
In your query

Part one
Expand|Select|Wrap|Line Numbers
  1. Field    yourfildDesc
  2. Criteria    LIKE *  & [forms]![YourFormName]![YourFieldName] & "*"
  3. Or    LIKE *  & [forms]![YourFormName]![YourFieldName] & "*"
Part 2
Expand|Select|Wrap|Line Numbers
  1. yourtxtpart
  2. LIKE *  & [forms]![YourFormName]![YourFieldName2] & "*"
  3.  
  4. LIKE *  & [forms]![YourFormName]![YourFieldName2] & "*"
Prt 3
Expand|Select|Wrap|Line Numbers
  1. [forms]![YourFormName]![YourFieldName]
  2. not is null
  3. NOT is null
  4.  is null
prt4
Expand|Select|Wrap|Line Numbers
  1. [forms]![YourFormName]![YourFieldName2]
  2. not is null
  3. is null
  4. Not is null
take each part an put them into the query
The logic is if a field is null it ignores it for the query if both fields have data both feilds are used if only 1 has data it ignores the other

Place them side by side and it makes more sense if this doesn't help I can try to explain it better or I could maybe send a screen shot but it would not paste.
Jan 22 '08 #7
NeoPa
32,567 Recognized Expert Moderator MVP
Check out Using "&" and "+" in WHERE Clause.
Jan 22 '08 #8
ConfusedMay
57 New Member
Check out Using "&" and "+" in WHERE Clause.
Thank you for all your help. but the code that you gave me is still not working (probably I'm missing something) Here is what I did: On the query (that showed all the data on the subform) on the partID criteria:
Expand|Select|Wrap|Line Numbers
  1. Like "*" & [forms]![frmsearch]![txtid] & "*" or  Like "*" & [forms]![frmsearch]![txtid] & "*" 
  2. and on the description criteria, I put:  Like "*" & [forms]![frmsearch]![txtdesc] & "*" or  Like "*" & [forms]![frmsearch]![txtdesc] & "*"
but I don't really know where I should put the code (part 3 and part 4). Is it on the search button? because my search button code is DoCmd.Requery "search subform"
Jan 22 '08 #9
NeoPa
32,567 Recognized Expert Moderator MVP
You've quoted my post, but I suspect from what you've posted yourself that you haven't checked it out yet.

I'm afraid jyoung2's suggestion is not quite right (although they're trying to be helpful so I have no complaint with that). Any strings in SQL should be enclosed in quotes ('). See Quotes (') and Double-Quotes (") - Where and When to use them.

NB. If you're creating a string to pass to SQL you need to remember that the VBA will understand some of the quotes (") and pass on the others to SQL. For a string value in SQL you need to make sure that the SQL gets the string WITH the SQL quotes in.
Jan 22 '08 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
8715
by: Les Juby | last post by:
A year or two back I needed a search script to scan thru HTML files on a client site. Usual sorta thing. A quick search turned up a neat script that provided great search results. It was fast, returned the hyperlinked page title, filename, and the body txt (30 preceding and following words) in context with the search word highlighted. Excellent.! See it working at: http://www.ipt.co.za Just search for "firearm"
2
3523
by: CharitiesOnline | last post by:
Hello, I have set this script up to add paging to a search results page. Which on the first page works fine. I calculates how many pages there should be depending on the number of results returned from my search and how many records I have set it to display per page. All great so far, HOWEVER..... When you click the next link to see the next 10 results on the next page, it just dumps the search details and pulls up all the records in the...
1
1556
by: bdawg | last post by:
what i want to do is create several radio buttons and a textbox for searching purposes. the search will perform a search depending on which button the user selects. here is what i have now: - for each radio button, i use the onClick event handler, which calls a function called showMe() - showMe() {
1
2095
by: N. Graves | last post by:
Hi, I want to have a Search Dialog box that has several text box and fields to build a search and display the results in a form. I can do everything that I need to if I us a report but I would like to have the search from data displayed in a form. The structure that I have for this was take from the Asset Manger from MS. Anyway I open a report that in turn opens up a search form that allow me to select data. Then you continue by...
9
20300
by: Christopher Koh | last post by:
I will make a form which will search the database (just like google interface) that will look/match for the exact name in the records of a given fieldname. Any suggestions on how to make the code?
8
3208
by: Steph | last post by:
Hi. I'm very new to MS Access and have been presented with an Access database of contacts by my employer. I am trying to redesign the main form of the database so that a button entitled 'search' may be clicked on by the user and the user can then search all records by postcode. I want to do this to prevent duplicate data entry.
1
7529
Merlin1857
by: Merlin1857 | last post by:
How to search multiple fields using ASP A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and having the sql statement dynamically built according to the input provided by the user. I have used the method described here hundreds of times it is quick and adaptive. I generally use a frames page for the search, in this way the search is maintained...
5
4146
by: Fran Jakers | last post by:
Hello all, I'm new to all this and I could really use some help. I've searched the web but cannot find an answer. I have an HTML form with 3 radio buttons and a search field that calls a MySQL database using JavaScript to find a product either by Product ID, Description, or Both. Originally, the 'Both' radio button was "checked" but I now want the 'Product ID' button set as the default choice (as you can see from the code below). ...
2
2260
by: Mark | last post by:
Hi All, I am creating a music database for a friend and have run into a problem. Within the ALBUM table, I wanted to store the ARTIST_ID rather than the ARTIST_NAME. To do this, I intended to have have a command button on the album form which would open a search form (based on the artist table). This works as I wanted and allows me to get to one record. I then planned to have a button on the search form which when clicked, would updated...
12
2337
by: iahamed | last post by:
Hi Everyone, I got two parts of my advance search to work, I am running out of Logic to connect the third. My mind is in swing! Pleaseeeeeeeee Help me. I have 3 Fiels to search, the First two works, thats: 1. Category to search from the (Drop down name: ASPDBCatSearch) is working with the Input Text Box Field (name: keyword), based on Whatever selection on the Drop down value selection:
0
8262
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8196
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8701
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8637
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6122
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5571
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4090
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4196
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1807
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.