Filter Vendor POC combo box based on the selected Vendor

[annsolomon]

I have a purchase order (frmPO) that has a combo box (cboVendor) to select a vendor. Once the vendor is selected, the various address lines are automatically populated.

Below this, I have a combo box (cboPOC) to select the vendor point of contact. Right now, the box displays all POCs in the underlying table. I'd like the combo box to display only those POCs that relate to the vendor that the user selected.

Is this easy? Am I overlooking something simple? Any help will be enormously appreciated.

Ann

 

[johndweidauer]

Yes.

ComboBox has a filter property. Set this property to "[VendorID] = [form]![frmPO].[cboVendor]"

I think the syntax is correct.

 

[annsolomon]

Okay, so maybe I'm brain dead. Where would I place this line of code?

Ann

 

[johndweidauer]

Select the outer edge of the sub-form and then select propreties. there will be a properties box in the properties window labled "Link On" or "Linked field", i dont remember, not on a machine with access, but it is easily identifiable.

 

[annsolomon]

Yes, I knew I could link fields between main/sub forms. However, my POC field is a field on the main form as are the vendor fields on the main form.

Other ideas? If there's no other way to do it, I guess I will create a subform for the POC.

Ann

 

[annsolomon]

After changing the combo box Row Source (i.e., adding a WHERE clause) it filters the way I want:

SELECT [qryVendorPOCs].[POCID], [qryVendorPOCs].[POCName], [qryVendorPOCs].[POCPhone], [qryVendorPOCs].[VendorID] FROM qryVendorPOCs WHERE [qryVendorPOCs].[VendorID]=[forms]![frmPO].[VendorID] ORDER BY [qryVendorPOCs].[POCName];

Ann

 

[LangAdel]

Ann

I have the same problem and it sounds like you have solved yours by adding the WHERE clause. It works partly for me. I get the right "filter" but alwas the POC's related to the first vendor. So if I choose another vendor my form doesnt seem to be updated. Any suggestions?

Brian

 

[annsolomon]

Hi Brian. Sorry for the delay; I've been out of pocket for a few days.

Try putting this into the OnCurrent event of your form:

Me.NameOfYourPOCComboBox.Requery

Hopefully, you'll get the same results I do.

Ann

 

[LangAdel]

Hi Ann

It dit'nt work exactly the same way, because I have the combobox on the same form. But if I put the Me.combobox.Request on the Combobox's own property under "On got focus" it works perfectly.

You've earned a star for this
Brian

 

[KenReay]

Hi

See FAQ on this subject in forms forum

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK