Search A Table
Sep 15, 2006
Apologies if this has been covered lots before, I searched and couldn't find anything. I am very new to this side of Access so simple instructions would be appreciated. I'll try to explain it as clearly as I can also.
I have a table with a bunch of fields detailing a job going through our factory.
There are hundreds of these records, so the user must be able to search to view only the ones they want to see.
So I created a form with some combo boxes/text boxes, each labelled for a different field, so they can choose PO number, customer name, supplier name, out date OR job status, and view all the records with a certain criteria attatched to one of those fields.
Problem is, I want them to be able to search for all jobs from a certain supplier AND a certain outdate. Or any combination of those 5 fields, maybe filling in all of them, or 4 or 3 or 2 or just 1. At the moment they can search by one of them, then press the button, and I have a different query for each one.
Have I explained that well enough? Please ask if I need to make something clearer. Hope somebody can help.
View Replies
ADVERTISEMENT
Apr 11, 2012
Actually I have a small form of customer details, that i made in excel, the field name mention below,
Customer Details Table
First Name
Last Name
Contact Detail
Address Detail
Postal Code
Last Purchasing Date
Remark
Now i want to make a search form like this
Search Form
Contact Details
& the result is show which I insert the contact number.......
View 1 Replies
View Related
Jul 23, 2013
I have a table called BID with the following fields
bidder, seller, iid, starttime, bidtime, and bidprice
bidder is an id number that is reference to uid of table USER
the USER table have the following fields,
uid, uname, city, and state
I created a BID form for the user to enter data into the BID table. What I am trying to do but is having trouble with is have the user search for their name that may already be store in the uname field in the user table, and what ever they choose base on their search, the uid of this uname gets store into the bidder field of the bid table.
I still have a second step where the user in the bid form can search the iid but shows the itemname from the item table, and the value of seller, iid, and starttime from the auction table gets populated into the bid table.
iid from the auction table is reference to the itemtype and the following fields exist in the itemtype table.
iid, itemname, description
View 14 Replies
View Related
Jun 11, 2013
Access 2007. I have the start of a family history database containing two tables with very similar data.
First table:
FILTERED Births 1837-2005
IDGiven_Name Location County Town Year
1 Eliza Ann England Yorkshire Leeds 1837
2 Elizabeth England Norfolk Aylsham 1837
3 Isabella England Lancashire Preston 1837
and
Second Table:
FILTERED Deaths 1837-2006
ID SURNAME GIVENAMES LOCATION COUNTY TOWN YEAR
1 FRANKLAND Alice England Lancashire Clitheroe 1846-1855
2 FRANKLAND Ann England Lancashire Clitheroe 1837
3 FRANKLAND Barbara England Yorkshire Whitby 1837
My problem; from the Given_Name in first table I want to be able to search for the same name in the second table probably using the Town field to narrow the search down. As an example;
FILTERED Births 1837-2005
ID Given_Name Location County Town Year
16 Birtwistle England Lancashire Haslingden 1838
FILTERED Deaths 1837-2006
ID SURNAME GIVEN NAMES LOCATION COUNTY TOWN YEAR
18 FRANKLAND Birtwistle England Lancashire Haslingden 1838
This show that Birtwistle died as a child, but it is also an easy match to spot due to the unusual given name.
View 2 Replies
View Related
Jan 8, 2008
Hi, Im trying to, upon the click of a forms button, search the records in one table for all records matching a certain number. I then want to update another table with all these records...but am stuck in the coding. This is what i have so far...
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAllInfo", dbOpenDynaset)
With rs
.MoveFirst
Do Until .EOF
If .Fields(1).Value Is 1 Then
event_num = .Fields(1).Value
Sport = .Fields(2).Value
team = .Fields(3).Value
Date = .Fields(4).Value
Time = .Fields(5).Value
rs.Update
Set rs = db.OpenRecordset("tblCurrentEvent", dbOpenDynaset)
rs.AddNew
rs![Event_No] = event_num
rs![Sport] = sport
rs![Team_Player] = team
rs![Date] = Date
rs![Time] = Time
DoCmd.OpenForm "frmInfo" 'whose record source is tblCurrentEvent
But I really need to be updating the latter table within the loop but dont know how to do that without resetting the loop pointer?? Or would it be better to create a form for tblAllInfo. Any help would be appreciated! Tania smile
View 9 Replies
View Related
Jan 25, 2007
Hi everybody,
How I can set a query to search in all table fields. In my table I have around 48 fields and I want to search for particular value from combo box if this value exists in any of those fields.
View 2 Replies
View Related
Dec 22, 2007
Hello all, once again I would very much appreciate anyones help on what may be quite a simple issue,, my problem (or one of!!) is as follows:
I have a table that contains approx 3k records I wan to run a query that allows me to select a certain number of fields with a group. From this I mean, I have all records grouped by discipline, firstly its easy for me to determine the discipline I require (I do this in the design view by adding the discipline number in the criteria row.) next I want to find a certain number of the records that are within my chosen discipline,, the records I wan to see all contain the word ‘alignment’. I tried entering ‘alignment’ on the criteria row but that didn’t get me anything,, the field that contains the word alignment also has many other words listed…
Please help
Many thanks
Jon
View 3 Replies
View Related
Apr 15, 2008
Hi,
I'm a newbie (I know nothing in VBA & SQL but I'm learning) and I've a database of 10 tables. I want to make a query & form to search through these tables and display results(in a form & report) according to the search criteria.
example of my request is attached..
I can PM you my real database if you need it.
Help is appreciated
View 1 Replies
View Related
Nov 4, 2006
I have 2 table in ms access (Table A and table B).I am doing a search function which able to search the record on this 2 different table. how to do that? how to set the source?
Here is the coding for me to search record just only from Table A but i would like to make it search on Table B as well.. :confused:
Private Sub btnSearch_Click()
Me.frmsubTableA.Form.RecordSource = "SELECT * FROM TableAQuery1 " & BuildFilter
' Requery the subform
Me.frmsubTableA.Requery
End Sub
View 3 Replies
View Related
May 27, 2014
How can I search through a table and if the record on a main form has certain words it will save a note in a record on another table.
View 3 Replies
View Related
Feb 17, 2014
I have a form that has 1 Text and 2 list boxes.
text box 1 = lstname (where user will put bunch of names line by line)
List box 1 = Txtfound ( will be displayed the names that exist in the db)
List box 2 = Txtnotfound ( will be shown the name that r not in db)
All i am looking to do is to display another information along with the name found in database. When it found a name in db, it should also pickup the Case assinged to it. The field name for that is LAN. So that when it displays the name in Txtfound, beside that name is should dispaly the LAN case number. My current code is able to display only the name, but can't get the case number.the current code I have:
Private Sub CmdSearch_Click()
Dim Values() As String
Dim MyLoop As Integer
LstName.SetFocus
Values = Split(LstName.Text, vbCrLf)
[code]....
View 2 Replies
View Related
Mar 28, 2006
Using Access 2000 is it possible to specify a starting location ie (c:) and using VBA search every sub folder from this point for files ending .mdb or .xls?
I want to be able to build a table with an record showing the file name and directory for each file found.
Any help most appreciated.
Regards,
Dalien51
View 2 Replies
View Related
Dec 28, 2005
I have a table that has the following: Joint Account, Employer, Employer1...Employer20, subemployer1, subemployer2... subemployer5, addemployer1, addemployer2... addemployer5, removeemployer1, removeemployer2....removeemployer5.
It might be possible that any of the columns with the word "employer" in them have the same number in them. The Joint Account column might have the repeating number in it as well.
I want to be able to push a button on a form open a input box and enter a 7 digit employer number and search through the whole table and return all the rows where that number particular number is.
I have tried building a query using "like" in the criteria but you can only use that 8 times.
I have searched through this forum, but I am unable to find something that I could use. As I m not a seasoned access user, I do not quite know what to do with some of the other "search" questions I have seen.
Thank you in advance for any help that could be given or pointing in the right direction.
John
View 4 Replies
View Related
Nov 17, 2004
I want to be able to find the last value entered into a table from a form that is not based on that table. How do I go about this?
What I am trying to do is produce a 'purchase order number system' that adds 1 onto the previous number, but this purchase order can be generated on many forms and for many reasons across the database. I do not want to use autonumber.
Cheers,
Recall.
View 1 Replies
View Related
Dec 31, 2004
Im just starting to program in Access.. can somebody please help me with my problem?
Im trying to figure out how to search the table with the content of my textbox. (Ex. i input "A" in my textbox, it should display all the records of "A" like it's AGE, STATUS, etc.) it doesn't have to be case sensitive or exact because i have a primary key that doesn't accept duplicate.
Thanks to anyone who'll help
View 3 Replies
View Related
Feb 2, 2015
I use this bit code to look at a table for the path for pdf file to go into
Code:
varFolder = DLookup("Folderpath", "pdfFolder")
this looks at the table pdffolder
what I would like to do if possible is for this to look at the first line in the table but I want to use this part of the code for 4 or 5 different paths Is it possible to modify the code so when I use it 5 times each code looks at different lines in the table
View 8 Replies
View Related
Mar 24, 2015
I have made a query to search for some items on a table. I want there to be a button on the search form that says 'search' and takes you to the corresponding page on the form. (The form has multiple pages in, all with different products on) Is this possible?
View 4 Replies
View Related
Apr 7, 2015
I have a table of information in Access and I was wondering what would be the best way to get the info I need out of it.
for example can I search for a specific word that could be anywhere in the table in any of the cells.
The word is 'pump' and I would also want to know if it is an inception pump or not and then I would like to see a list of clauses associated with the pump (the clauses would be in a specific column under a heading)
View 2 Replies
View Related
Apr 3, 2006
How to search an Access table for unwanted <cr> characters
Occasionally a stray carriage return <cr> Ascii 013 character finds its way into an Access table. These destroy the database when the table is processed by an outside utility for data cleansing.
To prevent this from happening, we have been told to clean the table before submitting it, i.e. remove all of the following:
carriage return, comma, double quotes, equals, greater than, smaller than
Is there any utility available which will remove all these characters when being run only once? (i.e. not find/replace which has to be started separately for each of these characters)
How do I search for a <cr>, even with find/replace?
Thanks.
Adrian
View 2 Replies
View Related
Mar 29, 2006
v sorry for the basic question, but ive been banging away at access and i cant my head around this..
i need to run a simple query. the query/ search will ask users to enter in the number of a document. i want, when this code is entered, for 2 controls on the form be updated with codes that are stored in a table based on the code they searched for originally.
heres my table structure...
Table A
Doc ID (PK)
Doc No (manually input and is the search item that users enter)
Table B
Unique ID (PK)
Doc No (FK)
Info (to populate field 1)
Info (to populate field 2)
how do i perform this in the query section? do i need to manually code the SQL required, or is this query basic enough that i can just select the fields required in the design view of the query?
thanks guys
View 1 Replies
View Related
Apr 16, 2013
I have a form with two unbound text boxes: HireMovieID and HireCustomerID, and a button HireButton which runs my query: HireHistoryQuery. Then I have a table: HireHistory. In my HireHistory table, I have my CustomerID's along the top as column names. Then the records for those columns are in this sort of format: "0001 on 19/05/2006" as type Text.How can I make it so that when a user enters a Customer ID (e.g. 23) into my HireCustomerID box in my form, it shows column number 23 and all it's records?
Also, what code do I need so that if someone enters something into the HireMovieID text box in my form (e.g. 0001) it shows all of the instances of that from the whole table in its respective column?
View 5 Replies
View Related
Jul 24, 2014
We have a form with a field Drawing No from the Table Job Register
I want to be able to take the first part of the drawing number and display all matching items from the quote details table.
Aesthetics I can sort later.
I've created a query Item History.
My intention is to have a command button on the form Job Register that runs the query qryQuoteHistory displaying matching results.
My issues are as follows:
drawing numbers are sporadic in length and information ie:
kk80
a2-19520
a2-19520 rev a
e8
1128215 issue d
Retrospectively I would have had issue or revision number in a separate field, or better yet have a products table and link the info there. Unfortunately this isn't the case.
View 14 Replies
View Related
Apr 29, 2014
I have a table of data lots of it (Table A)!!!
In another table I have a list of key words (Table B).
I need to somehow show all the records in Table A that contain (full words only (ie not part of a word) in Table B.
View 3 Replies
View Related
Aug 8, 2006
Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :
Private Sub SearchButton_Click()
DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"
End Sub
The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .
View 4 Replies
View Related
Aug 11, 2015
I have a form that has a control to search the table for a specific record See code below that is on the AfterUpdate event of a Combo Box
Code:
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Code]....
What I need to know is how to make the rowsource based on the filter and not the table.
View 4 Replies
View Related
Jun 26, 2013
I am attempting to create a search form where a user can search by either employee name or company name. I have 5 tables to use. Is there a simple way of creating a search method for this? I would like to be able to have the user type in a name and click a button that says search with any records matching the search come up. However, I could definitely use a method where they type it in a box and it finds it as the person types.
How to search from all five tables at the same time.
View 3 Replies
View Related
