I have a query based on two combo boxes in a form that links to a report, it works fine. What I want to be able to do is select criteria from one box and if the other is blank have it include everything (right now if one is blank the report will be be blank) and vice versa. Any suggestion on what to enter into the criteria section of the query.
Thanks in advance
Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)
View 6 Replies View RelatedI just started using Access a couple weeks ago. I bought a couple books, have read some help stuff in forums webwide, but I am still stuck on the basics of how to do the following~ Please Help ~
My DB:
I have a single main table, in addition there are several mini-tables used to support combo boxes for form inputing.
My Mission:
It's been requested of me that My Main Switchboard have an option to lead to a secondary form where the end-user can "quick-filter" records to generate a table or report based upon dropdowns and check boxes for 9 different search variables- the data will then be pulled from the original data table based on the 9 choices (or less, as there will be often certain variables left blank).
My Questions:
1) how do I tie the values from the combo boxes in my new form to supportive queries? (perhaps a parameter query with a macro that searches that particular combo box for values??) - or is there a better way to do this?
2) how do I tie all those queries together so that some of the 9 choices can be blank, and of the others the assurance that they'll stack- or further refine the results.
3) one of the search criteria is Industry Sector. there is a mini-table to provide the values for the drop-down, but in the main table there are four columns in case a particular company falls into more than one sector. will this present any problem or can a query be easily tailored to search any of those four columns for that value?
I want to thank anyone who cares to help very much, in advance. I know my issues probably seem very simple, but I've been tasked this duty of learning Access and working the company's DB on my own. The "comprehensive" books that I've purchased haven't seemed to clarify this particular issue, so here I am. :o
Hi,
I currently have a select query that has a where statement to select a value depending on the value of a combo box in a form.
WHERE (((tblSampleSubmission.SubmissionNumber)=[Forms]![frmReportPreview]![SubNumber]))
What I am wondering is there any way that the where statement can be for another form depending on what form is currently active by using an "or" statement?
Basically what I am trying to do is make the db as easy to use as possible and not have to type in the value again.
I have a rather simple query that will produce filtered results based on the user's selection in 1 to 4 separate combo boxes on a form. But I can't get it to do what I want it to do. Here are my vitals:
Form = frmQryAdHoc
City combo box = cboCity (from tblWARNData)
County combo box = cboCounty (from tblCounties)
MWA combo box = cboWMA (from tblLookUpData)
Query = qryAdHoc (source = tblWARNData)
CoCity column criteria = [Forms]![frmQryAdHoc]![cboCity]
CoCounty column criteria = [Forms]![frmQryAdHoc]![cboCounty]
CoMWA column criteria = [Forms]![frmQryAdHoc]![cboMWA]
The query works fine if I make selections in all 3 combo boxes. However, if I make a selection in only cboCity, the query returns no results because it doesn't find a matching record where the city = cboCity and the County and MWA fields are blank.
Here's how I want it to work:
If cboCity = Detroit and cboCounty = Wayne, then
Results = 40 records (where city = Detroit AND county = Wayne)
But if cboCounty = Wayne and cboCity & cboMWA are blank, then
Results = 120 records (where county = Wayne; cities & MWAs = various)
IOW, I want the query to filter only on the cbo's where a selection has been made by the user and ignore any empty cbo's. What statement do I need to enter in each cbo-related criteria field so the query filters only on the combo boxes where a selection has been made by the user?
Any help you can provide will be greatly appreciated. Thank you.
Hi all,
Im trying to do the following query work.
I have a form, with combos , text fields which are filled in with parameters.
The query I want to make is complex.
For example we shall use field1, field2, combo1 , combo2 for explanation.
I want the field1 param AND the field2 AND combo1 AND combo2 to be evaluated for a result. At the same time, I want the user to fill in only field1 and combo1 or like that and the result to be right.
Is there any possibility to make them all work at the same time, individually, or in combinations ?
Sounds too Complicated ?
I ve managed to make it work only by filling in individually the fields or combos and have the right results. The other ways didnt.
Desperately need your helpppp. :rolleyes:
THNK U
Hi All,
I am currently having problems with a multiple criteria query with combo boxes (see attached file).
The problem:
- When user defines all 3 criteria (e.g. selects Project: A, Category: Services and Equipment Type: Packages), the query returns a result - no problem here.
- When user defines no fields (i.e. all combo boxes empty), query returns all records - again, not a probelm.
- However, say a user wants to display all records belonging to just Project: A, for example, the query returns no records. Obviously, there a a number of combinations of this (another example would be a user wanting to show records from project: B, with Category: Equipment Type).
I have included the Is Null statement in the query but to no avail. Could anyone advise on a solution based on the attached database. Your help would be greatly appreciated!
Kind regards,
Stephen
I have a form with combo boxes that works beautifully, but I've been asked to add another feature to it. It requires adding a button that runs a query and displays the query results on the screen.The query code is:
Code:
SELECT DISTINCT Product.MSDS
FROM Product INNER JOIN tblStoreProducts ON Product.[ProductKey] = tblStoreProducts.[ProductKey]
WHERE (((tblStoreProducts.MaxUnits)<>0) AND (([Product.HazardKey])<>79))
GROUP BY Product.MSDS, tblStoreProducts.StoreKey
HAVING (((Product.MSDS) Is Not Null))
ORDER BY Product.MSDS;
One of the existing buttons on the form has this code behind it:
Code:
' btnHMIS_Click
Private Sub btnHMIS_Click()
On Error GoTo Err
If IsNull(Me.cboCompany) Then
[code]....
As you can see, the button is able to pass the parameters (which Company, and which Store within the company) to the report.
Code:
' btnMSDSSheetsPrint_Click
Private Sub btnMSDSSheetsPrint_Click()
On Error GoTo btnMSDSSheetsPrint_Click_Err
[code]...
How do I pass the StoreKey information into the query? Is it my query that's wrong?
I have strange issue when creating auto populated text boxes which displays rows from combo boxes.
In one database auto populates works with simple text box Control Source edit "=[ComboName].[Column](NumberOfColumn)".
Somehow same method doesn't wotk in different database: here one time I have to insert VBA code at On change Event:
Me.TextBoxName = Me.ComboName.Column(NumberOfColumn)
And other time it wont work with On change but only with After Update Event (code is same).
Another thing this morning happens was that when I tried to add new record trough form where combo box and tex boxes are located, MS Office suddenly stopped working after selecting combo box selection (with message Microsoft Office has Stopped Working). It is 2013 version.
I got it work after deleting and re-inserting VBA code to autopopulate text box at After Update Event.
I currently have 3 tables within a database with student details of three different classes. I need to create a user form that has a dropdown box which I can select a student from one of these tables with a number of text boxes below which brings up all the students details, then once the student has been selected and the correct details are shown then I need to create a button which allows me to move that student from one table to another.
View 4 Replies View RelatedI'm trying to build an database for aircraft operators. I've got the basic tables structure and relationships but I'm stuck on building an search form to filter records by user input.I've got following controls on my form (unbound):
1. AircraftType (combo box) from tblAircrafts
2. CompanyName (combo box) from tblListOfAircraftsOperators
3. TeailNumber (text box) from tblAircraftOperators
4. AirportNameSearch (combo box) from tblAirports
5. PassengersNumber (text box) from tblAircraftOperators
6. ManufactureYear (text box) from tblAircraftOperators
7. SourceSearch (combo box) from tblInfoSource
8. CountrySearch (combo box) from tblCountry
9. CategorySearch (combo box) from tblAircraftCategory
10. EamilToOperator (text box) from tblAircraftOperators
11. InteriorPhoto (Bound object frame) from tblAircraftOperators
12. ExteriorPhot (bound object frame) from AircraftOperators
I need to enable users to search for aircrafts based on those criteria. As I mentioned I'm new to Access and I don't have any advanced coding skills. I have a query build to perform the search and this is the code I've managed to write so far:
SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber, AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto, AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType
FROM tblAircrafts INNER JOIN (tblAirports INNER JOIN (AircraftOperators INNER JOIN tblListOfAircraftOperators ON AircraftOperators.CompanyName =
[code]....
My company is finally using my accounts package (alongside our original package until we can be certain there are no bugs).
Anyway just adding nice to have features at the moment.
I've got a form that has 5 combo boxes where the user can select 5 customers. Then a button so they can graph the amount of money generated by each customer and compare them.
I've just made a query to return the top 5 highest grossing customers.
What I what to do now it feed the query results into the combobox values when I press a new button on my form.
How do I go about this?
I am creating a query that should filter records of events based on multiple fields. The filters should work with any combination of field criteria, but only two of the four field are working properly, as follows:
1. a text box for searching with event name (free text) - this is working;
2. a combo box to filter events by country name - this is working;
3. a combo box to filter events by event's keyword (category) - this is NOT working;
4. a combo box to filter events by year - this is NOT working
there is a way to convert multiple text boxes to combo boxes all at once, rather than right clicking on them one at a time, and selecting Change to.
I have a form with about 50 fields and most of them need to be converted to combo boxes. I'd always done it manually one at a time up to this point, but I'm trying to build up my learning and look for smarter ways to do things.
I am trying to make a search option in my form header. Right now I have two unbound combo boxes (CboAccountsfilter and cboCourseName) that I can use to filter my records. Currently, I can use the drop down for CboAccountsfilter and a list of accounts will appear. When I select one, the corresponding Course Names will appear in cboCourseName. This works fine...Code below. I would like to take the filtering a step farther and add checkboxes to filter the data. I my form, there currently exist several check boxes (yes/no)...(Priority, Rep Top Target, Manager Top Target, ect). I would like to have the option to use a check box to filter. I.E if I had a checkbox in my header called PriorityFilter, if checked it would only bring up those records that met the two combo boxes criteria and was a priority.
Below is the code I have so far...it doesnt have anything for the checkbox because I am at a lost of how to get started.
Private Sub CboAccountsfilter_Change()
Me.Requery
Me.cboCourseName.Requery
Me.Check178.Requery
End Sub
[code]...
I managed to get separate combo boxes to filter out results on a query, however now I have a slightly different problem.
I managed to get the combo boxes to filter records in different query criteria, i.e criteria A for field A, criteria B for field B, etc.
Now I would like to have several combo box filters in one criteria field, however no matter how much I try to move the code about, they either filter out nothing at all, or show up blank records as they're filtering one after the other, i.e filter for A, and then B, which clearly won't work as there will be no B if you've already filtered for A.
The code I'm using in each criteria box is;
Like "*" & [Forms]![MyForm]![Combo1] & "*"
and this works as a single criteria in a single field.
I have a table with all information on it, that is input via various forms, I then have different queries pulling information from all information to run reports off. These all work fine, my problem is my 'Search Form' - below
I have created a query that finds information from 'all information' using
Like "*" & [Forms]![SearchAll F]![txtDateRasied] & "*"
This is working on all text boxes, It only half works on the combo box's when I use
Like "*" & [Forms]![SearchAll F]![combofailureanalysis] & "*"
If a selection is made in the combo box the query brings the correct results, however, if all the fields are left blank it should bring up every record, but it doesn't do this. I am certain it is the combo box's that are causing this anomoly as when I remove the combo box criteria it works perfectly again.
Ok so im working in MS Access 2007.
I want to create a query based on 3 combo boxes but have it so that if the field in the second or third combo box is not populated the query still runs.
Right now i have the Criteria set for the three columns that i wish to sort by as seen below.
Column 1
Criteria: [Forms]![Entity Selection Form]![areabox2]
Column 2
Criteria: [Forms]![Entity Selection Form]![devbox2]
Column 3
Criteria: [Forms]![Entity Selection Form]![entitybox2]
This gives me the correct query result but im forced to make a selection from each combo box. Is there a way to progamme it so that if I only make a selection from the first combobox and leave the others blank i can still get results in a query?
Another quick request:
What would the code be for a button which clears the contents of a text box or a combo box on a form?
Many thanks,
Paul.
I have a form with cascading combo boxes pulling from a table. They work perfectly, no worries. My problem now is if I do not enter information in every combo box (i.e. only two out of four combo boxes), how can I still run the query and get the appropriate information?
For example (these are my combo box titles in order):
Product
Type
Customer
Contract #
I don't want to necessarily look by Contract # all the time, but sometimes just by the general Product and Type to get a larger view. How do I set up expressions/criteria in my query to accurately produce that information? Right now it just produces a blank query table if I don't fill out all the boxes.
I've tried a couple of expressions with "isnull" criteria, but I must be doing it incorrectly.
Hi guys, I have a related databes with about 20 tables.
My main table stores the data linking with most of the other tables. This main table stores football match records with player line ups. Initially I had a problem linking the 11 player fields in the main table to the player table, Icould only do it with 1, so someone at work suggested to link it using the lookup function. This worked brilliantly. However, now I am designing a GUI with forms but the forms have combo boxes where the lookup function was used and when i change these to text boxes, the players names are replaced with their ID numbers.
Is there any way of creating the form without the unsightly combo boxes, as they won't be needed, the GUI will be read-only. I look forward to your help!!
I have a table that has client names and addresses. I have designed a form to be able to invoice these clients and everything is fine however what I'd like to do is have the address of the client appear automatically.
I have set the client names in a combo box and would like their address to appear in either a text box or sunken label automatically from the table. Is this possible and how do I do it.
Please keep in mind that I've done basic programming so please be kind to this newbie :D
Hi everyone,
I have been making progress with customizing a
MS Access program, but one major problem is that
I have been trying to make a List Box or Combo Box
that I can use to enter data in the TABLE, but I
find that I get a pull-down list that has the list of
values from only the parameter that is primary key.
More specifically, the program is set up as follows:
I have two tables in this program: One that is called
"invoices" and one that is called "items". In both of
these tables, there is one common parameter, which
is "Item Number". Item number is the primary key,
and I used the "relationships" function to tie this
parameter to itself between the two tables.
I was successful in setting up a list-box for the
"Item Number", but when I try to set up a list-box
for another parameter that is supposed to display
a person's initials, the pull-down list displays the list
of item numbers instead of the list of people's initials.
In fact, I don't know if there is an extra step I need to
take so that the database stores a list of people's initials.
Instead, I just fill in the initials in the field for each
record for which the "Item Number" is the primary key.
How can I get the list-box to pull down a selection of
different people's initials, or in other words how can I get
all the people's initials to be stored so that the list of
initials can be looked up. Just so you know, I have tried
different choices of entries in the "Lookup" tab in the
Design mode of Tables, including Display Control,
Row Source Type, Row Source, and Bound Column,
but the outcome is that the only parameter that I
get get in the pull-down list is the primary key, which
is Item Number.
I appreciate any help you can offer in explaining how
to correct this.
On a separate note, one of the parameters is "Date", and
on the reports, I'm trying to figure out how to filter a
specific date range so that I can limit each report to a
specific month. Please advise me on this procedure as well.
Thanks.
I have several comboboxes (6) on my form.How to populate these comboboxes with values depending on selected value in previous combobox.
Example.Lets say that you select value "Audi" in combobox 1, then available values in combobox 2 should be "A4","A6","TT" etc. and if you selected "BMW" in combobox 1, then available values in combobox 2 should be "3-series", "5-series" etc...
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies View RelatedHi
I have a combo box and its row source is a query that selects distinct values from a column in a table
However i would also like the user to be able to select nothing/null value from the combo box. How do i do this.
Thanks