Hello
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 hope this make sense!
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?
help much appreciated
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!
View 6 Replies View RelatedI 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?
I just know nothing of VBA.
Any help is greatly appreciated...
tango..
putting a search button on a form on my database.
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 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.
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.
View 7 Replies View RelatedI'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.
When I type the first letter I into the search text area I get the following error
Run-time error '2110'
Microsoft Access can't move the focus to the control SearchResults
Most of the code is below
QRY-SearchAll
SELECT Clients.ClientId, Clients.ClientFileNumber, Clients.ClientShortFileNo, Clients.Salutation, Clients.FirstName, Clients.LastName, [Group Branches].BranchCode, Clients.Phone, Clients.Mobile, Clients.Fax, Clients.BpayRef, Clients.TradingAs, Clients.EntityType, Clients.ABN, Clients.ACN, Clients.Address1, Clients.Address2, Clients.Town, Clients.State, Clients.PostCode, Clients.Country, Clients.Email, Clients.ClientGroup, Clients.DateCreated, Clients.Notes, Clients.LastModified, Clients.UserCode, Clients.BdmCode, Clients.CollLongNo, Clients.CollShortNo,
[Code] ....
I have a search combo box to search for a field on my form. But it just goes to that particular record. The combo box is for client id and it has more than one record. How can I have it return just the records pertaining to the client id choosen in the combo box.
View 5 Replies View RelatedI would like to create a series of help fields for various text boxes on a form to free up space that is currently displayed in several labels.
Does anyone have an example that will allow me to create several help field icons on a form. Clicking on each individual icon will display a help message for that particular icon? I would prefer to store the data on a table. If I write this, I will probably use a recordset and display the data in a label.
I just wondered if anyone else has any other ideas.
Hi, i was wondering how can i make a field that does search on a form linked to a database?
if anyone can help asap it would be really great
I have created a Form based from a Table. On one of the fields, I need to be required inside the Form before moving to the next record of that form. I can not make the required field in the table, because I have to append information day to day.
Also, the required field is a combo box option that is limit to the list.
What kind of Code I can do inside the form of the specific field to make it required before clicking onto the next record. It does not matter if the processor closes out of the form before updating, only if moving to the next record.
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.
Thanks
Steve
please see the example attached, it is a very basic search, double clicking on the return number opens that record.
what i would like to do is show the account name and date is the same box.
below is the code that i use. can someone please someone help me incorporate something into it?
rivate Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmSearchReturn"
DoCmd.OpenForm stDocName, , , stLinkCriteria
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
DoCmd.Close acForm, Me.Name
End Sub
Private Sub TxtSearch_Change()
Dim vSearchString As String
vSearchString = Me.TxtSearch.Text
'Me.txtSearch2.Value = vSearchString
'Me.List2.Requery
'Err_cmdSearch_Click:
End Sub
Hello,
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:
IIf(IsNull([Forms]![File Search Form]![SearchFieldName])=False,[Forms]![File Search Form]![SearchFieldName],[FieldName])
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?
Any way, hope it was clear. Thanks in advance. =)
Hello,
Stuck on a problem and was wondering if anybody has a possible solution. I currently have the beginnings of a database setup that uses a search field on a form to find a reference listing using a part number input by the user. It finds the part number, and then displays all the information about it on another part of the form. My question/problem is, I now want to expand this database to multiple specialties, all with different parts in their respective listings, so they would have different tables. The tables would be the same format, just different information. No one table would ever contain the same info.
Q: How would I set up a form to search for a unique part number over multiple tables, and return that parts info?
It works great for one table, but I am stuck on the multiple tables.
Thanks in advance!
I'm developing a program to search several fields. One of which is a memo field with large characters. Using wildcard in the search form I made, it only returns the record if the first word of the field is typed in the search box. I want to type any word in any part of the field to return the record.
This is my code
' Check for LIKE Subject
If Me.txtsubject > "" Then
varWhere = varWhere & "[subject] LIKE """ & Me.txtsubject & "*"" AND "
End If
subject is a memo field.
Anybody here know how to create a search field on a form to pull up a certain record?
I am practicing with Microsoft Access by creating a database that tracks my college degree progress as well as my wife's college degree progress.
Here is the Database Structure:
tblStudentID
---numStudentID (AutoNumber)
---strFName
---strLName
tblQuarter
---strQuarter
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 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.
View 10 Replies View RelatedCurrently, I am trying to search a memo field on a table by having the user enter keywords on a form (up to 10 keywords can be entered). It works when just one keyword is entered, however it doesn't work if more than one is entered. How do I account for more than one keyword being entered to search the memo field and return the recors where any of the terms show up in that field. Each one of the keywords [KW1] ...etc are in a separate unbound box.
Current formula looks as follows:
Like "*" & [Forms]![KeywordInputForm]![KW1] & "*" OR "*" & [Forms]![KeywordInputForm]![KW2] & "*" OR "*" & [Forms]![KeywordInputForm]![KW3] & "*" OR "*" & [Forms]![KeywordInputForm]![KW4] & "*" OR "*" & [Forms]![KeywordInputForm]![KW5] & "*" OR "*" & [Forms]![KeywordInputForm]![KW6] & "*" OR "*" & [Forms]![KeywordInputForm]![KW7] & "*" OR "*" & [Forms]![KeywordInputForm]![KW8] & "*" OR "*" & "*" & [Forms]![KeywordInputForm]![KW9] & "*" OR "*" & [Forms]![KeywordInputForm]![KW10] & "*"
Should I be using AND?
I have a table name ex. equipment and many fields inside ex. date, equipment name (combo box) and others.
I need to make form for all fields and button search once I click on it will open a report depending on a fields i selected.
I think that I have to use VBA
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.
[URL]
I have Territory Mgmt database. Sometimes when I get a new lead I need to quickly find other leads in the database that are closest to it.
I am currently not tracking latitude/longitude etc. Do I need to? Is there a way to set certain location marks by address?
If so, once I put in the latitudel/longitude how do I create a search to find the nearest?
This is what the search box must do:
- Searches 3 fields (StudentID, FirstName, LastName) and jumps to the relevant record.
The closer to the search box in the navigation bar below, the better, so this is what I would really like too.
- A repeat click would take me to the next result/record (if any) with the same criteria
- Instant search as soon as I start typing