Cascade Combo Boxes

[ebartlett]

I need to know how to do a cascade combo box. I have searched the archive and can't really figure it out. Is there any way to do it so it will work with any version of access. I have heard if you use the VBA it will not be compatable. Is this true?

 

[mdemarte]

By cascade, do you mean you select a value in the combo box and it automatically updates another field? In your combo box, you would select all of the fields you want to update. You hide the ones you don't need to see to make your selection. Then you create an Event Procedure using VBA in the After Update option to set the other fields. For example, after I pick a vendor by name (in combobox labeled cmbVendorname), then I update the vendorno. The first column is column 0, so the line in VBA to update vendorno is

vendorno = [cmbVendorname].Column(1)

I also usually set vendorno to Locked Yes so that the user can't update it.

Now, about VBA. Generally speaking, most software is upward compatible. Which means for your current release of software and releases AFTER it you should be fine. Best bet is to test it though.

 

[David R]

Keep in mind that if the different fields are all uniquely tied to a single field (like a VendorNo), and they are not going to change in the future, you can store ONE field and just display the rest from the lookup table whenever you want. Doing otherwise violates relational database design rules.
For things like invoices and fluctuating prices, this rule does not apply, in general.

Just a thought.
David R

 

[ebartlett]

Ok...here is the exact deal I have going. I have a datasheet that just has say 10 lines that I am using for one combo box. Say the districts. Then as I have it right now I have 10 other data sheets that have sites in each of the 10 districts. I want only the sites in that district to come up. Do I want to have just one datasheet for the sites? How do I do this cascade??? I am playing dumb I know but can someone walk me through it? I have aol IM or I can make a phonecall if it would help...email me if this is possible?

 

[David R]

Oh okay, I understand now. What you want is called 'cascading combo boxes' and they've come up many times in the archives here. Do a search for 'cascad combo box' or check the Knowledge Base article: http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q209595

 

[mdemarte]

First of all, my apology to ebartlett for misunderstanding what you needed. Second, it was not a DUMB question. As a matter of fact, thank you for clarifying and thank you David R for the answer because that is what I need to do next in the database that I am working on.