What Is The Best Way To Search For Records?
Mar 15, 2006
Hi there
This may seem to be a very general question for some but im wanting to find out what the best possible way of filtering records?
I have a database that structurally is split up into 2 tables. The one table refers to primary address information and the other table with the more detailed table. These two tables are linked as one-to-many. Theres also about 20,000 records to my db.
When i use the advanced sort filter in Access my address table is the primary table im allowed to search by, not any of the secondary table fields.
Is there a way around this or should i be using other recordset or filtering techniques?
Does anyone know the code to use the advanced sort filter function, rather than going into the records tab in access?
Any comments to put me in the right direction greatly appreciated
cheers
View Replies
ADVERTISEMENT
Jun 18, 2014
why the code below is not functioning properly. When I type in an acronym in the textbox, it keeps saying there is an error "Run-time error '3345': Unkown or invalid field reference 'ABO'." I do have ABO in the field.
The dysfunctional code:
Code:
Private Sub btnFind_Click()
If (TxtFind & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Acronym] = " & TxtFind
[Code] .....
The red highlighted line is where the debugger leads me to. Something with identifying the field? I would like to enable the search procedure to search throughout the entire records rather than just a specific field. How may I write such a line or two?
View 5 Replies
View Related
Apr 29, 2014
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
View 4 Replies
View Related
Nov 5, 2007
Hello All,
I have a form that I want to search Criteria that is "Like" the data entered. So if I am looking for "VA11336", the user may enter "11336" it will pull that record up (VA11336) and any other records that are "Like" what is entered. Possibly with a "find next" to browse other records. I am not sure how to make the query do this......I tried
Like [Which Site #?] in the query but I know this is not right...
Any Ideas?
Thanks
View 1 Replies
View Related
Jan 11, 2007
Hi All,
I was wondering how would you search all records by a given month. What criteria would you put in the date field?
View 12 Replies
View Related
Jul 4, 2006
Hello Friends,
I had a problem with my database although the problem is very easy because I am out of touch I am not able to solve it. I am attaching my databases along with this thread.
I want that if the user enter First, Last or Inumber in a text field in the Form "search". After pressing the button ‘search’ a the form ‘search_results’ opens and displays all data records that match to the entered parameters.
By clicking on the data record the form ‘candidate’ opens with this specific record and the form ‘search_results’ is closed.
I would be happy if someone help me.
Thanks
View 1 Replies
View Related
Jan 7, 2005
Having an excruciatingly hard time seting up a txtBox and listBox to search records
ListBox, which contains an agency name should display all but filter out as user types in the textBox above. this listbox should allow a user to double-click to bring up record.
Will really appreciate the help! I have seen it being done on Access – and was wondering if anyone knows of a link or tip on setting this up!
ThankYou Friends!
View 1 Replies
View Related
Oct 31, 2006
On a form I have an scrollable listing of all records in my database, alphabetized by last name. There are alot of records so I would like to have a 'Search' text box -- that is, I would like to be able to type letters of the last name in the 'Search' text box and as each letter is typed I would like to see the records scroll to the first "last name' that starts with the text I have typed so far.
Any ideas ... Thanks ahead of time!
View 2 Replies
View Related
Nov 9, 2006
I have records in a table that contain many fields 3 of which are text fields that I use for storing a year eg 2002,2003 etc. A record may only have a value in the 1st text field whereas other recods may have values in 2 or all 3 text fields. The problem I have is I want to select records that have the same year in any one of the text fields. For example:
Record 1 may have text fields containing: 2002, 2003, 2004
Record 2 may have text fields containing: 2003, 2004, 2005
Record 3 may have text fields containing: 2002
Record 4 may have text fields containing: 2002, 2004, 2006
Record 5 may have text fields containing: 2006, 2007
In the query I want the user to enter one year value using the prompt [Please enter required year] eg 2004 and for the query to then select records that contain that year value in any of the text fields. In this example records 1, 2 and 4 would be selected. If the user entered 2007 only record 5 would be selected. How do I do this?
Grateful for any help!
View 6 Replies
View Related
May 30, 2006
Dear all,
I'm trying to incorporate a combo box in my form to navigate through records. The plan is to select a piece of equipment listed in the combobox, and that record will then be displayed as an "after update" action.
However, I have tried through coding I have tried before and utilising the wizard to create this combo-box, but each time I get a "Complie Error - Can't find project or library".
The section of code is shown below:
Private Sub Combo227_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EquipmentID] = " & Str(Nz(Me![Combo227], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Any help gratefully received!
Thanks,
Steve
View 2 Replies
View Related
Jan 9, 2014
I have several forms in a navigation form. On all the forms, the little search bar at the bottom shows # of 103 records. On my navigation form it shows 1 of 1 and won't allow me to search.It would seem that the search bar is only looking at the current form and not at the records.
View 1 Replies
View Related
Jun 25, 2014
I have a form that lets you add records to my table, it works well and I can see the records in my table but then once I use my search form, which also is working well, the new records won't come up when I do a search, it just keeps searching through the records that were there when I created the search form.
View 4 Replies
View Related
Dec 6, 2012
how to search records by month and year.
example i will have form that contain combobox that will list Jan-Dec.
and also unbound box for user to enter the year.
then, it will have search button. after the user click search button, it will show report of the selected month and year.
View 2 Replies
View Related
Oct 15, 2014
I have used John Big Bootys Dynamic Search Form, however to search on the fields I want I have created a query which has mutiple tables and when I get a duplicate value in the search box, i.e. in Client, there are 2 client "A"s and when I click on one it automatically chooses both.From here I want to create a double click event to open that records in a form, but I can't if it defaults to both records.
View 1 Replies
View Related
Feb 7, 2014
I am building a search form in my DB. I have got this working by using a text search box on the main form which then links to a subform that shows the results. This works great, however, when the form is first opened, it shows all the records that be searched. Is there any way that I can stop this? I just want the subform to be blank until a search is ran.
View 3 Replies
View Related
Mar 17, 2014
I have a database with over 100k records and I want to use a combo box to search for records by typing in the name. I found code to do this at: [URL] .....
This is working, however i have many fields on the form that I want to populate from the table after the search. Right now only the 3 fields from my rowsource combo box populate.
In my select statement do I need to SELECT * (all) and then populate the fields that way, or is there a way to populate from the table based on the ID of the record that is found?
View 3 Replies
View Related
Aug 6, 2014
I have a search button / text field on a form with the following code:
Private Sub SearchButton1_Click()
Code:
Dim rsTemp As Recordset
If IsNull(Me.SearchField1) = False Then
Set rsTemp = Me.RecordsetClone
rsTemp.FindFirst "[CompassRef] = '" & Me.SearchField1 & "'"
If Not rsTemp.NoMatch Then
Me.Bookmark = rsTemp.Bookmark
[Code] .....
Unfortunately it only returns the 'No record found' MsgBox, even if SearchField1 is populated with relevant content.
View 9 Replies
View Related
Oct 27, 2014
What could cause certain records to not be searchable when performing a search?
I have created a pretty simple Search Form that I can look up a record by a Job#, Phone#, or Name. It populates a list of record(s) on the bottom pane using a split-form.
The issue that I have recently discovered is that some records are not showing up when searched, though the record does exist. I can find the record by manually going to a record in the database that is close to the one I’m looking for and then using the previous and next record button to view the record I want.
So if a record exist and has the relevant data for the field that is being searched, why would it not find it? Using the default search built into Access on the bottom record bar, I can find the record using the search criteria I mentioned above.
I also have the Search Form set that if no search criteria is entered, it will display ALL records. Even with all the records shown, these few records do not show up.
Could these few records have a feature or setting that is disabled that needs changed? If so, what to look for at this point.
View 11 Replies
View Related
Oct 13, 2015
Basically I have one table with all relevant information needed, etc, and I have the original spreadsheet data inputted to the database. The original data appears fine when a search is done, eg, the user searches for something using a form that then uses a query and ultimately brings up the information that matches. In the queries, I use the same basic criteria
"LIKE "*" & [Forms]![FormName]![FieldName] & "*" "
My question is, now that I have a form to add a new record to the table so a user can add to it, what would be the best way to get the added record to appear during the search, like the original records? I have used " IS Not Null" at the end of the criteria within the query, but it tends to bring up all the new records no matter what the user searches.
View 3 Replies
View Related
May 20, 2014
I am trying to write some code to search for similar strings. I am creating a database with records that all contain street addresses. These addresses may have more than one record attached to it, and we would like for folders to be created containing the records with similar street addresses. Problem is, all the existing records are from an excel spreadsheet that did not contain any data validation, so there are several instances of:
123 Street
123 st
123 street job 1
123 st job 2
etc....
So I am trying to write code to prevent this from happening in the future, by searching the database for a similar street address and asking the user if this is the address they are trying to enter. I have been trying to do this with the DLookUp function, as such:
Private Sub ProjectName_AfterUpdate()
Dim stLink, pName As String
pName = Me.ProjectName
stLink = DLookup("[ProjectName]", "tblMaster", "[ProjectName] LIKE '" & pName & "%'")
If IsNull(stLink) Or stLink = "" Then
[Code] ....
I have worded the stLink line different ways, and have used (*) instead of (%) but nothing is working. The CODE is working, as in no errors, but it is not finding a similar project that I know is present.
View 14 Replies
View Related
Apr 21, 2014
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 & "*')"
[Code] ....
View 5 Replies
View Related
Apr 11, 2015
Below are what I have created.
Table 1 with 3 fields:
- UniqueID
- field1
- field2
Form 1
- text box to enter UniqueID
- button to save the new UniqueID and date/time stamp
I set the Default Value for UniqueID in Table 1 as Now()
Each time I enter a new UniqueID and click the button, a new record will be created in Table 1 with the entered UniqueID and current date/time in field1
This form seems to work well.
Form 2
- text box to enter UniqueID
- button to update
How I can use the UniqueID entered into the textbox of Form 2 and update the corresponding field3 with a new current date/time stamp?
View 1 Replies
View Related
Mar 14, 2013
I have created a form for a table which contains ~600 movies and their name, genre, rating, director, year it was made, and length (min).I need to be able to enter numbers into the two Year boxes, and then it filters the movies in my database and only shows me records from between those two years. The years in my database are just in one column in the format of: XXXX e.g. 1996 etc..I've tried the code:
Code:
Private Sub Year2_AfterUpdate()
Me.Filter = "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2
Me.Filteron = True
Debug.Print "[Year] BETWEEN" & Me.Year1 & "AND" & Me.Year2
End Sub
Year1 and Year2 are the boxes Year: and To: respectively. [Year] just being the column name which contains all my years.When I try to run my query it shows me my records, but it shows me all of them! It doesn't filter it at all!
View 4 Replies
View Related
Mar 6, 2013
I have a column that has a bunch of keywords they are separated by comma... so for each row of that column it will have a few different keywords example: lake superior, river, mountain, lake wollongong
I know its a bit of a nono with databases to have columns with comma separated text.. well so i read somewhere anyway but the document i have been handed to work has hundreds of rows in this column with up to 14 keyword entries.
I have a form that searches through Item's names based on 2 keyword boxes.
Here's the criteria in my query:
Is Null Or Like "*" & [Forms]![frmItemView]![SearchPhrase1] & "*" And Like "*" & [Forms]![frmItemView]![SearchPhrase2] & "*"
This will show all records when both keywords are blank, and filter records using the 2 keywords otherwise.
I have a form with two text boxes and have set the correct values as outlined in Evans post. I then have the query set to run via a button. I run it but it will only give back records for the entry i have put in the 1st text box. This would work well for me otherwise... mine is like this:
Is NULL or Like "*" & [Forms]![Searchtable]![Key1] & "*" And Like "*" & [Forms]![Searchtable]![Key2] & "*"
Searchtable being my search form
key1 being my first text box entry
key2 being my second text box entry
View 1 Replies
View Related
Jun 21, 2005
i have a form porblem. I have a search form embedded onto my main form its really a listbox that when i click the records in it they display the record.
prob;em is that when i delete the record it still shows in the listbox
View 1 Replies
View Related
Nov 14, 2013
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code:
Private Sub Command18_Click()
On Error GoTo errorcatch
Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
View 3 Replies
View Related
