I have a form with about 30 fields on it, all connected to a table with file information. I want to create a search form using all 30 fields, so that if a user inputs information in any one of these fields and clicks search, it will find records based on the combination of what he/she inputted in the fields. For all the fields that he/she leaves blank, the search engine will ignore in its search.
I have already created a query that does this somewhat. For each field, I have used this as the criteria:
I have put this in the criteria and they are all linked by an And statement. It works fine, except that the program does not seem to match Null fields together. So, if the user leaves a field blank, the search won't ignore that field, it will only show records with some piece of data in that field. All records that are Null in that field are cut out.
So, I guess my question is: how would I make the program be unbiased towards fields that are Null and let it include records that have null in the field? Am I going about this the wrong way?
I am trying to create a search facilty that will allow me to search any table /form. I have read some posts and have tried to download some of the examples in the sample database section,however after downloading I can't open as my version of access (97) says it does not recognise the format. Ant suggestions would be appreciated . Thanks Treggy
anyone knows how to make a search engine inside microsoft access or database? like for example, i want to look/find information on a specific record.. how will i start making a search engine? help me coz i dont know how.
I have tried to do a search on this but i cant seem to find something similiar. And I did a post of what i wanted to do here: Click Me (http://www.access-programmers.co.uk/forums/showthread.php?t=74845)
This is my table structure: http://www.geocities.com/gerald20000/Alpha/table.jpg
** I have keep this structure the simple. In actual of what i want to do, its more section under the 3rd lvl. as in more section under tblNAMe and tblRELATIONSHIP.
tblMALE (Question related to Name - Male will be here) MaleQuestion MaleAnswer
tblFEMALE (Question related to Name - Female will be here) FemaleQuestion FemaleAnswer
tblMOOD (Question related to Mood will be here) MoodQuestion MoodAnswer
tblNEAR (Question related to Near Relation will be here) NearQuestion NearAnswer
tblFAR (Question related to Far Relation will be here) FarQuestion FarAnswer
So what am i trying to do? People would put in a question and an answer into a box. After that, the person can choose which category does the inputed question belongs to which category(male? female? .. ).This is actually a FAQ search engine.
So ppl will have an option to search for keywords and match questions. There should also be an option weather it should search from tblQNA or tblName or tblGOOD. So it has different level of searching. This is why it has such tree. So after searching, it will display the possible matched question (display question only). then the user can click which question to view the answer (together with the question).
Hope to get some advise. Is this how the best way to implement? OR is there a better method? pls advise and thanks in advance. ill be trying to do the access now. ill post as i goes along.
I would like to create a query where I can search my selected fields on a particular field. However I do not want to do this in the standard way. I would like the query to appear in a 'search engine' type format (like a form but with a search). Is this possible????
anyone knows how to make a search engine inside microsoft access or database? like for example, i want to look/find information on a specific record.. how will i start making a search engine? help me coz i dont know how. :confused:
Here I am again all lost in the beyond side of Access. So, I would like to create a "search engine" , through a macros if possible, so that the users can find the one thing among the hundreds of specs available to them in this form/subforms assebly.
My form has two subforms inbuilt in it, hence it can be really time consuming to find the one piece of information they are looking for. And I was hoping that there was a way they could just type for example "rated voltage" in the search box and they would jump to the one textbox titled "rated voltage". Just like most of us do when reading a large document online, we just use "find" to get what we are looking for.
I have a table that was set up from a parameter query. Before the form opens it ask you for a part number. You type in the part number you want the information for and the form pops up to that particular part number. The problem that I am having is that if I want to move to another part number I have to close out the form and reopen it to type in another part number. I tried using the Access Find/Replace feature to do a search for a another part number to open up the information to that part number and I have even tried to create a command function that will do a search, but when I use them they both come up with no records found.
Is there a way to make a search function in a form created from a parameter query? I am not a script writer and am just really getting indepth with Access at the moment.
Exit_cmdSearch_Click: Exit Sub Option Compare Database Option Explicit
Private Sub List2_DblClick(Cancel As Integer) Dim rs As Object
DoCmd.OpenForm "frmquery"
Set rs = Forms!frmquery.Recordset.Clone rs.FindFirst "[RETURN NUMBER] = " & Str(Nz(Me![List2], 0)) If Not rs.EOF Then Forms!frmquery.Bookmark = rs.Bookmark
tblDegreeAudit ---numStudentID (Lookup field from tblStudentID) ******(Displays the First and Last Name according to Student ID number) ---strCourseCode ---strCourseNumber ---strCourseTitle ---strDegreeProgram (Yes/No field) ---strQuarter (Lookup field from tblQuarter) ---strYear ---strCredits ---strComplete (Yes/No field)
frmDegreeAudit ---tblDegreeAudit
Now, on the form (frmDegreeAudit), I want to put in a search function that allows me to drop down a list with the Student's name and all of their courses next to the name according to the Student's ID. Below is how I want to list to look when you drop the list down:
First Name-----Last Name-----Course Code----Course Number
If I can't do that, then this is what I want to do then. I want a drop down list that has the first and last name. Then right next to that drop down list, I want another drop down list that lists all of the courses associated with the Student ID. It would look something like this:
List #1=========================List #2 First Name-----Last Name->->->->->->Course Code----Course Number
Anybody understand what it is I'm looking for? I hope so because once I figure this out, I can figure out the other database I've been trying to create for the longest time.
I want to create a single search field inwhich the user can search data within two different tables. So for example in both tables there is a field called Container Number. When they type in the number into the search field i want a query to search both tables and return if it is in table one or table two.
I havent used access before and need a easy way to seach through 1300 records i have created in a form. Each record has 6 criteria, some with drop down options. I would like to create a button that when you type into a box and click this button it will search all my records and bring up a list of all the records containing the key words searched for.
I think I can achieve what my boss wants with just one table.
I work in a legal department and we track employee contract complaints/issues. Simple data entry is used and one table (Complaints Table) has been created with fields like: employee name, emp #, what union rep issues the complaint, who from our department responded, when the complaint was sent, when the response was sent, two memo fields to describe the problem and the resolution, and ... perhaps most importantly... contract section.
It is possible that one complaint could touch on multiple contract sections.
The data-entry form I've created has the following fields: contract section 1: contract section 2: contract section 3:
Now I need to create a Search Form and I'm having all kinds of headaches trying to understand how to make this work... trouble with setting up the right macro, too.
I'd like the main form to contain the following search fields: Rec # (which is set as an autonumber in the "Complaints Table") Employee name Employee # Contract Section ...or perhaps just a "contract section" search form...
1. If a contract section # is entered, I want to push a button and have every record of that contract section # filtered from the database and show exactly as the data-entry form beneath the "search" portion of the main form. (did that make any sense?). I know how to create a general form and subform, but the subform always shows up as a table object rather than a form object. How can I fix that?
2. Some contract sections might be numbered: 3.A.2. ....others might have a longer name like 22.C.3.a.vi.(b). .... Obviously, the filter/search needs to catch all records which include the contract section specifically, regardless of which of the three "contract section" fields from the main data-entry form contain data. What's the easiest, non-VBA way of setting that up?
Hello, i have a database that simply holds all the information on one table at the moment and what im trying to figure out is how to create a form that i can enter data onto that searches all the different fields at once, instead of one search for one thing and another for another. Some of the fileds are Headset number, gasket number, make, model, engine cc, etc.. so what i want to be able to do is have a form with a text box for each of these fields that then searches the table to narrow down the search results. at the moment i have a search for each, say for one i enter the headset number, it searches and pulls up all records with that headset number, then i have a search for engine cc, but what happens is it searches all records again for the engine cc not following on from the search for headset number so i end up back with a load of records with diferent headset numbers but with the searched engine cc. Basically the search form needs to take data entered into each text bow for each field then the combined results from each, narrowing down the results. can anyone help? in laymans terms!! Thanks Paul:
I have created a form to search a field in my database by running a query. i can only get it to search for direct matches as I am having no luck getting the "LIKE" Operator to work for me. the format should apparently be, like "A*" , this works but i woud like to replace "A" with a variable from a txt box in my form. which will be a combination of letters and numbers
followed what i think is the right instructions and pasted the appropriate bits in the expression builder to tell it where to look but doesnt work cant fathom out why its not working, it just produces blank queries
Also in the query design page do i need to do anything with the optional "Total" row dropdown menu?
On the navigation buttons at the base of the form there is a search facility where I can type a record id but I would like to get rid of that and search records either by turning the ID field into a searchable box or by adding a button where I can press it and get a search box up.
I am using access 2010 and am trying to create a dynamic search like the one google uses, it will feature a drop down that will populate with results but when its not in use the drop down is not shown.
I'm looking to add a menu system to the form its self so that the users will be able to do certain functions. third image is what I'm looking to create with the menu bar at the top of the screen.
I have a form that when initially displayed shows all client records in my database with the ability to add new ones through a "Add New" command button. I have another command button called "Search" that displays a second form containing a dozen or so fields to search and filter down on.
When the Search button is pressed, I display search form and enter data in some of the fields for searching. I then click ok and the main form should now display a subset of the records based on the data I entered on the search form.
My dilemma is that the WhereCondition of the docmd.OpenForm doesn't work. The doccmd works to open the form when I don't include the WhereCondition, but the moment I put the WhereCondition in, I get an unknown runtime error (3000).
Here's the code. I've stripped out most of the search fields to target just the one until I can get it working, then I'll add the others back in.
Private Sub cmdSearch_Click() Dim varWhere As Variant ' Initialize the search string to Null varWhere = Null If Not IsNothing(Me.srchLastName) Then varWhere = "([p_last_nam] LIKE '" & Me.srchLastName & "*')"
I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:
Adult Banding Records Nestling Banding Records Historical Banding Records
The specific issue:
I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.
Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon
creating a VBA that will allow me to search for a record in Table A, and once the record is found, allow me the option to add that record to Table B. Is this possible to do if Table A and Table B are not formatted the same (i.e. one has more fields than the other)? In summary, creating a VBA that will allow me to search in one table and once the correct record is found, allow me the ability to add it to another table.
I've been able to navigate a lot on my own, but there is one issue I cannot seem to resolve. I have 1 form and 1 table. I have the form set up so that you can enter data, and then press a button, and it will "save" and refresh the form for a new entry. However, I want to be able to pull that entry back up in the form, and fill out additional fields later on.The form is set to data entry = yes because I do want the form to open up as brand new each time.
To sum up my question. I want a text box and search button at the top of my form. When you type an ID number in the text box, and then press search, I want access to populate my form with the information in my data table associated with that ID number.
If I type in the number 1234 and hit search. I want my form to autopopulate with the data in the row for ID number 1234 (all the fields I have already populated). So by searching 1234, the name, phone, background info, etc that is populated in the row will appear.
creating a searching form and to show the results inside the form! It's like a Library type searching. u want to search for some type of monument or so and it shows the results, all the info, photos and that!
