Cascading Combo & NotInList
Oct 3, 2006
I have 2 Cascading Combo boxes setup and working correctly.
cbo1 lists all agents and cbo2 lists the branches that relate to each agent.
tbl_agents and tbl_branches are setup with a 1 to many relationship.
I have used the code (explained here (http://www.fontstuff.com/access/acctut20.htm)) that allows the user to add a new agent if they are not in the list. This works correctly. Now I want to let the user add a new branch for the agent they have just added. The code appears to work correctly from the front end, but when you check tbl_branch, there is a new entry but it is missing the appropriate agentID foreign key (ie, it's not related to the new agent).
Is there something that I'm missing? I assume this is possible, but I must be doing something wrong.
Maybe I need to create a little pop-up form for data entry?
any help appreciated.
View Replies
ADVERTISEMENT
Sep 10, 2014
I have a form with a subform that contains a combobox where I choose companies to add to a project that is on the main form. If the user types in a company name that is not in the database, I run code on the NotInList event that passes the company name using openargs to a company entry form.
After this form is closed, I return to the subform to choose the company name that I just added, only it is not there because I can't figure out where to automatically requery this combobox. My duct tape solution that's working, is a command button that runs this code when clicked:
Code:
Forms!frmProjects_Detail_CSS!ASP_Project.Form!cbocompany.Requery
I have tried putting this code in the OnCurrent, OnFocus, Onclick, OnEnter....etc on the subform and on the combobox itself. So far the only way it requeries is if I use the command button.if you want to see the NotInList code, and the code on my company entry form.
View 5 Replies
View Related
Dec 7, 2005
Dear members,
I have a form with 4 Combo boxes bound to fields in 4 tables;
1) Oblast Name
2) District Name
3) Jamoat Name
4) Village Name
The selections work fine. Cascade works from Oblast to Village Name. My question is ;
Currently the ID's of the bound columns are stored in the tables instead of the corresponding data. For example for the Oblast name; I have the value "3"=" rather than "Khatlon" for District_Name, I have "223", rather than "Muminobad".
Is there way to capture the names of the respective places rather than storing ID's in the table?
Thanks in advance.
Irshad
View 9 Replies
View Related
Apr 11, 2006
This is driving me a little mad, and its probably something quite simple!
I am creating a form which will allow users to narrow down their search based on criteria entered into three combo boxes.
The first combo box pop's up a calendar and is populated when the date is chosen on the calendar.
The second combo box will provide a list of systems that have a record entered on that date
The third combo box will then show a list of case id's based on the system and date selected.
I can get it working when ignoring the date, but as soon as I bring the date combo into the equation I get a data mismatch error. Now I presume that this is to do with holding a date in a text field and comparing with a date/time field in the table, so I have set both to 'short date', but still get the error.
Can anyone provide any pearls of wisdom to help stop me from going insane? (that might be a bit late tho!)
View 1 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
Nov 4, 2007
Hello
I have a database (Access 2007) that has two tables, reports, queries and so far all seems to be working and going well. Quite impressed with it to be honest for my lack of skills.
However, I've run into a bit of trouble and need assistance getting a combo box to work with another combo box to produce list kind of thing.
The database is a vehicle database and I need the 'Vehicle' combo box to give me a list of vehicles. I then need the 'Model' combo box to produce the models of the car selected in the 'Vehicle' combo box.
Example - If I select 'Ford' from the 'Vehicle' combo box list I want all the models to be displayed in a list form thing in the 'Model' combo box so the user can easily select make and model of the car.
I have two tables:
CarMM
VD
The 'CarMM' table contains a list of around 1,500 records of car makes and models. First column has the 'Vehicle' make and the second field coloumn has the 'Model'.
The 'VD' table contains all other information like the charges, name, the make and model of car once selected in combo box.
I then have the form called 'VD'. In this form contains the combo boxes I want to be cascading.
Any advice on how to do this? I'm quite new to this so any help would be nice. Thanks you very much.
View 2 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
Nov 21, 2005
I have an unbound Form with a Subform bound to a query. The query parameters are selected by the users via Unbound cboboxes. The form is working but I would like to add a feature on each cbobox that would allow to show "All" the data (ie:Show the data like if there was no parameter on that field). I can't see how to add this feature, is anybody got any idea?
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
May 24, 2005
Hi folks, this may seem like a silly question... but I'm rather new at this...
I have set up 4 Cascading combo boxes on a form, as per the fantastic little tutorial by SJ McAbney... They work great...
What I want them to do is output the combo box selections back to a table. I have been able to get it to do this, but all it's putting back in the table is the ID number of the combo box selection. I need to get the text there instead. I experimented with switching the column order on the form, so the text is before the ID... and that worked with the output, but messed up a number of other things.
Any suggestions??
Thanks in advance...
B
View 8 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
Jun 28, 2006
Afternoon,
I'm having a problem with a form used to display table records as well as create a new record entry.
The form has 2 combo boxes and the rest are fields.
Combo1: Artist
Combo2: Gallery
I would like it so that when the form opens and displays the first record it will also populate the two combo's with the correct details for that record. Secondly when I click to create a new record it will populate the first combo with all artists but then the second combo will populate with multiple galleries (I think because that artist might have done work with multiple galleries).
I'm a little stuck so any help would be great at this point!
:)
View 2 Replies
View Related
Jul 25, 2006
Im using the code from an 'older' example posted here ages ago and it works fine on other databases when there are only 3 combo boxes. This time I'm using on a total of 5.
The problem is the 3rd, 4th and 5th boxes will not requery to a null value, I've tried using DoCmd.Requery "cbo_whatever" instead of Me.cbo_whatever.Requery in case that makes a difference (as you guessed I'm not much of a code jockey :p)
So am I being a bit thick, why doesn't this work ? I've had a search on the forum and have found many examples (obviously) of cascading box examples etc. but I'm comfortable using this example and can figure out what's wrong.
Private Sub cbo_region_AfterUpdate()
If IsNull(Me.cbo_region) Then
Me.cbo_city = Null
Me.cbo_city.Enabled = False
Me.cbo_city.Locked = True
Else
Me.cbo_city.Enabled = True
Me.cbo_city.Locked = False
Me.cbo_city.Requery
End If
End Sub
Private Sub cbo_city_Afterupdate()
If IsNull(Me.cbo_city) Then
Me.cbo_building = Null
Me.cbo_building.Enabled = False
Me.cbo_building.Locked = True
Else
Me.cbo_building.Enabled = True
Me.cbo_building.Locked = False
Me.cbo_building.Requery
End If
End Sub
Private Sub cbo_building_AfterUpdate()
If IsNull(Me.cbo_building) Then
Me.cbo_floor = Null
Me.cbo_floor.Enabled = False
Me.cbo_floor.Locked = True
Else
Me.cbo_floor.Enabled = True
Me.cbo_floor.Locked = False
Me.cbo_floor.Requery
End If
End Sub
Private Sub cbo_floor_AfterUpdate()
If IsNull(Me.cbo_floor) Then
Me.cbo_room = Null
Me.cbo_room.Enabled = False
Me.cbo_room.Locked = True
Else
Me.cbo_room.Enabled = True
Me.cbo_room.Locked = False
Me.cbo_room.Requery
End If
End Sub
View 3 Replies
View Related
Aug 22, 2006
Hi,
I am using a form with 3 combo boxes name, weight and grade. The combo boxes are cascading:
A name is selected in the first combo box, since each name has different weights attached to it only the weights associated with that name are selected when the weight combo box is clicked. In a similar vein, there are only certian grades associated with a certain weight.
The effect is to filter the results so that an invalid entry cannot occur.
My problem is that once an entry is made and I select a new name for the next entry, the weight and grade boxes remain with the same values instead of updating to new values. Updating of the boxes does not appear to be working.
Does anyone have any ideas how to solve this problem?
Many Thanks
Turbojohn
View 2 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
Sep 28, 2006
Hello to everybody!
I need a big help to solve a problem with my DB Access.
I created a form in a DB where I have to fill some information and store them in a table.
I created the cascading combo box. It works, infact when I select the value in the first Combo box named NOME_PROVIDER the second combo named NUM_MAM comes populated with the value got from a query. Anyhow it works well.
THE PROBLEM right now is that I can only select the first value of NOME_PROVIDER and not all the other. I'm sure that the selection query works, but I'm not able to select the other values. Maybe could be a focus problem or something like that.
PLEASE CAn you give me an help on this matter?
Thanks in advance to everybody!!!
Cheers,
Antonio.
View 8 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
Apr 25, 2008
hi,
I have a db which uses the data taken from cboInstructors which is used in a query and a produces a list in cboExpertise. This procedure is repeated from cboInstructors for cboAssessors and then cboIVName.
This subform then shows all the modules each student have taken, who taught it, who assessed and who IV'd it. The problem is when one selection is made all the subsequent choices are the same as the cbo above it, i.e. the queries do not run again when a diferent selection is made in cboInstuctors.
I hope this clear.
Help please.
Iain :eek::confused:
View 7 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
Jul 3, 2005
I have 2 cascading combo boxes. Thr first calls all 5o states in the US. The Second is "Supposed" to called the call all the cites and zipcodes for the state selected. I based them on the sample cascading db from this forum modified for my db. I have a problem with my WHERE clause. Without the WHERE Clause Combo2 works fine. It shows every record in the table. When I add the WHERE Clause It won't run. I am asked for a parameters for each field in the second combobox.
Table1 has 2 fields-StAbb(PK) and State. Table2 has 5 fields-ZipID,ZipCode, City, & StAbb(FK).
I have a query for each table that the combo boxes run off.
The rowsource for combo1 is
"SELECT tbl_states.StAbb, tbl_states.State FROM tbl_states ORDER BY tbl_states.StAbb;"
The rowsource for Combo2 is:
"SELECT qry_zipcodes.ZipID, qry_zipcodes.City, qry_zipcodes.ZipCode, qry_zipcodes.StID FROM qry_zipcodes WHERE qry_zipcodes.StAbb=Forms!frm_clirntinformation!com bo24 ORDER BY [City], [ZipCode];
It doesn't matter whether I run them off the querys or directly off the underlying tables. The same thing happens. Can anyone tell me what I am doing wrong? As far as I can see I followed the example db.
View 8 Replies
View Related
Oct 12, 2005
I have managed to set up a cascading combo box by following this guide:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209576
I have two boxes - [BusinessUnit] and [Location]. When I load the form or select one of the [BusinessUnit] I get a box which prompts me to enter a paramter value - [BusinessUnitID]. If I just ok it without entering anything I get to the form and my location select box has the correct locations listed.
What have I done wrong and how do I get rid of this prompt?
Thanks
View 2 Replies
View Related
Jan 20, 2006
I am using the below sub to create a cascading combo box, which works fine.
However, not every subject in tblsubjects has a subcategory, so I want a msgbox to appear only when there are subcategories to choose from. At the moment I am using
If Me![Subject2] = "Dispute Resolution" Or [Subject2] = "Employment" Then Msgbox "text"
but this will mean changing the code everytime new subjects with subcategories are added. Can anyone help please?
Thanks!
Alison
Private Sub Subject1_AfterUpdate()
On Error Resume Next
Subcategory1.RowSource = "Select tblsubjects.Subcategory " & _
"FROM tblsubjects " & _
"WHERE tblsubjects.Subject = '" & Subject1.Value & "' " & _
"ORDER BY tblsubjects.Subcategory;"
End Sub
View 14 Replies
View Related
Jul 13, 2006
I have attached a Microsoft inventory db that I have attempted to alter and apply to my needs.
I added cascading combo boxes to allow the users to make selections of products specific to categories. Once the category is selected in the CategoryID combobox the cascaded combo boxes ProductName & SerialNumber become populated with the products that belong to the category selected in CategoryID. This works as it should.
The problem comes after a record is selected and an attempt to switch categories is made. For example, say an inventory transaction for the product named "V1" which belongs to the "Special" category is entered. As soon as I switch back to the "Routine" category with the combobox, the product "V1" is now associated with the "Routine" category.
I know Access is just doing what it's supposed to but I need to find out how to prevent the category information for products from changing when a new category is selected in the first combobox. I've tried requeries, gotoRecords and various other commands without luck.
I have attached my db in hopes that a solution can be reached more efficiently.
Thanks in advance.
Shane
View 6 Replies
View Related
