I want to create a database with cascading boxes. I have a sample that has a form that filters the data from 4 tables and I want this data to be saved in a table. How can I do this?
Attached database. The table which I want to save the data is tblTotal.
I am trying to create a Cascading Combo Box for my Form in Access 2010. I ahve written the code:
Private Sub Combo11_AfterUpdate()
cboCombo13.RowSource = "Select TblAcc.SubFamily " & _
"FROM me.TblAcc " & _
"WHERE me.TblAcc.Family = '" & cboCombo13.Value & "' " & _
"ORDER BY me.TblAcc.SubFamily;"
End Sub
But keep getting the same "Run time error message 424: Object required"
And when I define the error it says "Identifier under cursor is not recognized" (the yellow arrow comes up and is pointing at the "ORDER BY" line.
I would like Combo Box 13 to give me the SubFamilies of the certain Families choosen from Combo Box 11. (Each Family has different SubFamilies). But if I ignore the error, Combo Box 11 lets me choose a Family and Combo Box 13 gives me options to choose any SubFamily in my DB.
I've been able to make a normalized relational database with cascading combo boxes, etc.
How to validate a field? The format needs to be according to the following:
<0 AND <=9999 [Optional: followed by a capital letter]
Examples of Valid Data:
7
4A
354
8756G
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 :)
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
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,.
Many Thanks
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.
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????
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.
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
Well first i'd like to say hi to hopefully all the good people out there who can help me.
My problem is this, i've searched through the forums and cant seem to find the right solution to what i'm looking for.
I've used the instructions to construct my cascading combo boxes in the form, now all i want is the 3rd and last of the combo boxes, once selected to automatically insert the corresponding Name in the text box below it.
The text field is bound as its need in the table.
The last combo box selects the code and the field i want to show is the data name in the text box that matches the code in the combo box.
I know its something to do with the AfterUpdate event in the combo box but dont know what code to use.
Hope someone can help me. Thanks in advance
Chris
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.
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!
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 RelatedHi 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
Hi all... and thanks for your help to this very frustrated moderate Access user. I have been using an example for cascading combo boxes in a timesheet form, but I simply cannot get it to do what I need.
I have a table called "Entry", with a form called EntryForm based on it. That table has the follwing fields:
Name
Date
Hours
Set* cboSet
Subset* cboSubset
Item* cboItem
The fields with asterisks are the ones I am trying to create cascading combos for. I have a table called "Sets" which has the same fieldnames in it, which I am trying to use as the sources for these fields. The form has "cbo" in front of the names. I have attached my code in a document.
It looks like all this does is act like an Excel autofilter. Can someone help me with the sourcing for these fields and how I get the Entry table to populate as I make entries. All my code does is push me to edit the Sets table records.
Thanks for any help!!
Working in MS Access 2007.
I am creating a small database for a budget and Im trying to create 3 cascading combo boxes on a form (areabox2 , devbox2, entitybox2) . I have it right now so that that
devbox2 is based on areabox2
and
entitybox2 is based on devbox2
BUT!!!!!!!
I want entitybox2 to be based on both areabox2 and devbox2
Parameters are :
Table = Budget Info
areabox2 data = [Budget Info].[Project Area]
devbox2 data = [Budget Info].Development
entitybox2 data = [Budget Info].Entity
This this the code im currently using :
Private Sub areabox2_AfterUpdate()
devbox2.RowSource = "Select DISTINCT [Budget Info].Development " & _
"FROM [Budget Info] " & _
"WHERE [Budget Info].[Project Area] = '" & areabox2.Value & "' " & _
"ORDER BY [Budget Info].[Development];"
Me.devbox2.Requery
[Code] .....
I am thinking that i have to add a second WHERE clause to the devbox2_AfterUpdate() but i dont know how?
I have a form with various input boxes, three of which are combo boxes. These combo boxes need to affect one another. So let's call these cmb1, cmb2 & cmb3.
Hypothetically let's say:
Within cmb1 the list is A, B, C & D
If the user was to choose A, cmb2 would show the options 1, 2, 3, 4, 5 etc..
If the user was to choose B, cmb2 would show the options 2, 4, 6, 8, 10 etc..
if the user was to choose C, cmb2 would show the options 1, 3, 5, 7, 9 etc..
(So, it is possible that if the user chooses A from cmb1, cmb2 would show 1 and if the user chooses C from cmb1, cmb2 would also show 1)
Using the example in the brackets above:
If the user chose cmb1 - A then chose cmb2 - 1, cmb3 would then show the options Option1, Option2, Option3 etc..
BUT
If the user chose cmb1 - C then chose cmb2 - 1, cmb3 would then show the options Random1, Random2, Random3 etc..
At the moment I have a table with 3 columns. Within the table is every possible combination of the drop down, i.e:
-A l 1 l Option1 l
-A l 1 l Option2 l
-C l 1 l Random1 l
-C l 1 l Random2 l
Etc...
I have tried to use the example shown on this website but i've had no luck: fontstuff.com/access/acctut10 (If you look at "Example 2", thats what I tried to do)
i have a main form which allows me to fill in Order Information within which is a subform which allows me to fill in Order Lines.My Order Lines contain the following fields:
Extrusion
Length
Qty
etc. etc.
Elswhere in my database (tblLengths) I have set up a list of possible lengths for each Extrusion and therefore when an order is filled out I want the user to be restricted only to those lengths that are possible with the particular extrusion they have chosen. This list contains the ExtrusionID and LengthID so that I can query the available lengths for a particular Extrusion.Within my subform I have set the record source to point at the tblLengths and criteria on the ExtrusionID to point to [Extrusion]. I've also put a requery against the Extrusion field so that I can force the combobox to refresh its list of results.
My problem is that I am getting unexpected results each time I add an Order line into my form and reading up I see that what I am trying to do is a big no-no.
I am totally new to Visual Basic.I have dependent cascading combo boxes setup. The hierarchy goes:
Segment
Family
Class
Brick
Now, I need the value in the Brick field to create a varying number of other cascading combo boxes. They are the Brick Attributes. The brick attributes are the labels for the combo boxes and the brick attribute values would be the values you can chose from within the combo boxes. Depending on which Brick you chose, there are a varying number of Brick Attributes, and thus a varying number of needed combo boxes to pop up.
OK, I so I am trying to filter one combbox tbltCatch from tblLocation (which is a sub filter from tblLocationCategory). Thing is, I am trying to select "Catches" filtered from the tblLocationCategory table. Reason being is because the "Catches" are the same the location types (example below). I know the code I am trying to utilize is incorrect and needs to be modified.
Code:
Dim sCatchLocation As String
sCatchLocation = "SELECT [tblCatches].[Catches_ID], [tblCatches].[LocationCategory_ID], [tblCatches].[Catches] " & _
"FROM tblCatches " & _
"WHERE [tblCatches].[LocationCategory_ID] = " & Me.cboLocal.Value
Me.cboCatches.RowSource = sCatchLocation
Me.cboCatches.Requery
Me.Refresh
how to create the cascading combo boxes that I need. For this, I have three tables:
Locations -- All store locations
Products -- Part Number and DESCRIPTION of any type of item that is available for rent
Serial Numbers -- (Unique) Serial number for each individual product, with its Product ID (foreign key) and Location ID (foreign key)
Each location has its own set of products available for rent, each with their own serial number. There may be more than one of the same TYPE of product at a location, but there will never be a repeated serial number.
I would like to have three (cascading) combo boxes. The first would allow the user to select a LOCATION. The second will have a list of all the types of products available at that location (DESCRIPTION). The third should have the list of SERIAL NUMBERS available at that LOCATION for that type of product (DESCRIPTION).
I'm using three combo boxes - The second (cbosubSkillList) is limited by value selected in the first (cboSkillList). The third (cboSubSkillList2) to be limited by value selected in the second. The third will not populate - If I comment out the code, It WILL populate will all values, but will not populate with code active. All names, properties, etc. with combo boxes, tables, field names parallel - Why am I not populating the third box?
[Code]
Private Sub cboSkillList_AfterUpdate()
With Me![cboSubSkillList]
If IsNull(Me!cboSkillList) Then
[code]...
My goal is to have two combo boxes the first filters the second. it controls the semester that I am viewing. This works if i don't use continous forms but when i do it requeries every record in the form instead of the current record. I am not an access or vb guy but i have picked a lot up. please help!!!
I have attached a copy of my database.