Hi,
I just want to create a search on my form where i input a date, and then press a command button that will jump to the first occurence of that date in the table. I don't want a filter, since that just excludes all the other data without that date. I tried playing around with FindRecord and GoToRecord, but to no avail, due to my limited knowledge. =P Thanks in advance.
G
I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.
View 3 Replies View RelatedI want to add a filter search on my forum which will look up load numbers based on the selection from 3 other combo boxes. I already have two combo boxes; Customer & Load. When the user selects a customer the Load combo box grabs all load numbers from the database with this customer name. I would like to add two more filters, Part Number, and Date. The only problem is I don’t know how to set my load combo box to only select values based on the other 3 combo boxes (Date, Customer, and Part #). To clarify my question, please take a look at my database example.
View 2 Replies View RelatedHello all,
I using a cmbox for a search filter on a single form. The code that I'm using so far is....
Private Sub cmbCategory_AfterUpdate()
'Search filter for Category Combo box
Form_ClipsViewer.Form.Filter = "Category ='" & Me.cmbCategory & "'"
Form_ClipsViewer.Form.FilterOn = True
The code works great if there is a record for the category you choose, but if there isn't one it freezes up. I need to write some code that checks the records for this value before it executes. If there aren't any matches then it stops and brings up a msg box
Stating something like this
MsgBox "Sorry, there are no records for this Category", vbOKOnly, "Category Search"
Thanks,
Rich M.
Hi folks. I have started getting back into Access at work. It's been at least 3 or 4 years. The company I am building this db for is using '97.
I am trying to build a form with a search button and a text box to type a search string.
The form also has a subform which will display or filter the records based on the search criteria
I have tried to adapt "themightydude's" code to work, but i'm not getting anywhere...
I am truly trying to get back into the programming mindset, but have a "writer's block" at this moment.
I really want to understand how VB works. (& Sadly, I know it's one of the easiest languages to understand...)
here's what i have so far.
Private Sub Search_Click()
Dim GCriteria As String
If Len(cboSearchBy) = 0 Or IsNull(cboSearchBy) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriteria = cboSearchBy.Value & " LIKE '*" & txtSearchString & "*'"
'Filter frmCustomers based on search criteria
Form_GlobalSearch.GlobalSearchSub.Form.RecordSourc e = "select * from projects where " & GCriteria
Form_GlobalSearch.Caption = "projects (" & cboSearchBy.Value & " contains '*" & txtSearchString & "*')"
MsgBox "Results have been filtered."
End If
End Sub
I have also attached a picture of the search form and a copy of the db for reference.
Sorry, I know this code is probably WAY off. Any help is greatly appreciated.
Thank you all for your help!
ok i would like to create a very specific search form. the form has many fields but they all belong to one simple table.. Enquiries.
i know this table isnt normalised but this section is separate from the database and i need this certain filter feature on the enquiry form i have made.
a screen of the form can be found here.
http://bb.1asphost.com/antonyx6/enqform.jpg
the top 3 combos contain all the records for that particular field.. the first bit of info i need is how to alter the statement for the combo lists to ignore values with no entries..this is because not every stored enquiry will contain an enquirer's forname.. is this correct for my Forname combo....?
SELECT zEnquiries.Enquiry_ID, zEnquiries.Enq_Forname FROM zEnquiries WHERE zEnquiries.Enq_Forname is NOT NULL;
i will take things one step at a time.. so if i can clear this up first i will describe what i want to happen next if thats ok..
thank you.
I have been hunting around to find exactly what I need but cant seem to find it...
what I need is a way to do a string search within a particular field (surname) and then filter by that string?????
Any ideas or point me to the right thread will be much appreciated.
i want to create a button that allows the user to search the db through the form,click the button textbox pops up and they enter the claim id they want to edit
i'm only really worried about searching the for the one field "claimid". can i do this in access?
Hi there, I currently have a search subform that will search for a specific record based on an ID or Name. However I do not want all the records to be filtered. I still want the user to be able to traverse through the other records.
Is there any way that I can disable the filtering?
thanks
I have a form in access where i need to select a record between two different dates. For example i have a "valid_from" and "valid_to" field. I have an unbound text box with short date format and calendar control inserted for users to select a date. This is named "drpdate".
I have a bunch of other filters also in the same form. Now my issue is that i have not been able to figure out how to put in a SQL statement which would give me the data which is between the "Valid_from" and "valid_to" fields based on the date selected in the unbound text box.
If the selected date does not meet the criteria, then it needs to be give the results from another table (which i have already done).
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!
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
I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. I created almost all the tables (all that I need for now) and made the relationships.
However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.
Now here is my problem: The User usually searches for the respective family by the Group ID. I implemented this with a search query using the ID number of a search text box. All done and no big problem.
But sometimes the ID number is not known so the user needs to search by name (First and Last Name). I use to different textboxes for this and it works in a similar way like the number search by query (Like "*" & [Forms]![frm_SearchIC]![txt_LName] & "*"). All still good However since most of the clients are actually from arabic speaking countries, converting the names into the Latin alphabet is bound to fail and produce a lot of misspellings. Therefore I added 2 more textboxes and 3 comboboxes for the user to give more information about the client and therefore make it easier to search for the person. I was able to produce a query which gives you the right result if you have ALL information at hand. However, this is not always the case.
1) But I cannot find a way to tell the query that if the a certain textbox or combobox is empty, it shall just "ignore" it and use the information at hand. I tried this in the query by adding in the criteria OR .... Is Null. This is alright for one or two textboxes but for the many I have, it seems to be too many different combinations for the criteria. It just worked with some fields but others always had to be filled in...
2) If no information is given at all, the database should inform the user that he needs to enter at least on field. If nothing is found the user should get a msgBox saying "No IC matches your criteria".
3) The results of the searches should be given out in another form where the user can pick the person from 1-to-many results.
I attached a sample database with sample data and reduced tables, fields, and entries ...
I wanted to build a dynamic search form using text box instead of the common combo box type.
I found an example that used the combo box and the searching portion of the code is as followed:
Code:
If Nz(Me.txtID, "") > "" Then
If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
strFilter = strFilter & "CategoryID = '" & Me.txtID & "'"
bFilter = True
End If
How to insert (Like "*" & Me.txtID & "*") into the code to make the dynamic search using text box possible.
I'm trying to implement a macro to filter through accidents to see if my company has already been hired on a particular case.
In the first form the user enters the date, location, and last name of one of the drivers. Then they click a button which opens a new form as a datasheet with accidents that match ANY of the three controls. Evidently, I need a filter that contains some form of a series of OR operators.
However, whenever I try any variation of a button that opens the second form and then applies the filter, I always get no matches.
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
My department and limited the users to only specific tables based on their need and role. However, users have expressed the need to view past work. I do not want to give them access to the table - I am thinking that there should be a way to create a report that shows the data, but how would I set up a search filter option so they can find the one record that they are looking for?
View 2 Replies View RelatedI have a problem with my database I have a combo box that will search for my record. Actually its working I input the specific number it goes to the specific record. But I want, if there no existing record in my database it will display a Messagebox that "No record Found" I try to put a code in a macro builder in a after update property field but nothing happened.
Expression code that it will display the msgbox if there's no record found.
the given code from macro builder is attached. I try to have an if else statement but I dont know how to not equal that giver conditional expression.
Access 2003
Trying to filter a form based on a field with wildcard. My form has a txtCustFilter control where a customer's name can be entered in part or whole. The Customer's name is in PCCustomerName
This code works but, I'd like to make it case insensitive
Dim strFilter As String
strFilter = "[PCCUSTOMERNAME] LIKE ""*" & Me.txtCustFilter & "*"""
Me.Filter = strFilter
Me.FilterOn = True
I am trying to provide the user a custom search feature. They want to enter a keyword or phrase and search 3 memo fields and filter the form base on the records found. they also want to be able to search the whole phrase or any part of the phrase.
I have a like expression for any part of the phrase but I when I set it up for whole phrase it doesn't work. Even if I run a simple query and use
For example: There's an acronym the user is looking for : ACA
If I set my query up like this: [field1] like "*ACA*" or [field2] like "*ACA*" or [field3] like "*ACA*"
it not only finds records with that acronym but it also finds records where that combination is found in a word, for instance vacate.
How can I set up my query to find the whole word?
Hi there, I'll give as much information as I can. This is my first post, I'm used to programming in Delphi but someone set me this task in Access/VB so I'm practically a newbie.
I'm creating a system for a youth project. All Youth Workers want in depth access to the list of kids in their area. Currently, all young people are stored in one table. The staff can access the list of young people via a form that populates Name/Age/IDnumber (basic information) from a report based on the criteria project = 'project_name'.
I have set up another form that displays the individuals in depth information.
I would like to access the indepth individual form from the list by a dblclick event.
but, I can't work out how to get it to open to the appropriate record.... any ideas?
Hi
I have a combo box to select a record on a form.
I would like then record to lock once it is selected via the combox box and the combo box to clear.
If a records is slected again from the combo box it will then show the new record and lock it again.
The problem is users are sometimes using the mouse button to scroll and moving to a different records by accident.
Thanks
This is the first time I've used this forum so, hello everyone
My first question is about filtering a form
When a user is in the menu screen and selects a command button to view details, the next form that loads shows every record within the main table. So the user can use the navigarion buttons to go through each record.
There's search criteria on this form so the user can select which ever record they like.
Because of speed issues, I'd rather the form filtered a record (whichever one) as soon as they pressed the command button.
I've thought about how i'd do this and can't really come up with much; looked at openargs but my VBA isn't that hot yet
Does anyone have any ideas?
Thanks in advance
Help needed again please
I have managed to link a report to a form where you can double click on the surname and the report opens automatically BUT I want the report to only show the data from the record that I have double clicked - at teh moment the report is coming up with thousands of pages - one for each record.
Can anyone help me with this - I think it's probably something simple but I can't work it out on my own :eek:
Hi
I have a form which displays records of our products, on that form I have now put a list box which displays the ancillaries to the products.
both the form and the list box run off queries.
I have the same unique identifiers in both queries.
I need to filter the listbox by the unique identifier every time I move record.
At the moment the list box stays populated with all the ancillaries, how do I filter it programatically to list only the ancillaries with the same unique identifiers as in the current record?
Thanks a million....
I have this on the Form - On Current Event to show how many records I have on the Form, However when the employee filters the combo box's I want the record count to show the current records when they finish each filter. At the moment it just shows random numbers.
PHP Code:
Private Sub Form_Current()       If Me.NewRecord Then        Me.lblRecordCount.Caption = "New Record"    Â
Else        Me.lblRecordCount.Caption = _         "Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount    Â
End If    End SubÂ