How to add a record at 2 or more tables at the same time?

  • Thread starter Thread starter Harry Stegeman
  • Start date Start date
H

Harry Stegeman

Hello all,

I have a question about an acces database.
In my example database I have 3 tables,

- MLFam
- MLExample
- MLExample2

I did make the next relations:

MLFam - MLExample (1 - 8)
MLExample - MLExample2 (1 - 1)

My question is, when I add a record to the table MLExample,
Is it possible to add automatic an record to table MLExample2?

With Regards,

Harry Stegeman
 
Harry Stegeman said:
Hello all,

I have a question about an acces database.
In my example database I have 3 tables,

- MLFam
- MLExample
- MLExample2

I did make the next relations:

MLFam - MLExample (1 - 8)
MLExample - MLExample2 (1 - 1)

My question is, when I add a record to the table MLExample,
Is it possible to add automatic an record to table MLExample2?

With Regards,

Harry Stegeman
Click to expand...

You could probably do this via code behind a form, but what would be the
point? If there's always going to be a record in MLExample2 for every
record in MLExample, why would this be a separate table? Why wouldn't
its fields all be in MLExample?
 
Hello Dirk,

Thank you very much for your answer.

You could probably do this via code behind a form, but what would be the
point? If there's always going to be a record in MLExample2 for every
record in MLExample, why would this be a separate table? Why wouldn't
its fields all be in MLExample?
Click to expand...

The reason that I am not changing the tables is that I am making use
of a third party database, I can't change the content of the tables.
If I am changing the tables, the code in the dll (from Primotiv)
won't work anymore.

For your information:
==============
I am making use of a database which has a connection to the
cad program Microstation (www.bentley.com).
A third party did make an application for Microstation,
called "Bouwmenu" (www.primotiv.nl)

I want to change the database inside MSaccess 2000,
on the moment Primotiv does change the database with help
of a their dll., but does function are giving many problem,
I am getting all kind of errors.
To get ride of those problems I want to manipulate
the database inside MsAccess 2000.

You could probably do this via code behind a form.
Click to expand...
Do you know some vba code how I can do it.
I have some experience with vba, see my website
(www.h-stegeman.demon.nl) download mvba.

With Regards,

Harry Stegeman





that is made
 
Harry Stegeman said:
Hello Dirk,

Thank you very much for your answer.



The reason that I am not changing the tables is that I am making use
of a third party database, I can't change the content of the tables.
If I am changing the tables, the code in the dll (from Primotiv)
won't work anymore.

For your information:
==============
I am making use of a database which has a connection to the
cad program Microstation (www.bentley.com).
A third party did make an application for Microstation,
called "Bouwmenu" (www.primotiv.nl)

I want to change the database inside MSaccess 2000,
on the moment Primotiv does change the database with help
of a their dll., but does function are giving many problem,
I am getting all kind of errors.
To get ride of those problems I want to manipulate
the database inside MsAccess 2000.


Do you know some vba code how I can do it.
I have some experience with vba, see my website
(www.h-stegeman.demon.nl) download mvba.
Click to expand...

The trick would be to work with a recordset that is opened from a query
that joins the tables on the related key fields. This query must
include the key field from the table that is considered
"primary" in the relationship. (Note: which table this is may depend on
exactly how you created the relationship.) Then, when you add a record
via the recordset (or via a form based on this query), use code to
ensure that at least one field from the secondary table has been
assigned a value, even if that value is Null.

For example, suppose you have (for some reason) tables

tblFamilies
FamilyID: autonumber primary key
FamilyName: text

tblFamilyAddresses
FamilyID: long integer primary key
FamilyAddress: text

with a one-to-one relationship between them on FamilyID, with
tblFamilies established as primary. Then you could have a form with
this SQL statement as its recordsource:

SELECT
[tblFamilies].[FamilyID],
[tblFamilies].[FamilyName],
[tblFamilyAddresses].[FamilyAddress]
FROM
[tblFamilies] LEFT JOIN [tblFamilyAddresses]
ON [tblFamilies].[FamilyID] =
[tblFamilyAddresses].[FamilyID];

