Help with Unbound Form, Two Subforms: Checkbox Selection for Product-Hotel Association

[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)

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 anyones help.

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

Here is a link to the database:


https://1drv.ms/u/s!AjzjUT0wzwHggdx4epMCNu7msF58VQ

Here are screenshots:

[June7]

Is this a split db with multiple simultaneous users?

Many will not download files from external location. Best to attach file to post. See instructions at bottom of my post.

[Odeh Naber]

Hi! Thank you for the reply!
- No, it is not a split database. I will be the only user.
- Ah! I forgot that I could zip the file! File attached.
Thanks!

[Welshgasman]

Crossposted https://www.access-programmers.co.uk...iation.332545/

[Odeh Naber]

Thank you for your message and for guiding me to the guidelines regarding cross-posting in both forums. I understand from the link that I have not broken any rules, and at the same time I appreciate your efforts to maintain a respectful and productive environment in the forum.


I want to clarify that I understand the concerns about cross-posting and how it can lead to confusion for those trying to help. My intention is not to disregard anyone's time or contributions.


In fields like programming and database management, there are often multiple ways to tackle a problem. By gathering insights from various contributors, I aim to implement the best solutions based on the diverse perspectives shared. I genuinely value the volunteer efforts of everyone in this community.

Rest assured, any solutions I receive that work and solve the problem, will be recognised, acknowledged and shared in all forums.

[mike60smart]

Hi
See the re-designed example attached.

[Micron]

I want to clarify that I understand the concerns about cross-posting

Not sure if that means you knew or only just learned. In case you don't know, the problem isn't cross posting, it's cross posting without declaring it and providing a link to the other forum(s). That can get you on anyone's ignore list.

[Odeh Naber]

Not sure if that means you knew or only just learned. In case you don't know, the problem isn't cross posting, it's cross posting without declaring it and providing a link to the other forum(s). That can get you on anyone's ignore list.

Thank you for your response and for sharing your perspective.

I would like to mention that my experience on forums has been positive overall, and this is the first instance where I’ve encountered concerns about cross-posting. My primary goal here is to seek assistance and gather insights to address the challenges I'm facing, and I believe that engaging with different communities can foster diverse solutions.


If declaring cross-posts is a rule in this forum, I will certainly adhere to it moving forward. However, if it is not, then I hope to focus my energy on those who are willing to assist rather than on discussions that may not contribute to the problem-solving process.


Thank you for your understanding, and I look forward to continuing to engage with those who are here to help.

[Odeh Naber]

Hi
See the re-designed example attached.

Hello! Thank you so much for the alternative suggestion; I truly appreciate it. The ability to view all hotels in the second subform is essential for easily identifying which hotels have been assigned (or not) the selected product or supplier. As the hotel group expands, having a visible overview will become even more crucial for effective management. Do you think this is doable in MS Access? Thanks again!

[Odeh Naber]

So, ArnelGP, provided the following solution:

OnCurrent Event on the Products subform:
Me.Parent!txtspid = Nz(Me.SP_ID, 0)
Me.Parent![T_ProductHotel subform].Form.Recalc

On the Hotels subform:
He replaced the checkbox with a textbox containing a Windings character depicting a checkbox:
- Control Source of this textbox: =IIf(DCount("1","T_ProductHotel","BU_ID = " & Nz([BU_ID],0) & " And SP_ID = " & [Forms]![F_NEW]![TXTSPID])<>0,"þ","o")
- OnClick event of this textbox:
Private Sub Text10_Click()
If IsNull(Me.BU_ID) Or (Me.Parent!txtspid = 0) Then
Exit Sub
End If
With CurrentDb
If DCount("1", "T_ProductHotel", "BU_ID = " & Nz([BU_ID], 0) & " And SP_ID = " & [Forms]![F_NEW]![txtspid]) <> 0 Then
.Execute "DELETE * FROM T_ProductHotel WHERE BU_ID = " & Nz([BU_ID], 0) & " And SP_ID = " & [Forms]![F_NEW]![txtspid] & ";"

Else
.Execute "INSERT INTO T_ProductHotel (BU_ID, SP_ID) " & _
"SELECT TOP 1 " & Nz([BU_ID], 0) & ", " & [Forms]![F_NEW]![txtspid] & " FROM T_ProductHotel;"
End If
End With
Me.Refresh
With Me.BU_Name
.SetFocus
.SelLength = 0
End With
End Sub

This 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. I am currently looking into how to resolve this final detail. Thank you again!

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] & ");"

[mike60smart]

Hi
In my example when you select a specific Supplier Record you are able to see all associated Hotels allocated.
Is this not what you originally asked for?

[Odeh Naber]

Hi
In my example when you select a specific Supplier Record you are able to see all associated Hotels allocated.
Is this not what you originally asked for?

Hi Mike!
Upon rereading my initial post, I realise my wording may have caused some confusion. I apologise for this and will take greater care in the future. My intention was to be able to view all hotels in the 2nd subform (those listed in T_BusinessUnits), along with whether the checkbox is ticked or unticked for each hotel, when selecting a specific supplier record. I've clicked on the star in your posts to add to your reputation, as I appreciate your time and help!
Thank you!

[Odeh Naber]

Thank you to everyone who contributed to the solution! I really appreciate it! It’s great to have a place where people are so willing to share their knowledge. Thanks again!

The final solution, provided by ArnelGP:

OnCurrent Event on the Products subform:
Me.Parent!txtspid = Nz(Me.SP_ID, 0)
Me.Parent![T_ProductHotel subform].Form.Recalc

On the Hotels subform:
Replaced the checkbox with a textbox containing a Windings character depicting a checkbox (checked and unchecked):
- Control Source of this textbox: =IIf(DCount("1","T_ProductHotel","BU_ID = " & Nz([BU_ID],0) & " And SP_ID = " & [Forms]![F_NEW]![TXTSPID])<>0,"þ","o")
- OnClick event of this textbox:
Private Sub Text10_Click()
Me.Text10.SelLength = 0
If IsNull(Me.BU_ID) Or (Me.Parent!txtspid = 0) Then
Exit Sub
End If
With CurrentDb
If DCount("1", "T_ProductHotel", "BU_ID = " & Nz([BU_ID], 0) & " And SP_ID = " & [Forms]![F_NEW]![txtspid]) <> 0 Then
.Execute "DELETE * FROM T_ProductHotel WHERE BU_ID = " & Nz([BU_ID], 0) & " And SP_ID = " & [Forms]![F_NEW]![txtspid] & ";"

Else
.Execute "INSERT INTO T_ProductHotel (BU_ID, SP_ID) " & _
"VALUES ( " & Nz([BU_ID], 0) & ", " & [Forms]![F_NEW]![txtspid] & ");"
End If
End With
Me.Refresh
With Me.BU_Name
.SetFocus
.SelLength = 0
End With
End Sub

[Welshgasman]

Thanks for that.