Adding a record in one table, auto adds one in another table

[Pony]

I have not found the answer to this yet in any tutorials or posts, that I have searched on the forums.

I have a table of people and addresses. I also have a table of notes, so I can make notes about a person at different times. I even have the notes to where anytime one is added about a person it automatically puts in a time stamp with the note.

I want to make it so if I add a record in the people table it will automatically make the first note in the notes table saying something like "added" so there is a note with a time stamp of when the person was first put in to the database.

[DACM]

...if I add a record in the people table it will automatically make the first note in the notes table saying something like "added" so there is a note with a time stamp of when the person was first put in to the database.

(1) Probably best to add the timestamp field ("added") to the "people" table, complete with the default you mentioned:

• Code: Select all

  ALTER TABLE "people" ADD COLUMN "created" TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP;

(2) But if you really want a note in order to trigger the default timestamp in your "notes" table, then you have a few options:

• Option A: Use a back-end database engine that supports SQL procedures so you can add a Trigger to accomplish your objective at the table level (universal solution; doesn't require form-based data-entry, etc).
  
  Option B: Use a Form with SubForm. Assuming table and field names as follows: Use the Form wizard to create a Form with the MainForm based on the "people" table and a SubForm based on the "notes" table, linked by common "people_ID" from both tables. Add a Push Button to the SubForm and set the 'Action' property to 'Save record.' Change the SubForm's 'Navigation Bar' property to 'Parent Form.' Now when you add a person to the "people" table using this Form, the push-button will remain dimmed until you add a note (type in "added" manually; a default Text Box value will not eliminate the need to add at least an an additional character to the note manually). The push-button will then come to life (because the note has been modified). Pressing the push-button then saves the SubForm and MainForm records to their respective tables, and displays the applicable timestamp.
  
  Option C: You can automate the note entry and eliminate the push-button in 'Option B' using a macro, but that's not so easy to implement. (See the macro-enhanced: Notes.odb)

...

[Pony]

Thanks for your very well put reply. As you so aptly pointed out doing what really want to comes up against limitations in what I am using. it will be easier to do it manually or have the time stamp in the person table.

I was thinking something like a sql statement with a Trigger might be where I should look. So I will ask this (a aria I'm really foggy in) when you say a back-end database engine, you mean like MySQL, or MS Access ?

Thank you for your time in explaining to me what I was really up against on this issue.

[DACM]

I assume by "manually" you mean Option B as demonstrated in the example download above...?

Yes, it would certainly be easiest to implement and retrieve your new-record timestamp within the "person" table. But if that's not how you prefer to manage your "notes" data, then other options become more appealing.

I was thinking something like a sql statement with a Trigger might be where I should look. So I will ask this (a aria I'm really foggy in) when you say a back-end database engine, you mean like MySQL, or MS Access ?

Yes...and not necessarily. Base is only a front-end bundled with a few back-end database options. You can read more about that here and here. The engine link I gave you includes a portable database engine (Base template with HSQLDB 2.x.x engine) that supports SQL procedures & Triggers. In fact, it's simply an upgrade to the 'HSQL database engine' you're currently using. In theory, even your current engine (HSQLDB 1.8.0.10) supports Triggers to external Java code which could then be used to issue SQL back to the engine using the engine's built-in SqlTool class; but I think you would have to run another instance of the engine or employ it in server-mode, so I doubt anyone has ever tried this patchwork approach. Just move on to the newer engine (2.x.x) to get real SQL stored procedures, if you go this route at all.

MySQL is not as easy or as portable as this Base+HSQLDB solution. MS Access is often a much better choice than Base, but you'll need to weigh the pros and cons.

And macros can do virtually anything a Trigger can do (since both can dispatch SQL and analyze results), but a macro is limited to the Base front-end or an individual Form. That can be good or bad.