Solved Unbound Form, Two Subforms: Checkbox Selection for Product-Hotel Association (1 Viewer)

[Odeh Naber]

Hello everyone!

I'm working on an MS Access form setup where I have two unbound subforms and need some help with the following functionality:

• Subform 1: Lists products from the T_Products table in a continuous form.
• Subform 2: Lists hotels from the T_Hotels table, each with a checkbox.

(the reason I have it set up like this is because I will have a search as you type textbox in the main form to search the products when the list gets big)
(also, more hotels could be added in the future so as I add them in a separate form, the new ones should automatically appear in Subform 2)

What I want to achieve:

1. When I click a product in Subform 1, Subform 2 should display all hotels with unchecked checkboxes (initially as I have not yet associated any products with hotels).
2. I should then be able to check hotels to associate them with the selected product.
3. When I select another product and then come back to the first one, the checkboxes in Subform 2 should reflect the saved selections (i.e., the hotels previously checked should stay checked).

The idea is that the product list shows is all products available in the market, and the suppliers who sell them, and I want to assign them to the hotels of our hotel group that are authorized to buy them. Each product can have none, one or many hotels assigned to them.

I think I am on the right track. I believe I am supposed to place some SQL code in the products form on the OnCurrent event property, and in the OnClick on the checkbox to update the junction table T_Assignments. But I am struggling to get anything to work. I would appreciate anyone's help.

I am on Windows 11 + Office 365. Thank you in advance.

 

[arnelgp]

this is an alternative without the check mark.
see form F_NEW. Also see table T_ProductHotel.

 

[Gasman]

Cross posted https://www.accessforums.net/showthread.php?t=90347

 

[Odeh Naber]

this is an alternative without the check mark.
see form F_NEW. Also see table T_ProductHotel.

Hello! Thank you for the alternative suggestion. The reason I need to view all hotels in the second subform is to easily identify on-screen which hotels have been assigned (or not) the selected product or supplier. This will become increasingly important as the hotel group continues to expand.

 

[arnelgp]

another alternative, see F_NEW form.

 

[Odeh Naber]

another alternative, see F_NEW form.

Thank you so much for the alternative! It worked perfectly for the behavior I was seeking! However, I've encountered a little issue: after clearing all the checkboxes to "start from scratch", I can no longer check any of them again. Any insights on how to resolve this? Thank you again!

**Edit**
I tried replacing this line in the OnClick event property, and it appears to have resolved the issue. Would this be a suitable and reliable solution?:

.Execute "INSERT INTO T_ProductHotel (BU_ID, SP_ID) " & "SELECT TOP 1 " & Nz([BU_ID], 0) & ", " & [Forms]![F_NEW]![txtspid] & " FROM T_ProductHotel;"

with this:

.Execute "INSERT INTO T_ProductHotel (BU_ID, SP_ID) VALUES (" & Nz([BU_ID], 0) & ", " & [Forms]![F_NEW]![txtspid] & ");"

 

[arnelgp]

sorry, here again.

 

[Odeh Naber]

sorry, here again.

Fantastic! Thanks for the solution! I really appreciate it. It’s great to have a place where people are so willing to share their knowledge. Thanks again!