Well I'm going start pretty much from scratch
[although you may not need some of what I'll hit on, others might so please bear with me,
and since I will use SQL in the macro it is good to see what is really being automated]
..and make a small change to your question.
Finally - I'll attach the database, with the macros embedded and the form to this post.
( Note I will be using the 3.1.1 version of OO.o for this example.)
Create a new Base file (embedded database) named TimeKeeper.odb
It has one table, Times3 with the fields
ID, DT1, DT2, DT3
- Table editor
Let's look at some basic SQL statements to insert and update the data
I can insert a record into the table by using an INSERT SQL statment.
To do this I open the SQL window from the Tools>SQL menu and enter the command:
INSERT INTO "Times3" ("DT1")
VALUES( CURRENT_TIMESTAMP)
- Insert record 0
Notice that in 3.1.1 the SQL window supports syntax highlighting for the SQL statement.
CURRENT_TIMESTAMP is a macro buit into the HSQLdb databse engine ( it is standard on most RDBMS )
and will return the date & time according to the system the database is running on.
For an embedded database this is of course the PC, but it would work just as well with MySQL
and would be the clock on the server.
Since this is the first record written to the table I know that the value in ID is 0.
(but after this I should not make an assumption about an AUTO_INCREMENT value again)
Opening the data view for the table (double click the table name Times) I can see this.
- Table data view after INSERT statment for record 0
Now back in the SQL window I will update the fields DT2 and DT3. I do this by entering standard SQL UPDATE commands.
UPDATE "Times3"
SET "DT2" = CURRENT_TIMESTAMP
WHERE "ID" = 0
Followed by
UPDATE "Times3"
SET "DT3" = CURRENT_TIMESTAMP
WHERE "ID" = 0
Great - at this point I want to point out that the changes to the record are NOT reflected in the table's data view window - Why?
The data view, and any form attached to this table, got a snapshot of the data when the record set was retrieved for display.
In order to see the changes to the actual data then you will need to
reload the record set - click the "Refresh" tool button.
Alright - now at this point I think to myself..in my table definition I set the field DT1 as required.
ID is also required but it is auto generated for me whenever a new record is entered. Could I do that for the DT1 field also?
The answer is yes. DO NOT be followed into using the "Default Value" text box back on the table design editor
- that property is for static strings only, and is only used by form controls. What I want is a vlaue written to field DT1
by the data engine anytime a new record is inserted and I want it to be the current date and time for when that happens.
Again I can do this with a standard SQL command, in the SQL window - the ALTER TABLE command.
ALTER TABLE "Times3" ALTER COLUMN "DT1"
SET DEFAULT CURRENT_TIMESTAMP
Excellent - now if I want to insert a new record via SQL I need not worry about the DT1 field rather I use a statement like this:
INSERT INTO "Times3" ( "ID" )
VALUES ( NULL )
Since the ID field is an Auto_Increment field it is a special case when a NULL is in the INSERT statement
- it triggers the data engine to get the next value for this field
- and that triggers the DEFAULT setting on the DT1 field.
(see next post)