Limit choices in combo box 2 based on box 1 choice

[morg59jeep]

I have a Vendor field for each Vendor and a cost code field. What I need to setup is when a vendor is chosen only the appropriate cost codes for that vendor are available to choose from the combo box. How do I do this on my form. We are having problems where the data entry person enters a cost code that has nothing to do with the vendor chosen.

 

[randy700]

Try this....

Combo Box - dependant on another Combo Box faq702-4289

Randy

 

[traingamer]

Among other places see thread702-1040180

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[morg59jeep]

the example that traingamer posted looks like it will only work if (in my situation) each costcode could only be assigned to each vendor. Im my situation though we use more than one vendor depending on whats happening at the time for each costcode.

I currently dont have any table linking vendor and costcode so I am wondering what kind of fields do I use to create the table? Once I get the table right i can work on the form to limit the choices.

 

[morg59jeep]

Sorry for the lack of detail but I am just learning this more advanced stuff. Thanks for your patience lets try again.

Ok here is what I have at this point in detail.

I have a 4 Tables

vendInfo
ID (PK)
vendName
vendAddress
vendPhone

costBreakdowns
costCode (PK)
costName

CostCodeFormControl
FormEntryID (PK)
costCode (lookup from costBreakdowns)
vendName (lookup from vendInfo table)

Bills
Bill ID (PK)
billedAmount
vendName (lookup from vendInfo table)
lotNumber
costCode (lookup from costBreakdowns table)
invoiceNumber

I have a form that is for new entries into the bills table. When you select a vendName from the combo-box it needs to limit the combo-box for costCodes to only those cost codes that match entries in the table CostCodesFormControl ... Each costCode has more than one possible vendName and each vendName has multiple possible costcodes as defiend by the CostCodesFormControl table. How do I go about getting my form to limit these selections.

 

[morg59jeep]

Im bumping this per still not fixed.

 

[randy700]

Have you viewed the threads provided by myself and traingamer?

Randy

 

[morg59jeep]

Yes.. those in my understanding dont apply to my situation where I have multiple possiblities. In my situation every vendor has multiple cost codes and every cost code has multiple vendors. I cant figure out how to make your example apply. It does not have a table that has records that define each bunch of possibilities.

 

[traingamer]

Use a query like :

SELECT CostCodeFormControl.costcode, CostCodeFormControl.vendname
FROM CostCodeFormControl
where vendname=[Forms]![your form name]![your vendor combo box]
GROUP BY CostCodeFormControl.costcode, CostCodeFormControl.vendname
;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill