CODEWITHSUNDEEP
ms-access
Logarith
Logarith

Reputation: 710

Checkbox per row in multi user access database

I have the following situation:

I have a many to many relationship.

For example an Employee Table, an Course table, and an relation-table inbetween.

Now I want that the user can select out of the course table his courses in a dialog form.

That means that the underlying table for my continous form inside the dialog is the course table. I want a checkbox in every row which indicates if the course is selected or not. As I read it is not possible, to add an unbound checkbox, because they are just copies, and a select click would select all of them. The solution would be to add an yes/no field to the underlying table courses.

But here I have the problem as I understand the matter, because I have several users using the database at the same time, that the underlying table will be updated if I click one checkbox and this update will select the value for all users which are using the dialog form concurrently, what I dont want.

So my question is, is there another solution to get a working checkbox per row in a multi user access database. There are two solutions which I could imagine:

1) The underlying table will be the relation table and in this every possible combination between employee and course will be saved together with a yes/no field. (but that would be from a data view point quite horrible)

2) If the changes to a checkbox would not be directly written back to the database table, I could discard them on saving and manually insert the relation records in the relation table. (Is that possible?)

Thanks for any solution proposals

Upvotes: 1

Views: 1508

Answers (3)

BitAccesser
BitAccesser

Reputation: 719

Have a look at this How to use unbound checkbox in a Continuous Subform - MS Access. A class that binds an unbound checkbox. Better than listbox, because you have a form with all its benefits (sort, filter, edit, append).

Upvotes: 0

SunKnight0
SunKnight0

Reputation: 3351

Based on your description I assume your form has a LEFT/RIGHT JOIN in its data source where some ID field is null if the specific Course/Employee combination does not exists in your relation table. Let's call it LinkID. Then your checkbox should be something like =NOT ISNULL(LinkID). While you will not be able to use the OnClick event for the user to check/uncheck this way, you can use the onMouseDown event to see if the user clicked the checkbox and take action accordingly.

That way you don't need an "all combinations" relations table, no temporary table and no Yes/No field. If a record with the Course/Employee combination exists the box is checked, if it does not exists, it is not checked. Adding and removing courses is done by adding and deleting records from the relation table.

Upvotes: 0

Andre
Andre

Reputation: 27634

I see two good approaches:

1) This assumes that your database is split in a network backend + each user has a local frontend. This is the recommended setup for multi-user.

The frontend has a local table with Course_ID and a yes/no column.
A join of this local table with the Course table is the recordsource for your continuous form.

On loading, you copy the course ids into the local table, and set the existing relations to True.

On saving, you update the relation table.

2) Use a ListView control instead of a continuous form. It has inbuilt checkboxes. Loading and saving is done with a VBA loop.

Upvotes: 2

Related Questions