Populating An Underlying Table When Using Cascading Combo Boxes In A Form
Jun 1, 2005
Hello everyone
I have read the FAQ on cascading combo boxes and have managed to apply the theory to my DB's data input form (frmDataEntry) which is very cool and prevents a lot of errors however in doing so it no longer populates the underlying table (tblProductionDetails).
I am at a loss as to how to correct this as you can see from the example I need the customer and description field to be populated with the correct data rather than the fields I am using to make the cascading combo boxes work.
I am using a form with a combo box (FieldExpenses) that lists from a query row source (SELECT statement). Whether entering a new record or editing an existing record on the form, the selection in the combo box populates the field in the underlying table (TableTransactions) with the row ID (ex: 105), not the desired field (ie: Telephone).
I tried using a split form and the table in the form shows the field correctly. If I open the underlying table separately, the field displays the row ID. It's not working as I wish.
I'm building my first Access DB for a while, so am a little rusty, but I can't seem to work out this problem.
I've bound my form, and all of the other fields are saving and populating the correct table, but I have two combo boxes, both of which populate a series of other text boxes based on the selection (eg. I select Joe Bloggs in one box, and it automatically puts Joe Bloggs' phone number and email address in two following boxes).
This all works, however when I save the record, or move to the next one, the data entered in the combo boxes and the related populated fields doesn't save - they just show up as blank in the record created in my table.
As an aside, how do I get the combo boxes & related text fields to show a default blank value until a value is selected.
I have been trying to modify a sample database to suit what I want to do but I am getting stuck on the very first part cascading combo boxes i want to open a form and add multiple items to a table - first i want to select, from a combo box a department - then select a supplier from all the suppliers/manufacturers related to that department then select a stock item based on the description from all the items available from that supplier
I have attached the database I am using to modify and my database.
What I am trying to do is create a data entry form to an "order table" using 2 cascading combo boxes. I have created a data entry form based on a query. I can't get the cascading combo boxes to work properly.Here is the code:
Private Sub Combo0_AfterUpdate() Combo2.RowSource = "SELECT L2_ID,L4_Element_name,L5_Category FROM qry_ord WHERE L3_ID = Combo0.Value;" Combo2.DefaultValue = [Combo2].[ItemData](0) Command4.SetFocus End Sub
I have a form called Adaptation Form in which Last Name and Course Number are entered.
The user looks up Last Name using a listbox on the form, and looks up Course Number using a second listbox on the form.
To use the form, the user looks up Last Name in the listbox, finds the ID number associated with the Last Name, and enters the ID number on the form. The form then autopopulates with the last name. The process for entering a Course Number is the same.
This populates the query underlying the form, but not the table. All that gets populated in the table is the ID Number for Last Name and the ID number for Course Number.
Checked the FAQ on this but doesn't apply to what I need.
Basically, I have a Product Class, and Products that are in the class. My database is for a computer component business, so the clsses are Processors, Mainboard etc.. and the products that fit into that class. The Class and products are listed in the Products table, and I need a way to have it on my subform, so I can choose the product class from one combo box, and then have another combo box to view the products in that class.
Right now I have it :
SELECT DISTINCT Products.[Product Number], Products.[Product Description] FROM Products
and that lets me select ALL the items in my products list, but I also have a Product Class Combo in the form that does nothing yet - So I thought adding : WHERE (((Products.Product Class)=(!FORMS![Orders Subform1]![Product Class])) ORDER BY Products.Product Class
To the end would sort it, but it doesn't and gives me the error : "Syntax Error (Missing Operator) in query expression"
As simple as it sounds. I have set up a table with a list of items and the costs. I then created a form with a list comprising 10 combo boxes. I want to be able to select one record from the underlying table per combobox on the form.
I am able to do this but the records in the underlying table keeps switching to the primary key field as I go along using the combobox.
I'm jumping to the forms development and going to try what I need via queries and SQL. I'm trying to do a series of cascading combo boxes which have worked out fine, right till I hit the first junction table.
I will include the VBA code below as well as a screenshot but here's how it goes.
REGION cascades down to COUNTRY cascades down to PORT and then to CARRIER.
This is for a shipping program.
When it gets to PORT and are trying to cascade to the various CARRIER's thats where it hits a junction table of PORT_CARRIER.
Here is REGION to COUNTRY:
Me.cboCountry.RowSource = "SELECT COUNTRY.CNTRY_ID, COUNTRY.COUNTRY FROM COUNTRY " & _ " WHERE REG_ID = " & Nz(Me.cboRegion) & _ " ORDER BY COUNTRY" Me.cboCountry = Null
COUNTRY to PORT :
Me.cboPort.RowSource = "SELECT PORT.PORT_ID, PORT.PORT FROM PORT " & _ " WHERE CNTRY_ID = " & Nz(Me.cboCountry) & _ " ORDER BY PORT" Me.cboPort = Null
PORT to CARRIER:
Me.cboCarrier.RowSource = "SELECT PORT_CARR.PORT_ID, PORT_CARR.CARR_ID FROM PORT_CARR " &_ " WHERE PORT_ID = " & Nz(Me.cboPort) & _ " ORDER BY CARR_ID" Me.cboCarrier = Null
PORT to CARRIER is where the problem is.
It populates the combo box, but with the ID numbers instead of actual CARRIER names.
(the Junction table are two PK fields and are lookups to PORT in PORT table and CARRIER in CARRIER table.)
Is there a magic spell for cascading combo boxes when you hit a junction table?
I need a table structure that will allow me to have a repair log data entry form with 3 cascading combo boxes on the repair log data entry form. There can be many repairs for a specific job but most of the time there will be one repair per job and 1% of the time two or more repairs for a specific job.
I am pulling a report based on a query that will show the repaired location, facility type, repaired item, repaired component on that item and other details related to the repair.I uploaded an empty database with the structure.
Cascade levels
I. Facility Type II. Item by [I] III. Component by [II]
Currently, I have a form with a combobox that lists all of the names of the tables inside my database. Depending on the table selected in the first combobox, I would like to have another combobox which allows the user to choose from the field names inside that table.
I have three nested forms that is Form3 is withing Form2 that is within Form1 (in reality = 1 Form and 2 subforms cascaded).
I want to select a value from Combo1, that will further brings the values in Form2, and then further filter the values in Form3. If the three combos were on the same form, I wouldn't have an issue. But since each combo is in every cascading form/subform, I dont know how to pass the filterning further to the other two combos.
I am trying to get a series of combo boxes to interact and record the selections made in new records within a destination table. Here is situation with respect to the tables involved:
1 table lists the names/acronyms of various research facilities and the branch to which they belong (acronym is PK) 1 table lists Financial Points of Contact for each facility (acronym is FK to relate to earlier table) 1 table lists Technical Points of Contact for each facility (acronym is FK to relate to Facility table) 1 table lists program participants and the branch they belong to (Branch in FK to relate to Facility table)
I am trying to create a form that allows me to set the participant and, from this selection, restricts the facility choices in the Facility combo box to those that fall within the branch to which the participant is assigned. I think the next step is pretty obvious too. Once the facility is selected I have combo boxes that would only display those Financial and Technical PoCs assigned to that facility.
I have watched the 4 Data Pig tutorials and I can easily make functional cascading combo boxes as long as they only refer to the look up tables. I generated perfectly functioning cascading combo boxes on a test form. How do I generate combo boxes that will then store the info selected in my destination table? Whenever I try to set a Control Source it disrupts the functioning of the queries.
I have a form that contains two controls for determining the law that applies for specific contracts. When you select the first control, there is a drop down from which you select the country. If you select US, then the second control has a drop down with the US states available for selection. If you select Canada, then the second control lists the provinces of Canada. If you select any other country, the state box is greyed out.
At input time, this works fine, however, I am missing something to get the second control to save the data to the underlying table. I am sure it is a simple code, but I am still a novice at code and can use your help. Here is the code for the first section. I appreciate any help. The second control cboState is currently unbound and I am sure that is part of the problem but am unsure of the solution.
Private Sub cboTermsCountryLaw_AfterUpdate() On Error Resume Next Select Case cboTermsCountryLaw.Value Case "United States" CboState = Null CboState.RowSource = "tblStates" CboState.Enabled = True Case "Canada" CboState.RowSource = "tblProvince" CboState.Enabled = True Case Else CboState = "N/A" CboState.Enabled = False End Select End Sub
I need some help setting up my tables to allow cascading combo boxes. I need to document when a Pharmacy IV room and chemotherapy room are cleaned. Each Room requires different cleaning procedures.
Here is my table structure: TblCleaningDuties DutyID-Auto# (PK) Duty - text (Example- mop floor, clean hood, wash ceiling, wash sink) Frequency-text
If the user selects Clean Room I don't want them to see wash sink and anything else not pertinent to the Clean Room. How do I set up the tables? Thanks, Jason
Just wondering if anyone can help Im creating two combo boxes where the first one determines the values in the second one.
The tables are made up of the following;
Line
Line number (PK) ..... ....
Main Setting
Part Number (PK) Press Line (FK)
Now ive took the advice of previous threads but im having the following error message when im creating this;
The record source 'SELECT [Main Settings].[Part Number] FROM Main AS Settings WHERE [Main Settings].[Press Line]=[Forms]![Example]![Line]' specified on this form does not exist.
I know that this doesnt exist but im typing it correctly. Access seems to be changing my statement i.e. inserting the AS command. Any help will be much appreciated
Right I have followed one of the sample databases and modified it to create cascading combo boxes like i needed.
Now all i need to do is use them in the context i need.
Basically I have country, region and sub region. These cascade to each other in the form which has been created. If you select a country, then in region you in only see the regions in that country, etc.
What i need is to be able to use the cascading combos boxes in conjunction with the "Location table". So someone can put in a project number and then use the cascading combo boxes to to chose the country, region and sub region boxes saving all of the data into the location table .
Please can someone help, as it took me allmost three hours to create the cascading combo boxes but now i cant even use them for what i wanted them for,.
I have trawled this forum searching for help with cascading combo boxes, it was very helpful but alas I need that bit more.
I have 2 combo boxes and the result is displayed in a subform. The problem is that it will only display one selection made from the Combo boxes and I need multiple selections on display in the record.
It is a parts job worksheet in a garage where multiple work is required on a vehicle. It is based on Work Type and Work Codes which then bring up the detailed description.
The first Combo Box is Work Type and this allows you to select letters. The second Combo Box is Work Code and this allows you to select numbers.
I used the example I found on here but haven't changed the field names as I daren't alter the coding until I know it will display multiple selections in the subform of the record.
I attach the database to see of anyone can help me, ppllleeeaaassssee. I woul dbe most grateful if you could make the changes and zip it back up as I ain't no VB programmer.
i currently have 4 combo boxes that each has its own table, and setup as cascading. Now what i want to add is an <All> in each combo to box to give any results in that table.
i have
combo 1 SELECT [tblProductManufacturer].[ProductManufacturerID], [tblProductManufacturer].[ProductManufacturer] FROM tblProductManufacturer UNION Select "<All>", "<All>" as Bogus From tblProductManufacturer ORDER BY [tblProductManufacturer].[ProductManufacturer];
combo 2 SELECT tblProductModel.ProductModel, tblProductModel.ProductModelID FROM tblProductModel WHERE (((tblProductModel.ProductManufacturerID)=[Forms]![MainScreen]![MainWindow].[Form].[CboProductManufacturer])) UNION Select "<All>", "<All>" as Bogus From tblProductModel ORDER BY tblProductModel.ProductModelID;
combo 3 SELECT [tblProductVersion].[ProductVersion], [tblProductVersion].[ProductVersionID] FROM tblProductVersion WHERE ((([tblProductVersion].[ProductModelID])=[Forms]![MainScreen]![MainWindow].Form.CboProductModel)) UNION Select "<All>", "<All>" as Bogus From tblProductVersion ORDER BY [tblProductVersion].[ProductVersionID];
combo 4 SELECT [tblProductSize].[ProductSize], [tblProductSize].[ProductSizeID] FROM tblProductSize WHERE ((([tblProductSize].[ProductVersionID])=[Forms]![MainScreen]![MainWindow].Form.CboProductVersion)) UNION Select "<All>", "<All>" as Bogus From tblProductSize ORDER BY [tblProductSize].[ProductSizeID];
in place of UNION Select "<All>", "<All>" i have also tried
UNION Select Null as AllChoice, "(All)" as Bogus
And in place of UNION Select "<All>", "<All>" as Bogus i have also tried
UNION select distinct null, null (And added distinct after the first Select of the query)
I have 4 Combo Boxes on my form (cmbo1, cmbo2, cmbo3, cmbo4). I have successfully managed to get the cascading down as long as the user starts with cmbo1.
What I would like to be able to do is have the user select any combo box on the form and have the rest of the boxes filter automatically, e.g., a user selects a value in cmbo2, the afterupdate would then filter/lookup the values of cmbo1, cmbo3, cmbo4 based on the value selected in cmbo2.
Is this possible and if so, does anyone know how to do this?
I'm really stuck on this and have tried loads of different ways, but I can't seem to get it to work. I have 3 tables: tbl_Workshop, tbl_WorkshopArea and tbl_Instructor.
I want to have a combo box that will display the field WkshopArea from the workshoparea table. When an area is selected the next combo box should only display the names of the workshops available in that area. (The second combo box should be based on the WkshopTitle field in the workshop table.
Once a workshop is chosen from the second combo box a text box should be updated with the corresponding code of the workshop.
I've attached an example of my database for you to look at - both the forms on there don't work, but show examples of what I've been trying.
I have a form with a combo box in it. When the user selects either the US or Canada, a second combo box will open with States or Provinces listed. For background, this will record the location of applicable governing law for the contract we are recording. Anyway, here is the code. My issue is I need to add some code that says, if the law is another country (not US or Canada), have the second combo box indicate an n/a or gray out the second combo box. I would appreciate some help. Thanks
Private Sub cbotlCountry_AfterUpdate() On Error Resume Next Select Case cbotlCountry.Value Case "United States" cboState.RowSource = "tblStates" Case "Canada" cboState.RowSource = "tblProvince" End Select End Sub
I have cascading combo boxes in a subform. When I click the second box, which is dependent on the first, I get the following error:
"Object does not support this property or method"
This is what I have for the On Enter Event:
Me!LeadIn.Updated Form!LeadIn.Requery
I originally had : Me.LeadIn.Requery for the On Enter, but it prompted a parameter box looking for the Phone_Card form.
I think I have to adjust the OnEnter even syntax, but I cannot figure it out.
Here are the names of the forms: Main Form: Updated Form SubForm: Phone_Card
Control Names:
Combo Box 1: Brand Combo Box 2: LeadIn
In doing a net search, I believe it should look something like this:
Me!Leadin.Form!leadin.requery ??
I should also add that the subform is in a tab control: TabCtl197 if that matters. Also, when I use the combo boxes while having the Phone_Card form open, they work fine. It is only when using it as a subform that it no longer works.
Hi, I'm working on a lengthy program and have had a request to simplify combo box choices for the end user. We have 3 boxes to work with : Division, Department, & Unit. The objective is to limit the Departmental choices dependent upon what was first chosen in Division. Likewise in Unit : Department. Any ideas on how to do this?
Firstly I'd just like to say HHHHHHEEEEEEEEELLLLPPPPPPPPPPP!!!!!!!!!!!! LOL
I'm creating a fantasy football database for a skool project but have hit somewhat of a stumbbling block with the way to display the team. After discussion with my teacher we decided combo boxes was the best solution (im open to other suggestions) However we cant come up with method to cascade the combo boxes :confused:. I need to cascade the four defenders boxes together, the four midfielders together and lastly the two attackers.
Ive tried to do this using an update query but the problem with that is its updating all the records rather than jus the one I dont no what else i can do and im really hoping one if u's cazn help. Im also tryin to make the user not be able to select more than 2 players from the one team if anyone knows how t create some sort of validation rule for that
