Show and edit link table data using checkboxes

[Dan Neely]

I have a form showing data on a given record. Each record is
potentially linked to zero or more values in a lookup table. I'd like
to be able to display the data in the link table by using checkboxes
to show if a link exists and to create/delete records in the link
table when the values of the checkboxes are changed.

 

[OldPro]

I have a form showing data on a given record. Each record is
potentially linked to zero or more values in a lookup table. I'd like
to be able to display the data in the link table by using checkboxes
to show if a link exists and to create/delete records in the link
table when the values of the checkboxes are changed.

There are a couple of ways you could do this. One way would be to add
a continuous forms subform to your edit page. It would show all
possible links with only the appropriate links checked. Another would
be to show only those links which are related to the current record in
a listbox, and supply an "Add" button next to a combobox lookup field
(for adding more) and a "Remove" button for removing the selected item.

 

[Dan Neely]

There are a couple of ways you could do this. One way would be to add
a continuous forms subform to your edit page. It would show all
possible links with only the appropriate links checked.

This sounds somewhat like what I want to do, but I don't know how to
set up the databinding needed to check the boxes based on if a record
exists and to do the add/delete operations when the state changes.

Another would
be to show only those links which are related to the current record in
a listbox, and supply an "Add" button next to a combobox lookup field
(for adding more) and a "Remove" button for removing the selected item.

I have a datagrid subform bound to the link table and using linked
fields to filter the values at present. Aesthetically I dislike this
suggestion almost as much as what I currently have.

 

[OldPro]

This sounds somewhat like what I want to do, but I don't know how to
set up the databinding needed to check the boxes based on if a record
exists and to do the add/delete operations when the state changes.

That does sound complicated. You want all of the records of the
second table whether or not they are directly linked to the first
table, so you will need to use a RIGHT JOIN. The key field in the
first table will be NULL on every record that exists in the second
table that is not linked to a key on the first table. You will need
to add a user-defined field to show TRUE if the key field is not NULL,
and FALSE if it is NULL. The user-defined field will have to be
linked to the checkbox. The SQL statement would look something like
this:

SELECT iif([KeyField]=NULL,TRUE,FALSE) AS [Selected], [Field1],
[Field2) FROM Table1 RIGHT JOIN Table2 ON Table1.[KeyField]=Table2.
[KeyField];

In the click event of the checkbox on the actual form that feeds the
continuous form, you would put code to remove or add the record in the
first table that links to the second table.