Cbo filter subform based on junction table

Zydeceltico

Registered User.
Local time
Today, 16:10
Joined
Dec 5, 2017
Messages
843
Hi All -

Functional question today.

I know how to filter a datasheet. I know how to use form references for the most part - subforms still confuse me. I'm pretty familiar with utilizing combo boxes and column counts and hiding ID fields, etc., etc.

However (in scary Boris Karloff voice) - - I have created a monster. :-) . .......... and to be preemptive...... this part of my db is not a traditional relational model - I know it.

I have three tables: tblInspectionTypes, tblItems, and tblInspectionTypeItems (the junction table).

tblInspectionTypes has 2 fields: InspectionType_ID and InspectionType.

tblItems has 2 fields: Item_ID and Item.

The junction table has 3 fields: InspectionTypeItem_ID, InspectionType_FK, and Item_FK.

As is probably obvious the values in the junction table are all numeric. And can and does look like this:


Code:
InspectionTypeItem_ID     InspectionType_FK       Item_FK
1                                 12             4
2                                 12             114
3                                 13             26
4                                 14             101
5                                 14             87
6                                 14             13
7                                 14             39

This is how the tables are related:
https://www.access-programmers.co.uk/forums/attachment.php?attachmentid=75389&stc=1&d=1562089201

In a nutshell I want to create a main form that has a datasheet as a subform - or at least the appearance of a subform.

The main form will have a combo box at the top of it. The user would select an InspectionType from the combobox and ideally that selection would filter the values in the datasheet below.

Without getting anymore detailed and confusing: I've attached a sample db with two different form attempts. Both - as a GUI - represent where I want this form to go - but the problems with both are best left to "the experience" rather than me trying to explain them.

frmItemFilterTest2 comes close to what I need to do but one will instantly notice an issue when selecting form the combo box.

frmItemFilterTestSplitForm is a split form based on tblInspectionTypeItems - - which if I knew how to get the actual string values to populate the fields would be perfect - - but - -- I can't figure out either one of the conditions. Either will work and get me where I need to get.

I am hoping someone more experienced than I can help me find some clarity regarding how to approach this.

Thanks,

Tim
 

Attachments

Instead of a query for subform RecordSource, just reference table.

In both forms, make InspectionType_FK and Item_FK textboxes into multi-column comboboxes. This will allow saving ID but displaying text.

Not even necessary for user to see autonumber fields. Delete textbox or set textbox not visible and hide column in Split form.
 
Instead of a query for subform RecordSource, just reference table.

In both forms, make InspectionType_FK and Item_FK textboxes into multi-column comboboxes. This will allow saving ID but displaying text.

Not even necessary for user to see autonumber fields. Delete textbox or set textbox not visible and hide column in Split form.

Thanks June! I'll give it a go this evening and post back. I think I follow you. One thing - for clarity - - you are saying use the Split Form version - yes? Not the subform thing? Right?

Any other details that aren't necessarily completely obvious to me?

Thanks a ton

Tim
 
I have only used Split form twice in very, very small, simple db. I do not really like the form. It has quirks that I find annoying.

Could use a normal form (not form/subform) in Continuous view with search box in header section. Continuous can be made to look like datasheet. However, user cannot manually resize 'columns'.

So take your pick.
 
I have only used Split form twice in very, very small, simple db. I do not really like the form. It has quirks that I find annoying.

But if you are comfortable with its behavior then go for it.

However, you really just need a normal form (not form/subform) in Continuous view with search box in header section. Continuous can be made to look like datasheet. However, user cannot manually resize 'columns'.

That’s what I needed to hear. Thanks!
 

Users who are viewing this thread

Back
Top Bottom