Filter continuous subform on tabcontrol (1 Viewer)

nhorton79

Registered User.
Local time
Tomorrow, 06:26
Joined
Aug 17, 2015
Messages
147
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.
 

Attachments

  • StrippedDown.accdb
    2.2 MB · Views: 192

theDBguy

I’m here to help
Staff member
Local time
Today, 10:26
Joined
Oct 29, 2018
Messages
21,358
Hi. This is probably not what you had in mind, but it might still work for you.
 

Attachments

  • StrippedDown.zip
    679.2 KB · Views: 197

nhorton79

Registered User.
Local time
Tomorrow, 06:26
Joined
Aug 17, 2015
Messages
147
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 :
Code:
Me.txtItemTypeID = DLookup("ItemTypeID", "tblItemType", "ItemType='" & Me.TabCtl0.Pages(Me.TabCtl0).Caption & "'")
the only change you made?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:26
Joined
Oct 29, 2018
Messages
21,358
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 :
Code:
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

Well-known member
Local time
Today, 13:26
Joined
Mar 28, 2020
Messages
1,030
I really could have used this idea in a past project. This is a great idea.
 

nhorton79

Registered User.
Local time
Tomorrow, 06:26
Joined
Aug 17, 2015
Messages
147
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....?
 

Attachments

  • DBGuy.jpg
    DBGuy.jpg
    81 KB · Views: 163

Users who are viewing this thread

Top Bottom