Combobox question

[Noclueforid]

Hello!

I have a table that will track projects. Each project can have many sub-projects, so i created a table to track those and created a relationship between them. Each sub-project will have many different vendors that will be used, so I created a vendors table. I need to track the time the vendors spend on the project. I want to create a dropdown box so the user can select the vendor from the list quickly. Each project will have its own set of vendors, so how do I only show the vendors in the dropdown box for that specific project? I don't want a basic show-all dropdown box as eventually the list will be huge and finding a specific vendor will get cumbersome.

Thanks!

 

[adam087]

What's your table structure? Specifically what table allocates vendors to projects?

You'd use something like

SELECT {vendors} FROM {vendor-project table} WHERE {project}='{thisProject}';

Or perhaps

SELECT DISTINCT {vendors} FROM {sub project table} WHERE {project}='{thisProject}';

Not sure if that helps!

/AJ

 

[Noclueforid]

I have 3 tables. The tblProjectMain table, then I have the tblProjectSub table. My third table is the tblVendors table. I have a field named lngAutoNumber set as the primary key for each table. In my ProjectSub table I have a field named lngLink that links it to the ProjectMain table. In my Vendors table I have a field named lngLink that I can get to populate with the ProjectSub tables lngAutoNumber, but where I have my issues is sorting the combo box to only show the records that match the currently viewed project.

I am no expert at Access and usually sort out all my problems via these forums and Google, but I can't really find anything related to this. Maybe I am not asking it in the right way.

I will try the suggestions you have posted! Thanks!

 

[adam087]

You're probably better off having a separate table to allocate Vendors to Projects (or sub projects) as a vendor could do more than one project.
{Table SubProj PK spID} - {Table Proj-Vend spID == vID} - {Table Vendors PK vID}

There're some good relational database resources out there to help understand the structure.

/AJ