setting up a database for my department at work and have a few questions
a.) i need to set my form to have a search or filter based on user criteria
1.) users need to be able to select themselves from a drop down list
that's based on a table in order to filter their cases from everyone
elses
2.) instead of using the drop down menu i'd like them to be able to enter
an ID number that pulls up a specific record rather than first selecting
all their cases and then navigating those until they come across the
case they're looking for
How can I create a "Filter Button" on a form and filter my records? I create a textbox on a form and a filter button on the right. Then I click the filter futton, the filter function will search/match the content in the box through the datasheet. And then the results of the filtering will be pop up on the split form datasheet.
I need to navigate through all records for a table where a <field> = a certain "string". Ideally I would have a next record button that would know to ignore records that do not meet my criteria?
Simply put, Next available record where <field> = "string"?
I use Access 2003 and saw Ken Higg's sample database to filter records in a form using a combobox. I managed to replicate his settings and wondered about an extension to the principle, but I don't know if it's possible.
In the sample, a value is selected using a combobox and all matching records are filtered so it's possible to scroll through them. Is it possible to have a second (or more) combobox on the form so it is possible to filter on one or another criterion? This would mean one combobox filling the other one and I realise that the one that Ken provided is unbound.
The reason that I'd like to do this is, for instance, if there are records with Surname, 1st, 2nd and 3rd lines of the Address and Postcode, I may wish to filter records by entering the Surname or Postcode. Each of these fields would have to be displayed in a combobox. As the sample is set up at present, only one of the Surname or Postcode has to be selected via the combobox and the other is a textbox.
It may be that what I'd like to do isn't possible - or someone may have come up with some other idea. I'd prefer to have the search/filtering on the form, rather than using the buttons on the Form View toolbar.
vba code for filtering the records of the form made out of a table. The form contains combos that contain the lists of unique records in several colomns of the source tale. So i need to filter the records of the form using the combination of the values of that combos. But can't even apply a single filter using this code:
"Business" is the name of the column in the source table, and the "list59" is the name of the combo which contains the unique records of the "Business"
the code takes place as a button "apply" is pressed
I am using combo box to filter records from a form. The data source for the form is from a query.
I use the combo box within the query to filter the data, I would like the form to refresh/ run the query again every time a new selection is made from the combo box.
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
I want to filter the records in a combo box (cboSupplier) using the In() function.
On the form I have an unbound control called "intFilter". It contains the following string: 23, 58
The SQL statement for the combo box is
Code: SELECT SupplierID, SupplierName FROM tblSupplier ORDER BY SupplierName;
In the criteria for field SupplierId I want to use the In() function so that the only records returned by the combo box are those in "intFilter". I have tried
Code: In (Forms![frmSupplierReport]![intFilter]
but this does not work.
If I put: In (23, 58) in the criteria it works, but I cannot hard code it because the string in "intFilter" will change on the fly!
I have been building a database for use in a charity shop and am struggling with an issue regarding one of the forms.
I currently have a form which displays all expenses from the shop. I have added a combo box to the top of this form which allows users to filter records based on an expense ID Code. It all works fine but I would like to be able to add a start date and end date box to the form also so that records can be shown between two dates.
I have tried adding parameter boxes to the query which runs the form (which works) but the issue I am having is that when a new id is selected from the combo box the parameter boxes pop up again asking me for start date and end date again. This happens every time a new combo box id is selected.
I think the way resolve this issue may be to add a start date and end date box to the form but I don't know how to implement this.
Please see the attached files for images of what I currently have. The forum won't let me post images directly here until I have 10 posts so I have had to attach the files instead.:
Query running the form - Attachment 1
The Form itself - Attachment 2
Combo Box - Attachment 3
Bound Column on combo box - Attachment 4
Code in Combo Boxes after update event - Attachment 5
The recordsource is a query with over 6,000 records. The form currently lists the records in datasheet format with header and footer for things like buttons and filtering. The client wants to be able to go from page to page of the souce query, showing 100 records on the form at a time.
But at the same time, they should be able to filter or sort the data source in it's entirety. The person who created the form came up with what seems like an awful solution to the problem. It seems to use a random number generator to determine how many records to portray at a time. I see this in the code as well as in operation, because the number of records on page to page varies. It doesn't even start out at 100! Worse yet, using a sort on the page only sorts the records that are visible.
I have seen a few articles here and there on using a combo box to filter records in a sub-form and to filter records in another combo box, but I am not getting anywhere. I hope someone can belp
Exercise 1
For this exercise, I have the following tables:
tblClients containing client names tblProjects containing some project details
I want to set up a simple form, so that the user can select a client name from a combo box on the main form which filters the project detail records in the Projects sub-form. Once the user enters project details, I want this info as well as the selected ClientID to be fed back to tblProjects.
Exercise 2
I have the following tables:
tblProjects as above tblWorkstream containiig names of workstreams and some other details.
Every project has one or more workstreams
I have a form where users will enter hours worked on each workstream. They will first select Project from a combo box on the main form. This should then filter records to be displayed in the Workstream Combo box, before they can then enter hours.
Please let me know if you need me to explain any part of this better.
Thanks in advance.
ps. I am a novice, so I'm hoping to do the above using default MS stuff, not with complex programming!!!
When I click the filter button I get a blank message box titled 'Microsoft Access" and an OK button, when closed the filter works perfectly.I have checked this procedure in other forms and it works without showing the blank message box.The only difference with this form is that its control source is a union query.
Hi, I have this table with job numbers stored by days of the week e.g. mon job, tue job, wed job etc. You can have the same job number everyday of the week or different ones. I have to get a report that shows JOB # ??? and all of the fields associated with it. I'm trying with a query, but can't get it! How can I accomplish this?
I have set a query to carry out a list of companies from a table based on two criteria. However, the same company could appear two times in the results .
Is there a way to requery results to filter records that appears two times and leave them only one time in the final list? Thanks in advance
i have a table with 20,000 records. one column includes names. another column includes multiple dates the person visited a doctor, and another column includes dates the person visited a hospital. i am only interested in the last date the person visited a doctor and the last date the person visited a hospital (if they had any visits to the doctor or hospital). I am also interested in members who have had no visits to the doctor or hospital. any suggestions on how i can filter the data to only return these records? thanks
Name Date Visited DoctorDate Visited Hospital Nigel01/01/1980012/31/1995 05/20/199906/06/2005 03//02/200405/06/2004
For example, I only want to return
Nigel, 03/02/2004 (Date Visited Doctor), and 06/06/2005 (Date Visited Hospital). thank you. i really appreciate any help you can offer.
I want to write a code that upon opening the report it looks at the sumofamount field and only shows me the records that the sum is greater than $50. It currently shows me all the records and it they are less than $50, it comes up as a negative. I only want to see the one which have amounts over $50. Would I write this in code and have an even procedure upon opening of the report? I should also add that I have the amounts listed out the bottom of the page, so I can't just go into the query and put >50.
Thanks. ="" & [first name] & ", for the month of " & Format([date],"mmmm") & " your DHL bill came to " & Format([sumofamount],"$#,##0.00") & ", the company allows you $50.00 per month, this leaves you with an overage of " & Format([overage],"$#,##0.00") & ". This amount will be deducted from future commissions. Below is an itemized list of your charges. If you have any questions, please contact me at extension 4491."
In my database I want to create a report that identifies if some is available or not every day. I have a general table(Tbl_General) with names and contact info, in a second table(Tbl_Recall) I have 3 fields Location StartDate EndDate. I want to show every person from the general table and only records from the Recall table where the start date is before today and the end date is after today.
The queries I write exclude some records if I limit the dates and the reports I build show all records in the recall table.
In access, I have the following: 2 sub forms located in an overall form. I would like when I select a record in the first subform, that the second subform would automatically filter for all records that contain the same ID Value.
I am trying to build an expression in a query that will filter specific numbers when the value is entered. I would like the value box to read something like "Please enter the file number" and when entered, see example of file # in brackets (001-200-001), it will show those records with that file number only. In addition, because I will be printing it from a report, the filtered file numbers should also show up in the form.
A form I created contains buttons for the user to restrict the display- showing records that are tasked to one specific employee. So their choices are Employee a, employee b or all employees. This is a process I use a lot :) This last form opens with NO records displayed. When a button is clicked the records show correctly. Any idea what to check? Thanks,
I am importing about 1.5 million records from a .txt file into an Access database. I have the code that splits the records and adds them to the database. However, the database has a 'specialty' table that lists certain specialty codes. The incoming record has a field that is a specialty code. Any way to search the 'specialty' table for the code that is coming in on the record to be added, and then, if the record 'code' is not found, skip it, and if it is found, add it to the database. There are about 70 codes in the 'specialty' table.
Made some tables with data, then wanted to filter it by form with comboboxes approx 10 of them, so started to write a simple query but it doesn't really work. Then I found this forum and when started to review some threads, noticed that some (most) of you write a query in vba under a button with on_click function.
The question is, what is the correct way of writing a query? Is is a vba code under a button, or is it a code in the actual query?
It have several fields, and the fields that I want to filter is ItemNo and SellingPrice.The problem is ItemNo has many different SellingPrice. I put a parameter in SellingPrice field as >=75 only for ItemNo "49565", but still i want to show other record in ItemNo and do not have filter in SellingPrice field.
I can create a dropdown list to filter my form (text) however im struggling to get back to showing all the data prior to my drop down selection.
ive tried refreshing and requerying with no success.
I would either like to get a drop down with the addition of "all records" in it or a refresh button.
the form that this is on is a sub form.
I could reference the drop down to a number if required.
Additionally: When I have selected my choice from the drop down box it blacks out "selects all". Is there away of making the list just select and lose focus so to speak.
