Form - List Box

[Rohdester]

Hi!

I need some help with a list box on a form. I'm new to Access which accounts for some of my difficulties I'm sure. Any hints, pointers etc. would be appreciated.

Anyway here goes.

I'm doing a database used for management of sponsorships etc. I have four tables: Companies, Contact Persons, Sponsorships and Products. I have a one-to-many relationship between Company and Contact Person as well as Company and Sponsorhips

Anyway, the idea is that the form for Company contains a list box which shows the names of the company's contact persons.

The list box's Row Source reads:
"SELECT Contactpersons.Name FROM Contactpersons"

the problem is that this (of course) lists ALL contact whereas it should list only the contacts for the firm (ergo where Contactperson.CompanyID equals the actual company's CompanyID).

How do I do that? Ergo how do I get a hold of the form's current company's companyID in an SQL statement?

I hope you understand what I'm looking for, if not just ask for clarifications.

Thanks
Jacob

 

[sonny]

If the company ID is on the form somewhere then all you have to do is include a where clause to the control where it equals it.

SELECT Contactpersons.Name FROM Contactpersons WHERE Contactperson.CompanyID = Forms!MyForm!ControlName

 

[Ian Mac]

Just to expand on Sunny's post,

While you in the Query Builder, right click in the Criteria box of the CompanyID and select Build, you can then navigate to Forms>Your Form>Your Control this will then base the Criteria on the Control you have selected.

 

[Rohdester]

Thanks

Thanks a lot for your posts. They helped a lot.