This form can have controls on it bound to FamilyID and FamilyName (in
tblFamilies) and to FamilyAddress (in tblFamilyAddresses). You can
update both tables by entering data in FamilyName and in FamilyAddress.
If you want to force a record to be created in tblFamilyAddress even if
the user doesn't enter anything in FamilyAddress, you could have code in
the form's BeforeUpdate event like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
If IsNull(Me.FamilyAddress) Then Me.FamilyAddress = Null
End If

End Sub

By dirtying FamilyAddress, you force Access to create this record, even
though the field is Null.

You could do the same thing to create the records programmatically by
opening a recordset on the query and doing something similar using the
DAO recordset methods.
 
Hello Dirk,

I did look at your example, if I make a simple Form with
2 tables your solution will work, but my database in much
more complex

My database has several tables,
the table MLKoppel (ID autonumber) has 1 on 1 connection to (MLKoppelID,
numeric (no duplicates)) to the next tables:
MLBoundary
MLStartCap
MLEndCap
MLJoints

the table MLKoppel (ID autonumber) has a 1 on 8 connection to (MLKoppelID,
numeric (duplicates or OK))
MLLine

the table MLKoppel (MLFamID, numeric) has a 8 on 1 connection to (ID,
autonumber)
MLFam

In levels MLFam is on top, MLKoppel are children of MLFam
MLLine are children of MLKoppel.

If I am making a query where all the tables are included, and I am using
that
query to make a Form, the form has one level.
If I am making a form by selecting the tables MLFam, MLKoppel and MLLine I
am
getting a form with 3 levels, 1 form with 2 subforms.

I don't know how I can use your solution if the form have more levels,
do you know a solution?

You could do the same thing to create the records programmatically by
opening a recordset on the query and doing something similar using the
DAO recordset methods.
Click to expand...
Can you give my an example how I can use the DAO recordset methods?

With Regards,

Harry Stegeman

Dirk Goldgar said:
Harry Stegeman said:
Hello Dirk,

Thank you very much for your answer.



The reason that I am not changing the tables is that I am making use
of a third party database, I can't change the content of the tables.
If I am changing the tables, the code in the dll (from Primotiv)
won't work anymore.

For your information:
==============
I am making use of a database which has a connection to the
cad program Microstation (www.bentley.com).
A third party did make an application for Microstation,
called "Bouwmenu" (www.primotiv.nl)

I want to change the database inside MSaccess 2000,
on the moment Primotiv does change the database with help
of a their dll., but does function are giving many problem,
I am getting all kind of errors.
To get ride of those problems I want to manipulate
the database inside MsAccess 2000.


Do you know some vba code how I can do it.
I have some experience with vba, see my website
(www.h-stegeman.demon.nl) download mvba.
Click to expand...

The trick would be to work with a recordset that is opened from a query
that joins the tables on the related key fields. This query must
include the key field from the table that is considered
"primary" in the relationship. (Note: which table this is may depend on
exactly how you created the relationship.) Then, when you add a record
via the recordset (or via a form based on this query), use code to
ensure that at least one field from the secondary table has been
assigned a value, even if that value is Null.

For example, suppose you have (for some reason) tables

tblFamilies
FamilyID: autonumber primary key
FamilyName: text

tblFamilyAddresses
FamilyID: long integer primary key
FamilyAddress: text

with a one-to-one relationship between them on FamilyID, with
tblFamilies established as primary. Then you could have a form with
this SQL statement as its recordsource:

SELECT
[tblFamilies].[FamilyID],
[tblFamilies].[FamilyName],
[tblFamilyAddresses].[FamilyAddress]
FROM
[tblFamilies] LEFT JOIN [tblFamilyAddresses]
ON [tblFamilies].[FamilyID] =
[tblFamilyAddresses].[FamilyID];

This form can have controls on it bound to FamilyID and FamilyName (in
tblFamilies) and to FamilyAddress (in tblFamilyAddresses). You can
update both tables by entering data in FamilyName and in FamilyAddress.
If you want to force a record to be created in tblFamilyAddress even if
the user doesn't enter anything in FamilyAddress, you could have code in
the form's BeforeUpdate event like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
If IsNull(Me.FamilyAddress) Then Me.FamilyAddress = Null
End If

End Sub

By dirtying FamilyAddress, you force Access to create this record, even
though the field is Null.

You could do the same thing to create the records programmatically by
opening a recordset on the query and doing something similar using the
DAO recordset methods.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top