Filter continuous subform on tabcontrol

[nhorton79]

Hi All,

I want to be able to have a list of items display on multiple tabs on a form, but each list of items to be filtered to the type of tab.

I have created a test form with a tabcontrol on it and managed to get the tab names (captions) defined from the records in a table.
I want them defined this way so that in future I can add additional tab names in the table and have them automatically generate on the form without having to edit the form and release a new version of the database.

So this part is sorted, however, this is where I come unstuck.
I want to have a copy of the same subform on each tab (one again, so that I don't have to edit and release new versions just to add an additional tab).

Each subform (a continuous form) will display "Item" records related to the job, filtered/joined on tblJob.JobID and tblItem.JobID_FK, however, I would also like to filter each one based on the tblItem.ItemTypeID_FK

I've attached a stripped down copy of the database with only the main form, subform and necessary code/tables to display.

If someone was able to assist that would be great.

 

[theDBguy]

Hi. This is probably not what you had in mind, but it might still work for you.

 

[nhorton79]

Hi DBguy, this is exactly what I was trying to achieve. I've quickly added your population of the txtItemTypeID to the load event of the from which ensure that on first load it shows the correct items.

However, I am still trying to determine what else you have done.

Is that single line :

Me.txtItemTypeID = DLookup("ItemTypeID", "tblItemType", "ItemType='" & Me.TabCtl0.Pages(Me.TabCtl0).Caption & "'")

the only change you made?

 

[theDBguy]

Hi DBguy, this is exactly what I was trying to achieve. I've quickly added your population of the txtItemTypeID to the load event of the from which ensure that on first load it shows the correct items.

However, I am still trying to determine what else you have done.

Is that single line :

Me.txtItemTypeID = DLookup("ItemTypeID", "tblItemType", "ItemType='" & Me.TabCtl0.Pages(Me.TabCtl0).Caption & "'")

the only change you made?

I did a couple of other things too.

1. I removed the subforms from all the tab pages
2. I added the subform to the main form (on top of the tab control
3. I linked the subform to the unbound textbox that contains the ItemTypeID for each change in the tab page

Hope that makes sense...

 

[Mike Krailo]

I really could have used this idea in a past project. This is a great idea.

 

[nhorton79]

Okay. I see what you did there. The tabcontrol doesn't hold the subform, the subform just sits on top and appears to be within the tabcontrol. Genius! And now I also see how to filte rmultiple Master/Child fields too.

Now I just need to work on ensuring that the order comboboxes based on the separate 'tab pages', so will need to filter the count by ItemTypeID as well as JobID. But hopefully that should be relatively easy (touch wood).

Thank you for your help here DBguy.

You sure this shouldn't be your avatar....?

 

[theDBguy]

You sure this shouldn't be your avatar....?

I might borrow that.

Good luck with your project.