Access continuous subform with checkboxes to store ticked values with mainforms id

Question

I am a newbie to MS Access and trying make a productivity tracking program for where I work.

I have a main form that's bound to multiple subforms with ID and I need to add one more subform.

That subform is going to fetch all data from Personnel table in continuous form view, and I would like to add one checkbox and two more fields to each row in continuous form. The ticked rows needs to be stored in a different table.

Is there a way to do this?

Answer 1

You'll have to make the new table

Then make a query which relates the new table to the old and returns all of the values you want edited or displayed in your subform.

Make the subform based on this new query, since it is only 2 tables it should be editable if the join isn't to complicated.

Then using the Locked and Enabled Properties of the controls on your subform you can change what is editable and what isn't.

I will say that if this is a one to one relation between the new table and the eixisting table it would be much easier to just add the fields and deal with security/data reporting concerns elsewhere.

Attempt to clarify more

I am assuming your Personnel Table has a foreign key to the main table ID and a personnelID of its own. To have more fields that correspond to records in the Personnel table you need to create another table, we will call it CheckBoxes. Checkboxes needs to have a foreign key to the personnelID and then whatever fields and check boxes you require.

Then you need to make a Query that pulls from Personnel and CheckBoxes and joins them on the foreign key you have relating the two tables. Then make your continuous subform based on the query rather than a table.

Access makes the query creation really easy and this relaition should be simple enough to be able to edit through the query.

Again I would consider adding these fields to the personnel table rather than making your database more confusing than it has to be but that is up to you the designer.