The Linux Page

Editing of a MS-Access table

Whenever you want the end user to edit a table, you create a form that shows a set of cells. These are defined in your form Details area. Up to here, nothing special about that.

The fact is you cannot easilly access rows individually. That is, the TextBox, ComboBox, etc. in each row is considered the same whatever the row. So if you change the background color of a TextBox, all the TextBoxes of that column will be highlighted, not just the current row.

This is the same for all the parameters, including the content except if the cell is connected to a table using the Data / Control Source Field in the properties of your MS-Access editor.

There are ways to bypass that problem, but that is quite complicated, is slow and require high level skills to understand the code (it tweaks the boxes with direct MS UI calls! What I'd do in C/C++.) I guess a good way would be to write an ActiveX module, but then you'd have to rewrite all the existing functionality of these forms...

In my case, the reason I'm writing here, I had a field with Notes. Because the notes can be quite long I wanted to display a button so users can click to edit. However, in the table itself they cannot see the notes at all. That's fine to a point, the problem is they also don't know whether there are notes. To fix that problem I added a CheckBox next to the edit button. That CheckBox is handled "by hand" so each time someone edits the notes, I check to determine whether the CheckBox should be checked or unchecked.

The result? The entire column of checkboxes would go checked or unchecked as I change ONE Notes field.

How to fix that? I had to add a Yes/No column in my table with the value attached to the checkbox. That works great. Once you add Note to a row, that one row shows the checkbox, not the others.