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)
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.
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.
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?
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.
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)
:confused: I have a combo box on a form that is used for data entry that allows the user to pick the date. This is based on a table that includes date, week ending date and month, all very specific for our business. I used a function that says Me.txtBox=ComboBox.Column1 for example. So my combo box would include all the information, and all but the actual date is not visible. I want this to feed into another table that will be used for reporting, and that table has the same fields of date, week ending date and month. I keep getting an error saying that the value selected from the combo box doesn't fit the criteria, and I believe what is going on is that it is taking all the fields instead of just the date. Any suggestions how I can still have the text boxes update if I only have the date in the combo box?:confused:
I have a main form which has 4 combo boxes all based on tblPeople. Combo0 is skill Combo1 is discipline Combo2 is crart Comb03 is active
The sub form is a datatable list of the tblpeople.
How do I apply the filter to the datatable. I am assuming I need to build a sql statement somewhere like SELECT from tlpeople WHERE me!combo0 = [tblpeople].[skill] and me!Combo1=[tblpeople].[discipline] etc
Is there a sample database? or can somebody tell me how to go about this.
My database is using data that is entered by the employees to generate Quotations. There is one important piece of information that will not be entered by the employee. The quotes involve metals which are priced based on market price and weight. I am planning on integrating a data feed with this information, but for now I want to enter it manually in a table. The price depends on two combo boxes one for "Precious or Base Metals", and one for "Metal Name". I want those two values to call the price from a table, and automatically fill in the "Market Price" field in the form.Also once that is in I would like to do my calculations. I am planning on using queries to do these. Is that the correct method?
I am trying to create a training matrix in which i need a form that if I put or assign maximum of 20 attendees then 20 comboxes will appear in form.for example, i enter in textbox1 6 or 15 depends but max is 20 then if hit enter comboxes will appear but quantity will based on value that i enter in textbox1.
I have a form that our operators use to do their hourly quality audits on. This is getting to be a huge burden on them because right now, they enter the date, the week ending date, the month every time they have to do an entry, and for me it is a nightmare because they can still enter the wrong information. So, what I was wondering is if there is a way that I can have them select the date from a combo box (easy enough), but from that, have the week ending date and the month fields automatically update as well. Any advice? I would really appreciate it! Thanks so much!!!!
I have a form called frmPO based on a table called tblPO One of the fields in tblPO is linked to the field idAddress in the table tblAddresses
The following fields exist in both tblAddresses and tblPO Company Address1 Address2 City ZP State
The form contains controls for these fields on tblPO. I would like to give the user the choice to either enter a shipping address manually, or selecting a preregistered address from tblAdresses. For this I would like a combo box showing the values of idAddress. I f a user selects a preregistered shipping address, I would like the form to fill the rest of the fields based on the value of this control.
My understanding is that I should set a BeforeUpdate event to set the values of the different controls, unfortunately my command of VBA amounts to 0.
I have two combo boxes in the form header and command buttons in the form detail. The combos allow the user to select either a customer or a prospect, then the command buttons open forms that only show records pertaining to the customer or prospect selected. Combos are "4Custcbo" and "4Proscbo".
1.How do I change which command buttons are available depending on whether the user has selected a customer or a prospect? Do I use two different subforms or is there a better way?
2.How do I hide the other combo box once the user has selected either a customer or a prospect?
I want to add to every row a combobox with a list of towns depending on the value in the field Country ID.how do I filter the comboboxes individually based on a value in another field on the same row?
I have a form and a subform with a master/child relationship set based on the primary key of each underlying table. All good there.Now, I want to use VBA to create a filter based on a set of inputs via combo boxes. But the filter must filter both the Parent and Child records.Example. "Show me only records where both only the Parent.Field1 = "string" and Child.Field = "string".I can do this in a QRY as follows:
SELECT Projects.[Project Number], Lessons.[Actions Resolved] FROM Projects INNER JOIN Lessons ON Projects.ProjectsRecordID = Lessons.ProjectsRecordID WHERE (((Projects.[Project Number])="AU-2102421") AND ((Lessons.[Actions Resolved])=True));
But, if I make this as a record source for the Parent Form, then the records in the Parent Form are repeated for each individual record in the Child form.
I am trying to put together a form where employees will be entering maintenance information into my database. One of the requirements for this data is that when they perform repair work on an asset, they have to list a problem, cause and remedy. These are codes that are specific to each asset.
The closest thing I can think of to what I am trying to acheive is the selectors on car search websites. You select a MAKE, and then the MODEL list is narrowed down based on that, then you pick the TRIM, which is narrowed down based on the model selection. I have an Excel spreadsheet of the Assets(Failure Class), Problems, Causes, and Remedies...I'm just not sure how to put this into Access to get the desired output. I have attached a sample of the Excel spreadsheet for illustration.
I believe I'm going to have to put each of the columns in a seperate table and use relationships..but I'm not sure about that.
I'm trying to navigate to a record based on a series of combo boxes and a button. The user goes through the combo boxes, first picking a (let's say) Store, then Aisle, then Item. The Item combo box stores the Item's unique ID as its bound column.My code is as follows:
Code: Private Sub NavigateButton_Click() Me.Recordset.Clone.FindFirst "[ItemID] = " & Me![ItemComboBox] Me.Bookmark = Me.Recordset.Clone.Bookmark '(<-- Don't actually have this in yet, but I will need it) End Sub
The ItemID field in the ItemTable table is an Autonumber Long Integer. Why am I getting the error "Engine does not recognize 'ItemID' as a valid field name or expression"?There are no references within the form to the ItemID directly (no text boxes, hidden fields etc).
im trying to enable/disable checkboxes based on a combobox selection for instance,
i make the selection in a combo box called terms and conditions. i want it then to only enable the business,domestic and summary check boxes for that type, with the onther check boxes staying disabled. is there a way this can be done through code like the statement "only enable if this letter type selection has been selected"
This works great to return a report if the user selects something from the combo box. How do I adapt this so that the user can also leave the combo box blank and filter the report to return all records?Additionally, what if I want to have the user filter between dates selected on the form; i.e. between 'txtStart' and 'txtEnd'
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.
I 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
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.
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.
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!
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?
