Need Help in Adding records in the related tables
I think that you have the concept of cascade update and cascade delete a
little bit mixed up. These events are triggered only if the primary key
value of your table changes. In other words if you change your primary key
value in TableA for the record that is ALREADY SAVED OR UPDATED and there is
an existing value in related Table B then cascade update will change all of
the corresponding values in Table B to match the new value in TableA.
However cascade update will not add new records to Table B. For more
information look in help under cascade update and delete.
In your case if you want to use the insert into method then there are
several ways that you can get around your problems. First before calling
the append query, save your current record on the main form. This updates
your main table. You will then be able to update tableB. Secondly I would
not be calling this procedure from the afterinsert event because this event
is triggered only after the new record is saved or updated. So what you may
want to do is call it in AfterUpdate Event of your primary key text box on
your form. The way around triggering INSERT INTO procedure if you are only
updating your primary key with a new value is by checking the NewRecord
property to determine whether the current record is a new record. If the
value is true then run your procedure if not the exit sub.
Probably a better way, if you want more control of when the procedure is run
would be to place a "Add to Related Tables" button on the form that triggers
your procedure only when you want to.
But the key is that your current record is saved before you add records in
the related table. Of course there are other ways....
The only concern that I may have with my reply is that I am not sure if any
locking properties will come into play. I suspect not, but I have not tried
so I can't really say.
Good Luck.
Alan
Quote:
>Hi,
>The problem may be solved by inserting the record in the related table
>using INSERT INTO in the AfterInsert Event. But the problem with
>AfterInsert event is that the record still hasn't been added to parent
>table yet and when I try to insert in the child table, the referential
>integrity doesn't let me insert the record.
>If I put the same INSERT INTO in the Before or AfterUpdate of the parent
>form, then these event will happen not only for the new records but for all
>the changes/updates of all the records. I want the changes/updates to be
>taken care of by referential integrity (cascade update/delete) options. So
>how do I insert a record using INSERT INTO and yet not have the same code
>run when a mere update happens?
>Thanx
>--
>Asim K. Shafi
>This only takes care of the problem when the user enters the record in the
>parent table the first time. What about the subsequent updates that record
>will have.
>> Hi,
>> There might be an easier way but if you want to do this through forms,
>how
>> about making a subform out of Table B and embedding it hidden in Form A
>> based on TableA?? (I'm wondering about this myself as I type so my
>> apologies if this doesn't work)
>> --
>> Just my $.001
>> Dev Ashish
>> ---------------
>> The Access Web ( http://home.att.net/~dashish )
>> ---------------
>> :I have a database that uses a form to enter data in the underlying
>table,
>> :call it table A. Now table A is related to table B which contains the
>same
>> :keys (one-to-one; cascade update & delete). This table B is now related
>to
>> :table C with the same keys (one-to-many; cascade update & delete). The
>> :problem is that when I enter data in the table A, I want the same key
>> :fields to be entered in table B. How can one automate this while
>entering
>> :data in table A's form?
>> :
>> :Thanx
>> :
>> :--
>> :Asim K. Shafi
>> :
