Subform combobox filtering

[warren0127]

Hi. I have created a Main form that has 4-5 tabs on it. Each tab holds an embedded subform. Each of those subforms were created from Queries. Each subform is for data entry that goes into tables. When I embedded the subforms, I linked them with the primary key of the parent table and child tables, which I used ProjectID - because the main form was built from the projects table, which is the parent table, and the child tables have a ProjectID FK.

So the main form is bound to the Projects table. The first subform is bound to a table called “Products”. The second subform is bound to a table called “Transitions”.



It works GREAT thus far. Each tab displays the contents of a particular project’s items. IE: Tab 2 – Products subform, Tab 3 – Transitions subform, etc – shows all of the records for whichever Project I am viewing and gives the user the ability to ADD new entries. NOTE: I have it setup so where the user can filter his/her project in the Main form as an entry point. The product tab shows the different product records for the project, and the transition tab shows the different transitions for the project. I can use the records selector buttons to navigate the items for the Filtered Project I selected.

I have one problem that I cannot seem to figure out. In the Transitions subform, there is a combobox that the user can pick a Product from, bound to the Product table. I would like to filter the “Product” combobox I have in the Transitions subform that only apply to the Project I am viewing. This is because, after the user enters a new product in the Product Subform, that Product should be available in the Product combobox in the Transitions Subform – which it is - except the user has to scroll down in the combobox to look for the product he/she just entered in the previous subform tab. How can I filter a combobox in a subform that should only display the Products for the specific Project being filtered, so the user wont have to go through hundreds of combobox choices? IE: Project A only has 5 products associated with it, but the user can see all 100 products in the combobox stored in the products table – which isnt what I want.

I went through multiple threads with a similar issue like mine, but they removed the example database attachments and I cant see how exactly they were able to do it. I tried changing the parent/child links in the subform, but doesn’t work.

Tried to follow this thread but was not clear to me how he was able to do it.

Hope this makes sense – thanks for any help!

[June7]

The thread link doesn't work.

You describe dependent (cascading) combobox. http://datapigtechnologies.com/flash...combobox2.html

The RowSource for the combobox would be something like:

SELECT ProductID, ProductName FROM Products WHERE ProjectID = [ProjectID];

[warren0127]

Sorry for the invalid thread link - just fixed it.

Yeah - I checked out the videos of the cascading comboboxes, I tried to replicate what was done in the video but seems to be a bit different than my setup. Thanks for the response - I will look more into the SQL statement. I'm guessing that I need to build another query and have the combobox rowsource be bound to that query?

[June7]

Bound to query object or build the SQL statement directly in the RowSource property.

[warren0127]

So the ProjectID Combobox is in the Product subform and the ProductID combobox is in the Transitions subform. Do you think if I applied the cascading combobox method as shown in the video above, it would work, even though on two different subforms? I would probably need some extra criteria statements to include the subform names and the main form.

I also tried to use/add your SQL to the rowsource of the ProductID combobox and nothing happened

[June7]

I am confused.

Why is ProjectID a combobox on Product subform? Isn't Product subform linked to the Projects main form and isn't ProjectID the PK/FK link?

If Transitions subform is also linked to Projects main form, and ProjectID is the linking value, then Transitions must have ProjectID as foreign key in its RecordSource.

[warren0127]

Hi June,

I have the ProjectID combobox on the Product subform to display the Project Name and for initial testing purposes, but since it already knows which Project it belongs to, I can remove it.

I am displaying the ProjectID combobox on the transitions subform as well for the same reason - I pulled it in from the query I had built the form on. Currently, the ProjectID does not have any FK in the Transitions table.

However, the ProductID is linked to the transitions table with a PK/FK.

[June7]

Not really understanding data relationships. If you want to provide db for analysis, follow instructions at bottom of my post.

[warren0127]

I needed to use a ProjectID Combobox in order to get the ProductID combobox to filter in the transitions subform. So what I did was, I have the ProjectID combobox "hidden" in the Product subform since the user wont need to see it - it automatically knows which Project the user is viewing. I also made the ProjectID a FK in the transitions table as you suggested. I guess this is because, the parent main form's record source is bound to the Project Table, and not the Products table, so the FK of ProjectID needed to be included in the transitions table - please clarify this is correct.

So I was able to modify the criteria in the ProductID combobox in the transitions subform to say:

(under the ProjectID column in the query builder):

Forms!frmMain!frmProductQueryControl.Form!cboProje ct

When going back to the transitions subform, under the product combobox, it has successfully filtered. The good news is, I wont have to make a requery VBA statement since the user will be only viewing his/her project.

Thanks again for your help!

[June7]

I don't know if it is correct to have ProjectID in Transitions table. I'm still not sure what the data relationships are. You should know how your data is related.

If the ProjectID is included in the Transitions form RecordSource, should not need to reference the combobox on main form. Just reference the field in Transitions.

[warren0127]

I just now added the ProjectID as a FK in the transitions table. Before, only the ProductID had an FK in the transitions table because everything should be tied to the "Product" but has an umbrella of the "Project" (if that makes sense). But because I based my main form on the parent table of the Projects, I'm guessing it wont know the linkage without the actual parent Key.


(note the ProjectID is in the transitions table, just under the ProductsID, but forgot to scroll down a bit when taking the SS )

[June7]

Be careful of 'spider web' design, review http://www.codeproject.com/Articles/...atabase-Design

I don't know what a 'transition' is. I would still be guessing in describing what this db is for. If Transitions is supposed to be dependent on product then why would it be linked to project? Perhaps you should have a form/subform/subsubform arrangement.

[warren0127]

Thanks for sharing the URL - I'll reconsider how my relationships should be designed.

Ultimately, for each Project there are several products. Each product transitions to a customer when it is completed. So, the product is what the customer wants - once complete, it becomes a customer solution.

Yeah - Transitions are linked to Products - and Products fall under a single Project. So you are correct, transitions shouldn't be linked to a Project since they are Product based. But I had to include the ProjectID FK in the transitions table because I used the Project table as the main form (or bound to it). So you're saying I should redesign my form - I probably should. I just wanted everything to start at the "Project" level. IE: I select my own project, i get to see the products under it, and transitions, etc. What you are telling me is perhaps I should be making a transitions sub-sub form or something similar? I'm new to all of this.. thanks for bearing with me as I try and figure it all out

[warren0127]

June - do you have a reference you could share that explains more about the sub-subforms? Thanks much.

[June7]

A subsubform is created same as subform. I don't know any tutorial that explicitly demonstrates subsubform. However, here is an alternative structure: http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp