How to set a form's Checkbox.Value to True based on a Junction Table

[easyrider]

Hi all. Simplified scenario here. I have 3 tables: UserT, ItemT and UserXItemJT. The latter being a junction table for the many-to-many relationships between Users & Items. I have an unbound form (ViewUserItemsF) that gets the user name & user_id from the calling form (EditUserF). This form also has 10 checkboxes for the items and all of them are named Item1, Item2, etc. The trailing number corresponds to the Item's PK in the ItemT. When I call the form I would like to read the junction table and enable the corresponding checkbox based on the user to show which items are associated with said user.

What would be the best way to accomplish this?

[Bob Fitz]

Hi all. Simplified scenario here. I have 3 tables: UserT, ItemT and UserXItemJT. The latter being a junction table for the many-to-many relationships between Users & Items. I have an unbound form (ViewUserItemsF) that gets the user name & user_id from the calling form (EditUserF). This form also has 10 checkboxes for the items and all of them are named Item1, Item2, etc. The trailing number corresponds to the Item's PK in the ItemT. When I call the form I would like to read the junction table and enable the corresponding checkbox based on the user to show which items are associated with said user.

What would be the best way to accomplish this?

If you have a table with fields named Item1, Item2, etc. then it sounds like the db needs to be "normalized".
Can you post a copy of the db

[davegri]

Another approach would be to use a listbox instead of checkboxes. See

Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

[Gicu]

In the Load or Current event of the ViewUserItemsF from add something like this (adjust for your names). If you want to update the junction table if the user is checking or unchecking the corresponding checkbox you would need to run some delete\appends to remove or add the records.

Dim i as integer, y as integer

For i=1 to 10
	y=dCount("*","UserXItemJT","User='" & Forms!EditUserF!txtUser & "' And [PK]=" & i)
	Me.Controls("Item" & i)=IIF(y>0,True,False)
Next i

Cheers,

[easyrider]

Vlad, that worked perfectly, with one small change. It threw a runtime error, #3464 Data Type Mismatch, in the Dcount assignment so I removed the two single quotes in the expression and viola! I appreciate your clean, efficient code. Thank you!

[easyrider]

Bob, Item1, Item2, etc are not the field names in the junction table. They are the names of the check boxes on the form. Cheers!

[Gicu]

Glad to hear, I usually associate User with some sort of string (text) hence the single quotes but you got it anyway ��!
Cheers,