Cascading Lookups In A Table
Oct 25, 2006
Probably an easy one that I just can't think my through it. I've been trying to create some (for lack of a better term) cascading fields using the lookup wizard (to eventually be used in a datasheet view/form).
In the main table, the user needs to select a Team (A, B, C), Sub-Team (A1, A2, A3, B1, B2, etc), and a Family (A1 contains bolts, screws, and washers).
The first lookup of selecting a Team (A, B or C) was easy. However after this point I'm stuck. If the user chooses A, I only want the "A" related sub-teams to show in the next lookup. Then based on the sub-team chosen, I want the Family list restricted again.
Any ideas would be appreciated. Thanks!
View Replies
ADVERTISEMENT
Dec 4, 2006
Simple questions I hope.
1.
What I'd like to do is do a lookup based on 2 fields in my table.
I'm tracking inventory for the company I am currently working with.
I have a computer table with 3 relevant fields:
compID, areaId, locationId
computer table
compID = PK for this table
areaId = FK from area table
locationId = FK from location table
area table
areaId
location table
locationId
areaId
There is a distinct relationship between area + location.
I want a lookup for the locationID, based on the area they have selected.
Thanks for any help on this one. On to the next question related to this.
2. As mentioned above I have an area table controlling the general departments (for lack of a better word). When an area is selected in the table, they have the ability to do a lookup for the relevant locations (implemented in the combo box).
I'd like the user to be able to add a new location item in the computer table, and have that value be added into the location table with the corresponding areaId as well. Am I looking for cascade update or something of this sort?
Thanks anyone. This is my first post here, I'm quite new to microsoft access as a database tool.
View 1 Replies
View Related
Apr 25, 2014
I have one attribute in the table and the table is normalized. But we have the same attribute in two different forms, one with a table lookup and the other with a free form text input. I'm trying to rationalize this for a dashboard output.
Do I keep it as one attribute or do I make 2 - one for the table lookup and 1 for the free form input? What are the pros and cons of each?
View 5 Replies
View Related
Jan 7, 2013
I am trying to link two fields in a table here is an example ....
Field A
Values stored could be....
AAA, BBB, CCC, DDD etc
LIST TO LOOK UP
AAA = UDC2
BBB = UDC1
CCC = UDC4
DDD = UDC3
Field B
Needs to lookup listi n Field A
and get correct UDC1, UDC2 etc...
View 3 Replies
View Related
May 31, 2007
Hi there,
I'm pretty new to Access so I hope this question is not to simple/stupid ;)
Ok, I have an Excel Worksheet that I want to import into an Access Table. The column headers are identical! The only problem is:
- the Access table looks up a couple of the columns from other Access tables. When I try to import the Excel data, Access does (properly) report an error.
- Error: the records from the Excel file were added to the Access table but not all values were imported (no values were imported into the lookup columns...)
Any idea how I should procede so that I can import my data into Access?
Thanks!
Steve
View 3 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
Nov 22, 2006
Hi All
I've worked with Access in the past but haven't touched it for 6 years now so I've forgot a lot of the things i've learnt. Bare with me while I try and explain it as clearly as possible
I'm creating a Database for work which will register all our Commerical Clients, which projects the clients are related to and which students worked on them projects. So I have 3 main Tables:
1.Commercial Clients
Client ID
Company Name
Contact
Title
2. Commercial Projects
Project ID
Nature of Project (Graphic Design, Multimedia, Media)
Project Name
Project Information
Staff Member Responsible
Start Date
End Date
3. Students/Graduates
Student ID
Student Name
Area of Expertise
Contact Details
Last Project worked on
Right here we go.....
Each commercial Client can have more then one project to their name. Each Project can have more than one student working on it and each student can be working on more then one project.
My relationships are set up like this:
Commercial Clients <One to Many> Projects
Projects <Many to Many>Students
What I have done is create forms with sub forms in to enter to extra data. I dont know if this is the right way to do it but it seems to easiest way. Please show me a way that will work better if you know it.
Anyway... the way the tables are now I can on cascade the information down through the tables.... it will not cascade upwards. I have ticked everything you can possibly tick for all relationships. So when I can enter projects in the Subform on the Commerical Clients Form and I can enter students on the subform in the Projects form. But on the Student form the projects the students at on does not show up in that subform and entering the day only tries duplicating it (which i dont want to do). If I had a form that showed the projects and the related commercial clients in the subform it wont find the commerical clients. So i.e.
It shows fields when the cascaded down like:
Commercial Clients
V
V
Projects
V
V
Students/Graduates
But it wont show if its:
Commercial Clients
^
^
Projects
^
^
Students/Graduates
Anyone have any ideas how I get get them to cascade upwards?
Thanks
Pete
View 9 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
Mar 1, 2005
This is my first attempt with cascading combo boxes (2) and with the help of the forum I've made it work BUT, I feel like an idiot because I'm drawing a complete blank on how to link them back to a "main" data table.
The example I used as a reference was to create them as unbound boxes while using queries to pull for each of the combo boxes. I have 4 tables. One will serve as the main table while 3 others are each feeding one of the combo boxes. I went back and set the main table as the record source and added some of the other fields but those unbound fields have me stumped.
If anyone recognizes an earlier post that clarifies this, please let me know. I've looked through numerous posts but haven't found one yet.
Just like everyone else, any help would be greatly appreciated.
mike
View 1 Replies
View Related
Mar 25, 2013
updating my table when I use cascading combo boxes in my form.What is happening is that my table is being populated by the xxxxID column vice from the xxxxName column that is being used from that specific table.
here is my visual basic code that I am using to determine what the subsequent combo box will display.
Option Compare Database
Option Explicit
Private Sub cboPlanktonID_AfterUpdate()
' Set the Family combo box to be limited by the selected Plankton Type
Me.cboFamilyID.RowSource = "SELECT tblFamily.FamilyID, tblFamily.FamilyName FROM tblFamily " & _
" WHERE OrderID = " & Nz(Me.cboPlanktonID) & _
" ORDER BY FamilyName"
[code]...
Example of the Combo box Row Source is: SELECT [tblWaterbody].[WaterbodyID], [tblWaterbody].[WaterbodyName] FROM tblWaterbody;
My Control Source is PlanktonAnalysis.WaterbodyName
When I fill in the form with the data, The Waterbody name is visable for selection (example: I see "Lake Lillinonah" in the cascading combo box, But when I save the record in the PlanktonAnalysis Table I get a number in the WaterbodyName column vice the name of the waterbody
PlanktonAnalysisIDWaterbodyName172
311411511677118397104113
tblWaterbodyWaterbodyIDWaterbodyName2Ashland Pond3Bantam Lake4Crystal Lake5Gardner Lake6Hungerford Park Pond7Lake Lillinonah8Lower Bolton Lake9Middle Bolton Lake10Pocotopaug Lake11West Thompson Reservoir
View 4 Replies
View Related
May 19, 2005
Hi All
I am trying to develop a database for recording project risks and issues and need to assign an owner to each one. My design stage has three tables for project resources, issues and risks. The Resource table is basically ResourceID; Surname; Forename, GivenName (forename and surname concatenated) Status; Role and DateAdded. When I allocate a risk or issue I want to be able to select the ResourceID from a drop down list that references the Resource table (I have got this working) and then from that ResourceID value to automatically populate another field with the matching GivenName and that is where I am getting stuck. I have setup relationships between the tables based on ResourceID but can't see where I need to be with a query to do this. Someone a suggetsed a union query but not sure of the syntax for this. I know I could have a separate lookup for the GivenName but that invites data capture error and requires the user to undertake an extra input step that could be avoided with a bit of slick programming.
Hopefully the above provides enough information to identify the problem and possible solution - any help on this appreciated.
Thanks
Andy
View 4 Replies
View Related
May 18, 2006
I am a total newbie and possibly in over my head already.
I have 2 tables:
tblEmployees
..EmployeeID autonumber PK
..FirstName text
..LastName text
..DepartmentID number
..HourlyRate currency
tblDepartments
..DepartmentID autonumber PK
..DepartmentName text
Many-to-one relationship tblEmployees to tblDepartments
I need to create a form to enter employees in tblEmployees.
FirstName, LastName, HourlyRate are all straightforward. I'm stuck (probably a HUGE mental block!) on how to get the DepartmentName into the tblEmployees from the input in the form.
Please help!
View 1 Replies
View Related
Aug 6, 2013
I am trying to use a combo box to control which table a data entry form will write data to. I want to create a form that has a combo box to select from a top level table that I will call "Stores". Once a "Store" is selected from the drop down, the next field on the form will be a data entry field. The data entered in that field will be written to the table selected by the preceding combo box.
So, basically I would have say Wal-Mart, Macy's, Sears, K-Mart and etc, listed in my "Stores" table. Once I select one of the stores from the drop down, I would then enter a "department" name in the data entry field and based on which store I selected from the previous combo box, the data would be written to that stores department table (which each store will have its own department table), e.g., WalMartDepts, MacyDepts, SearsDepts, etc...
View 1 Replies
View Related
Aug 4, 2014
I have a form with two cascading comboboxes where the first selection is the alphabet and the second selection is a person's name. (the alphabet is a coding system but not assigned based on a person's name) I used one table based on example 2 of this website: fontstuff. com/access/acctut10.htm
What I want to do is add columns to the table such as organization, purpose, etc. then have them in the form as textboxes that update as soon as the second combo box is selected. Is this possible with just one table or do I need to go a different route?
View 11 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
Apr 27, 2013
I'm currently using Access 2010 and I'm working on a database project. My question is related to table relationships. Within that project I do have a table that is related to other three tables where that table is the parent. The problem here is that once that table is updated or have a new value it never cascades it down to the other tables.
1- How to have multi relationship to the same field on the same table from different tables?
2- how to cascade the updates to the related fields?
3- Is there any way to force the data update to other tables?
View 5 Replies
View Related
Oct 20, 2006
In an attempt to eliminate user input error,I am Looking Up values from a field in a separate table as input for my SEMINAR field. The table containing my Look Up field has only one other field, which is an auto-numbered key field.
When I view my SEMINAR field in datasheet view, the data is as I would expect to see. However, when I use my SEMINAR field in a report or query, it returns the value of the autonumbered key field. The data type for both the SEMINAR field and the Lookup field are text. - Any Thoughts?
Golfer
View 3 Replies
View Related
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 3 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
Aug 18, 2005
I have a subform in continuous mode which has a list of people. I have created an unbound field which has a dlookup into a telephone number table.
What I want to happen is for each person in the list to have their telephone number displayed (not recorded - just displayed) on the form alongside their name. On a single form this works fine, but on a continuous form it displays the same phone number on all the rows (presumably the first person in the lists number).
Can anybody advise how I can get the dlookup working for each record in the continous form.
Thanks in advance,
Adam.
View 14 Replies
View Related
Apr 27, 2006
Hi Guys,
First off, a big thanks to everyone on the site. I have learnt a lot since first discovering this site a few weeks back.
Problem:
Having understood that it is better to create SQL lookups to queries of tables rather than to the tables directly, I am having trouble understanding what value I should store in my main table, a text value or the ID (number) (of the text value.)
I have an asset table with a field Equipment Type. This field looks at a query of the EquipmentType Table.
Would it better to store the text value "Printer" in the main asset table (in which case I can query the table directly but the field will use up more space (i.e. 25 char)?)
or
Store the Equipment_Type_ID "1" relating to the Printer (will use up less space, but mean any queries querying the actual name would have to include the EquipmentType table).
Any advise would be much appreciated.
:confused: My thought was that I should go with the ID as otherwise I will be storing duplicate data. If this is the case, when would a text value be more suitable.
View 1 Replies
View Related
Jan 2, 2015
We are on Win7 with Office 2013 (32-bit). I have had to migrate my application away from a network that is open to the web. I am now on a more isolated network. My problem is that I can no longer use Outlook for e-mail.
I knew that you can use CDO to send SMTP via a Mail Relay server. We've got one of those on this isolated network... but there is a show-stopper here. When setting up the configuration portion of the CDO message, there are these references using
CDO.Configuration.Fields.Item("HTTP:schema.micro soft.com.... etc.) = value
These references are how you define the SMTP server, server port, authentication method, etc. My problem with this is I can't get to that Microsoft site from the isolated network. The SMTP Relay won't do this lookup for me and I have no general internet access.
When I looked into the CDO library, I found that there were definitions for the fields like cdoSMTPAuthenticate and cdoSMTPServer and cdoSMTPServerPort - but when I attempt to use them, Access doesn't seem to like them even though the CDO library is checked in my references list and those values are defined. For what it is worth, the library file says it is version 6.6.7501 (or something close to that), so it is not the older version. The revision date is 6/6/2012, just about right for a file that would have been included in Office 2013's library set.
How to use CDO when you can't do the HTTP lookups? (I know how to use it when the lookups work...)
View 5 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 23, 2006
Hi,
I'm fairly new to access.
I'm trying to design a database for my work network which will show how nodes are connected. I currently have the following tables:
section - refers to agreggate cable links between buildings/sites
media - refers to individual links
Network_element - refers to switches/routers
section (section_id PKEY,media_type, start_site, start_building, start_cabinet,
end_site, end_building, end_cabinet).
media (section_id PKEY, from_pair, to_pair, from_ip, to_ip)
Network_element( Name PKEY, MAC, IP)
I'd like to be able to make sure access will only allow from_ip and to_ip to be entered if there is a corresponding value in the Network_element table, however I do not know how to enforce this.
Any help much appreciated.
Thanks
Dan
ps - if anyone has done something similar, I'd be keen to see how they gone about it.
View 1 Replies
View Related
Jan 27, 2006
Can someone please tell me if there is a way to perform lookups in Excel from an Access table without first exporting the data into Excel? I have a form that is being exported into Excel from a pricing tool our company uses from an outside vendor. It is important the users have the ability to perform complicated calculations and are not at all experienced in Access. There are several data sources that are well over the 65,000+ rows limited by Excel. I need to either prove or disprove whether it is possible to perform lookups between Office applications. If it is possible to lookup between applications, I would appreciate any sources known to learn how to do this. If not possible, I would like to be able to confirm that fact to my manager.
Thanks for any help that can be offered.
tschultz
View 1 Replies
View Related
