Combo Boxes And Queries Again - Two On A Form
Mar 4, 2005
I have re-written this question as I think I managed to cause confusion!
(I really do need help!) My problem involves three tables. The first table is NAMES with an auto-number key field called NamesID.
The second table is a list of Dance Classes, called CLASSES again with an auto-number key field called ClassesID.
The third table is a list of MEMBERS in each dance class.
Obviously, the MEMBERS can belong to many CLASSES, and each of the CLASSES can consist of many MEMBERS.
My aim is to add a new record in MEMBERS by using a ComboBox containing records from the NAMES table.
On the form, I have a combo-box which shows me the list of CLASSES
(ComboClasses). I click on the class.
I then click on the NAMES combo-box, click on a name and want it to appear in the table of MEMBERS for the class shown in the CLASSES combo-box.
The problem
I am selecting the name using “ Select * From NAMES where Names.NamesID=[Forms]![FormName]![ComboNames] (supplied by supersubra) but how do I get the ClassesID into MEMBERS record.
At the moment, if I add a name that member appears in every class, or I have to manually insert the ClassID.
Philip
View Replies
ADVERTISEMENT
Mar 24, 2014
I'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]....
View 2 Replies
View Related
Mar 25, 2013
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 Related
Sep 29, 2014
I have a user form with six different dropdown boxes. I would like to create a query that gets its criteria from users selecting values from one or more of these boxes.
I have tried:
[forms]![frmName]![comboboxname] or [forms]![formName]![comboboxname] is null
in the corresponding query fields and get a "query is too complex" error message, even when only selecting one criteria.
What am I doing wrong?
View 5 Replies
View Related
Apr 3, 2014
I'm wondering is it possible to create a multiple parameter query which will return results even if you leave some of the parameters blank ?. I'm trying to set up a Form which will allow users to select parameters using combo boxes but at the moment you need to fill them all in or you'll get no results...
View 10 Replies
View Related
Jun 5, 2014
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]...
View 1 Replies
View Related
Mar 20, 2013
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 Related
Mar 2, 2005
howdy all, ive never touched Access until 2 days ago so my experience is
sorely lacking but here is my question:
i want to create a form with a combo box from which a selection is
made (data in the combo box is simply a field list from the same table the query is searching, but
my stumbling block is that i want to include the query in the
same form as the combo box and have it dynamically updates based on the
selection in the combo box.
however for the life of me i cant get the query to update based on the
input (using [Forms]![Form]![Combo1] as the criteria in the query) i have set this criteria in the
CustomerID field of the query (which is also the primary key of the table)
View 1 Replies
View Related
Jul 20, 2007
Let me see if I can explain this.
I have two tables: TblValues and TblLOC.
TblValues is a table of stactic values where I have listed about nine field names with values under each that usually will not change. This way if there is a change to one of the fields later, I can just add it to the table.
Then there is tblLOC. Here I have similar field names that matches each field in tblValues where I can populate them in my form. I have several fields defined as combo boxes. In the properties for each field defined as a combo box, for row source type has Table/Query selected and for the row source I have the following code in the table, which is an example for the field named Profit Code in TblLOC.
SELECT tblValues.ID, tblValues.Pro1Code
FROM tblValues
WHERE (((tblValues.Pro1Code) Is Not Null));
Then I have my form where I have referenced my fields from the tblLOC. In the properties of each combo box, under the tab labeled DATA, the control source has the name of the the combo box field that is in tblLOC, in this case Profit Code.
Everything works fine. The user selects a value from the form and tblLOC is updated. I can create queries without criteria with no problem. However, in a query if I try to add any criteria, even as simple as using "like" to identify a value in a field of the table that has been populated by the combo box, I will get no results. However, if I use the same functionality in a column where a combo box is not used to populate the field, I get the results that I expect.
Here is an example of the SQL in the query that does not work for me that is applied against a field that is a combo box.
SELECT tblLettersOfCredit.[Profit Code]
FROM tblLettersOfCredit
WHERE (((tblLettersOfCredit.[Profit Code]) Like "Misc"));
If I use the same functionality with a field that is not populated by one of the combo boxes then everything works fine.
I am hoping that there's a simple solution for this, maybe syntax due to the use of a combo box or maybe a property that I need to set... So far I have been unsuccessful in finding the answer. If anyone has any ideas on this one, I would certainly appreciate it. Thanks :)
View 6 Replies
View Related
Jan 31, 2014
I have 2 unbound combo box's on a form 1 called cboclient1 and the another called cboclient2. would like to be able to filter field name client name twice by cboclient1 and cboclient2 here my sql from my query
Code:
WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null));
This works perfectly for cboclient1 problem comes when I try and add cboclient2
I have tried
Code:
WHERE (((Assets.Client)=Forms![report gen]!cboclient1)) Or (((Forms![report gen]!Cboclient1) Is Null))AND(((Assets.Client)=Forms![report gen]!cboclient2)) Or (((Forms![report gen]!Cboclient2) Is Null));[/
This doesn't work at all....
View 2 Replies
View Related
Jul 13, 2015
I have a form where I am trying to use 4 combo boxes(nomenclature,BPN,vendor, and reference) to filter a list box containing part numbers. The way I have it set up right now is in the listbox it is searching for each field and then in the criteria section i have [Forms]![myform]![respectedFieldsCombo].
This works when selections are made from the combo boxes but when one is blank (not being used to filter) then I assume it passes null for that value and the listbox doesn't return anything. I have tried to make it so the listbox ignores null values but im still having this problem.
For example: If i only have a selection for the vendor combo box then i want the list box to show all respective part numbers for that vendor, where nomenclature or any of the other fields are irrelevant. I also want to be able to stack these filters upon every new combo box selection.
View 7 Replies
View Related
Dec 18, 2013
I am working on a database where law clerks input the tasks that they accomplish each day. They use a form called "TaskDetails" to input all of the information related to their daily tasks via combo boxes. The problem I have is: often times the clerks work on the same task more than once and I would like them to be able to continue tasks already in progress - using notes to indicate what particular portion they completed that day.
In order to do this I would like to have a combo box that is based on a query of all of the tasks the clerks have completed. When they select a Task Name from the combo box, I would like all other combo boxes on the page to auto-populate based on the selection.The query that my combo box is based on includes all relevant fields.
When I click on the "Task Name" combo box I am presented with all of the records related to tasks. The fields/columns are divisionofwork, subcategory, workassignedby. I have combo boxes for all of these fields on the same page, bound to the table of tasks. Ideally, a clerk makes a selection from the "Task Name" combo box, and the subsequent 3 combo boxes will reference their relevant column in this selection and auto-populate.
View 1 Replies
View Related
Jul 23, 2015
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
View 2 Replies
View Related
Nov 20, 2013
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.
View 5 Replies
View Related
Aug 13, 2013
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.
View 11 Replies
View Related
Jul 30, 2013
I have a form which runs specific queries and opens a report using combo boxes, these work perfectly fine if I just try to run them in the form. I have put the form into a navigation form and if I try to run the same query I get parameter queries popping up instead of just running the query and opening the report.
View 2 Replies
View Related
Jul 10, 2013
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?
View 2 Replies
View Related
Jan 28, 2005
Hello to everyone!
I have a serious issue to solve and I would like to ask for your help, as I just don't seem to know how and where to start from.
I need to make a database that will have 2 tables, say tbl_item and tbl_offer. The items table will have different cardboard dimensions and other characteristics (width, height, color, photo, price etc) while the tbl_offer will have different combinations of clients' cardboard requests.
The workflow is:
A client comes and asks for:
- two pieces of cardboard of 120x100 cm
- five pieces of cardboard of 135x90 cm
- one piece of cardboard of 110x125 cm
I want to be able to enter the client's request in a form and the form will do 2 things:
1. Store the clients request as a record in the table tbl_offer (for current or future referrence)
2. Create a report (out of the above record) that will be printed out and given to the client (but I suppose that's easy...)
Now tbl_item has different types of cardboard (their code names) along with different dimensions and price for each, for example:
Type|Width|Height|Price
----------------------------
001 | 120 | 100 | 10
001 | 135 | 190 | 12
001 | 110 | 125 | 11
001 | 110 | 100 | 16
001 | 150 | 100 | 12
002 | 165 | 170 | 17
002 | 140 | 105 | 10
002 | 140 | 130 | 18
002 | 170 | 130 | 18
The problems are
1. I don't know exactly how I can create a form (say frm_offer) that will give me the possibility to add many different cardboard types and save them as one offer to the tbl_offer. What I would like is to choose from a combobox (or any other similar function) the type (say 002) and by this selection a second combobox would be filled only with the available Width dimensions for this type (165, 140, 170). By choosing what I want from the width combobox, a third combobox would be available that would give me the available height dimensions for this width (if for example I chose Type 002 and Width 140, the last combobox would give me two choices, 105 and 130). After choosing the Height I want, I would fill in a text box of how many pieces the client wants and the form would also fill automatically the cost of all the pieces for that dimension (by taking the price/item from the tbl_item perhaps?). If 1 piece then it should say i.e. 18 for the 140x130 piece, if 10 pieces it should say 180. Finally, I want to add the photo of the cardboard automatically (pls note that same type of cardboards share the same photo) not at the form but at the report to be printed.
I have already looked in the Access FAQs section of the forum for the combobox techniques but I can't understand how I can apply them to three (or more) comboboxes plus the photo.
I can use Access only when little/no programming is required, and I am very much afraid that all the above will require a great deal of it, so I apologise in advance for my non-knowledge of programming :(
2. How do you think would be suitable to split all this data to tables? Is my thinking correct (2 tables, tbl_item and tbl_offer) or would you suggest something better? Will the tbl_offer get a lot of data that way? Mind you also that every once a year there is the need of updating the cardboards' prices (all will rise/fall at the same percentage) and, furthermore, we are talking for about approx. 100 different types of cardboards, each with about 15 different dimension combinations.
I am not sure if I had to post this message to the Tables section of the forum or here but I think here is better as the main problem is the form part.
Thank you all in advance and my apologies once again for the low level of my programming skills :(
Alexander
View 5 Replies
View Related
Nov 27, 2013
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.
View 2 Replies
View Related
Apr 16, 2005
I need a form that has multiple categories, 7, listed one after another vertically. I also need the categories divided in to 2 columns.
I need to have 2 combo boxes, 1 each controlling the info for each column.
Can I do this on 1 form. Suggestions would be appreciated.
Freddie
View 6 Replies
View Related
Oct 24, 2005
I have a continous form that once the combo box filters are selected, shows the applicable records. Included in records, I've put a bound combo box that allows the user to fill in that particular field. This works with the first record but when you attempt to select the other records, all the combo boxes disappear. What am I doing wrong here??
View 12 Replies
View Related
Jul 17, 2006
Hi,
I need help in the following 2 problems:
I have two combo boxes on the form and print button.
In one combo box showing numbers 1,2,3,4 and 5.
Other combo box showing logical operator =,<,>
User is going to select from one combo box logical operator and from other number "means class field".
Print button prints the report. Report is based on the query.
In the query in criteria line I put the code
[FORMS]![MAINFORM]![CMB_CLASS]
It works for one combo box. Problem is to get criteria from second combo box or concatenate the both criteria’s. In query criteria line it should show like <2 or =3 or what ever user is selecting.
Second problem is in the same line. Three text boxes and 2 combo boxes showing "OR" and "AND" For example in one text box user enters last_name and then he select "OR", second text box enter city and then he select "AND" from second combo box and third text box enter department. After selection he is going to click the print button to print the report.
I really appreciate if anyone knows the solution or they used this type of thing in there application.
Thanks
View 2 Replies
View Related
Sep 14, 2006
Hi all! :D
I'm trying to make a form to autofill with customer data (from a database), with 4 columns (= 4 customers). Each column contains on top a combo box and below 5 text boxes with address, phone etc.
So i created a form and placed a combo-box (from wizard) with the option "Find a record on my form based on the value i selected in my combo box".
The problem is that this one combo box controls all the text boxes. Even if i create an other one, it still controls all of them.
So i'd like to ask: How can i link a combo box, to autofill specific textboxes?:confused:
Thanks... :)
View 9 Replies
View Related
Jul 5, 2006
I have a form that we are creating to issue project numbers. We have about 80 different clients. I have set up a combo box for the client name, where we can choose the client from the drop down menu. We want the form to filter according to the client name that you choose. For example, if you pick Lawrence as the client name, the next combo box is the project name and I want it to only show the project names that have been assigned to Lawrence in the past. Can someone please help me on how to do this and if it is even possible at all? Thank you so much for your help!! I have been wrestling with this for about a week and a half now.
View 1 Replies
View Related
Jan 18, 2012
I made a form from a "CS-PG1-PG2" table. then i added a subform that contains these 3 fields plus some more information. What I'm trying to do is to insert in the main form 3 combo boxes:
CS (discount group): not to be linked/synchronized with the other two combos;
PG1 (product group);
PG2 (product group2): it must show the filtered results from PG1 combo box.
As long as I insert the CS combo box, everything is fine. Whenever i add the other two combo boxes, or just the PG1 combo box, then the two of them conflict and i get zero records.
All i want to do is to add these 3 combos and give an option to filter the results by CS or PG, plus give some sort of hierarchy order between the PG1 and the PG2.
View 5 Replies
View Related
Sep 21, 2014
I have 2 combo boxes on a form
cboWkLoc This selects workers city where employed This combo gets data from company table
CboEmp This Selects Employee
I am trying to join these both combos so when I select City (e.g Reno ) its shows only employees working in Reno - This combo gets data from Purchases table
I have tried to work on the following code....
Private Sub cboWkLoc_AfterUpdate()
Dim strSource As String
strSource = "SELECT City " & _
"FROM Purchase " & _
"WHERE Employee = '" & Me.cboWkLoc & "' ORDER BY City"
Me.cboEmp.RowSource = strSource
Me.cboEmp = vbNullString
End Sub
View 7 Replies
View Related
