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.
Any help would be greatly appreciated
Regards
Adrian
View Replies
ADVERTISEMENT
Mar 9, 2015
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.
View 2 Replies
View Related
Mar 9, 2012
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.
View 12 Replies
View Related
Mar 12, 2011
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.
View 5 Replies
View Related
Jan 31, 2014
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
[code]....
View 2 Replies
View Related
Oct 10, 2013
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.
View 3 Replies
View Related
Mar 14, 2005
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"
What am I doing wrong?
Cheers,
James.
View 1 Replies
View Related
Jun 14, 2015
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.
View 8 Replies
View Related
Jul 5, 2013
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?
View 14 Replies
View Related
Jul 19, 2014
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]
View 14 Replies
View Related
May 15, 2013
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.
View 3 Replies
View Related
Dec 5, 2013
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.
View 6 Replies
View Related
Sep 12, 2012
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.
View 4 Replies
View Related
May 5, 2008
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
Alan
View 8 Replies
View Related
Dec 12, 2006
I have a few cascading combo boxes that restricts the value of one combo box depending on what the the value of other one is.
The problem I have is that some of the data is not displayed in the combo boxes when scrolling through the records after data entry.
I have tried to re query them on the form on open event but it does not seem to help much.
Any Ideas?
thanks :)
View 9 Replies
View Related
Jul 28, 2005
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
TblLocation
LocationID- Auto# (PK)
Location - Text (Clean Room, Chemo Room, Anteroom)
TblCleaning
CleanID-Auto# (PK)
LocationID-(FK)
DutyID- (FK)
Date/Time
TechID
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
View 1 Replies
View Related
Jun 2, 2005
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
View 3 Replies
View Related
Jan 31, 2005
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,.
Many Thanks
View 12 Replies
View Related
Mar 15, 2005
Hi
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.
View 9 Replies
View Related
Oct 9, 2005
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)
but to no avail????
View 7 Replies
View Related
Aug 24, 2006
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?
Thanks.
View 14 Replies
View Related
Oct 27, 2005
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.
View 1 Replies
View Related
Feb 25, 2008
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
View 12 Replies
View Related
Nov 29, 2006
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.
Thanks for any advice or help!
View 2 Replies
View Related
Oct 19, 2005
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?
View 3 Replies
View Related
Feb 18, 2005
Hi Everyone
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
Thx alot
Ramsjammin
View 1 Replies
View Related
