Auto create record for one-to-one relationship tables 
Author Message
 Auto create record for one-to-one relationship tables

I have one main table containing property details. This
has a one-to-one relationship with many other tables
containing things like lease details, health & safety
details etc.

When I add a new record to the property details table, I
want to create a new record in each of the other tables.
These new records would be blank apart from the PropertyID
field that links it to the main table.

How can I do this?

Thanks in advance for your help.

Ian



Sun, 14 Nov 2004 20:11:57 GMT  
 Auto create record for one-to-one relationship tables
Do not really understand why you want to insert
these "blank" records....but

In the form's After Insert event use something like

Dim Db as DAO.Database

set Db = CurrentDB()

Db.execute "INSERT INTO MyLeaseDetails
(MyLeaseDetails.propertyId) VALUES ( " & Chr(34) &
Me.PropertyID & Chr(34) & ");"

Db.execute "INSERT INTO MyHSDetails
(MyLeaseDetails.propertyId) VALUES ( " & Chr(34) &
Me.PropertyID & Chr(34) & ");"

You can omit the Chr(34) & if the property ID datatype is
a number vs a string.

Hope this helps.

Hamlet

Quote:
>-----Original Message-----
>I have one main table containing property details. This
>has a one-to-one relationship with many other tables
>containing things like lease details, health & safety
>details etc.

>When I add a new record to the property details table, I
>want to create a new record in each of the other tables.
>These new records would be blank apart from the
PropertyID
>field that links it to the main table.

>How can I do this?

>Thanks in advance for your help.

>Ian
>.



Mon, 15 Nov 2004 01:55:30 GMT  
 Auto create record for one-to-one relationship tables
Thanks very much for that.

You're comment about the "blank" records has made me think
a bit. I'll still use the code, but I'll only create the
new blank record when the user is ready to edit it.

Cheers,

Ian



Mon, 15 Nov 2004 17:54:21 GMT  
 Auto create record for one-to-one relationship tables
As an alternative, you might want to look at using linked
subforms to enter the new records.  By doing so, Access
will automatically enter the keyfield in the record for
you.

Quote:
>-----Original Message-----
>Thanks very much for that.

>You're comment about the "blank" records has made me
think
>a bit. I'll still use the code, but I'll only create the
>new blank record when the user is ready to edit it.

>Cheers,

>Ian
>.



Tue, 16 Nov 2004 04:09:46 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Adding records with one-to-one relationship

2. Eliminate records of one table based on another table record count

3. Representing a One-to-Many relationship in one row

4. Performance Hit on Access One-to-One Relationships

5. one-to-one relationship with adox

6. Updating the one-side of an one-to-many relationship

7. One to one relationship

8. SQL - Join one table in one destination to another table in another destination (DBF)

9. HELP NEEDED-One-to-many record relationship

10. records transfer from one table to another table

11. Create one table from 10 tables in two files

12. Fast Way to update one field in one record

 

 
Powered by phpBB® Forum Software