Logging date on check box

[sqw68]

I've got a number of checkboxes that are ticked as an when certain tasks are done.

Is there a way to easily record the date when these were ticked, without having to add adate field to the form?
Or will I need the date field?

[Nocton]

You could write a bit of code to do an SQL update of a date field when the check box is ticked. It's easy, but you may not have written code before?

[sqw68]

No, Sadly never written code.
I'll just build a date field into the table rather than a tick box.

[Villeroy]

The attached document is a demo on default values with a default date in the "Data" table and a time stamp in the related "Check" table. This is a one-to-one relation where every record in "Data" may have one or zero records in table "Check".

How I added the default date in "Data" using Tools>SQL... (not possible in the graphical user interface):

Code: Select all

ALTER TABLE "Data" ALTER COLUMN "Date" SET DEFAULT CURRENT_DATE;

How I added the time stamp

Code: Select all

ALTER TABLE "Check" ALTER COLUMN "TS" SET DEFAULT CURRENT_TIMESTAMP;

How I linked the two tables by adding a foreign key to the "Check" table (can be very tricky to do in the GUI):

Code: Select all

ALTER TABLE "Check" ADD FOREIGN KEY ("ID") REFERENCES "Data" ("ID");

The form:
The subform shows the related "Check" record for the selected "Data" record in the parent form.
The forms are linked by their common ID fields, the time stamp controls are read-only, the save button marked as default button (reacts on Enter key).
When you check the box of an empty "Check" record and either push the save button or move back to the parent form, then you store a new record in the "Check" table which marks the related "Data" record as checked. The new record gets an automatic time stamp.
You can uncheck a record either by unchecking the boolean field (which keeps the original time stamp) or by deleting the "Check" record alltogether.

The default date in the "Data" form is today's date if the user does not enter any other date.

Unfortunately, I don't know any way to show this one-to-one relation in a single grid AND keep the grid editable. You can merge both tables in a read-only query as shown by query "qMerge".