This seems so easy, but I can't find the answer anywhere. I have a lookup table called Types with two fields. We'll call the two fields Tfield1 and Tfield2. I have another table called Dtype. It has a field we'll call Dfield1 which is a lookup field bound to Tfield1. Dtype has another field we'll call Dfield2. I would like Dfield2 to get its value from Types based on the value chosen for Dfield1. Here's what I mean:
It's eminently possible, but pointless! In effect, you are performing a simple calculation to derive the second field. But if you want to return that value you can simply do the calculation at the time it's needed. And your table is half the size.
But, if you insist, you need a form based on your first table. In the After Update event of your Dfield1 put
Actually, I was just using numbers for an easy example. Tfield1 actually holds section names within our organization, and Tfield2 holds Division names within our organization. I think I finally figured it out though. I have a form with a combo box that puts the value of Tfield1 into Dfield1. I have a textbox bound to Dfield2 called txtDfield2. Then in the AfterUpdate event of the combo box, I put :
txtDfield2 = Me!Dfield1.
The general rule is if you CAN calculate it, don't store it.
Only a very limited set of data will change in the future so that you cannot calculate it again anytime you want. In those cases database normalization rules are not violated by storing the data (prices on invoices, wages for paychecks, etc), because you cannot get it back later.
Are you trying to automatically select the Division when you choose a Section in the first combo box? Which structure includes the other (are Sections within Divisions, or vice versa? I confess I do not fully understand what you are after...
Yes. I am trying to automatically select a Division when I choose a Section. Sections are within Divisions. Sorry for not giving more details. I guess I was trying to be too general. Anyway, I got it to work. Thanks again.
Make a table of your Divisions and the Sections within them. Example:
Code:
SecID Division Section
1 Accounting Payroll
2 Accounting Pension
3 Human Resources Complaints
4 Human Resources Hiring
etc...
Now make a combo on your form (in place of, say DField2). The Row Source (under the Data tab) should look up the values in your new table. You can make the first and 2nd fields 0" width (functionally invisible) if you like. If you're generally choosing by Section, reverse their order. It should store (Control Source) in your new field, which will be type Number if SecID is a Autonumber in that table.
Now your "Division" textbox should have this as its Control Source: =ComboDField2.Column(x), where x is the column of the Division in that other table. When you fill in a Section value, it will display (but not store!!) the Division on your form.
This has several benefits:
a) storing this in a separate table means you can create updates and changes easier by just opening the table, instead of editing a Value List by hand. Make sure you limit access to this table, and/or hide it from unwanted fingers!
b) You're storing one Number field (2-4 bytes, probably), instead of 2 Text fields. And if "Complaints" ever changes its name to "Employee Services", you can change it once in the lookup table and all your records will reflect it instantly. No complicated Append Queries needed.
c) If a Section ever gets moved to a different Division, make a new entry in your table. Leave the old one for archive purposes! You might put a checkbox field in that table called [Current] and only display the entries that are checked. Then you can not display old records but still have them around for reports, etc. The same applies if you'd rather keep old records as being under the "Complaints" Section, not "Employee Services".
Hope that helps. Post back if something's still confusing you.
