DAO code to write records

[Jean]

Posted this before and someone kindly gave me an SQL
answer. Unfortunately I don't know enough about SQL to
apply that answer. Isa there a DAO alternative?

What I need to do is have the user click a button on the
form. The click event causes a copy of the current record
to be added to the current recordset. This, I can do.
However, the existing record will have child records in a
child table and I need to be able to identify all the
child records and copy them. The foreign key for that
links these child records to the parent record will need
to be the primary key from the newly added parent record.
I need help to copy all child records from the original
table and make sure they have the correct value for the
foreign key.

Hope someone out there is a lot more expert with DAO than
me !

TIA

Jean

 

[Douglas J. Steele]

Whenever you have a choice between using SQL or using DAO, you're almost
always better off using SQL.

Sorry, but since I don't know the solution that was suggested, I can't offer
help on how to use it.

 

[TC]

It is seldom correct to create a new record based on a copy of some other
record in the same table. Say the two records share fields A-K in common
(ie. those fields have identical values), and only fields L-O have different
values (in the two records). This suggests that fields A-K should be in a
single record in a >different< table, and only fields L-O should have a
record each, in the (current) table.

Perhaps read:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC

 

[Tim Ferguson]

The click event causes a copy of the current record
to be added to the current recordset. This, I can do.
However, the existing record will have child records in a
child table and I need to be able to identify all the
child records and copy them.

If the child table has PKOne inherited from its parent table, and PKTwo
which is its own serial number, you can create a parameterised query that
copies from one parent to another (this is not tested but should be close)


PARAMETERS wNewParent INTEGER, wOldParent INTEGER;
INSERT INTO ChildTable (
PKOne, PKTwo, FieldOne, FieldTwo )
SELECT wNewParent, PKTwo, FieldOne, FieldTwo
FROM ChildTable
WHERE PKOne = wOldParent

and then code it something like

Set qdf = Querydefs("CopyChildren")
With qdf
!wOldParent = GetOldPKFromForm()
!wNewParent = GetNewPKFromTable()

.Execute strSQL, dbFailOnError

End With

But I agree that there are not many reasons for wanting to copy a whole
tree of records. Design problem or user quirk?


B Wishes


Tim F