CODEWITHSUNDEEP
ms-accessms-access-2007
Andrea Sindico
Andrea Sindico

Reputation: 7440

Access: How to automatically create a record in related tables

I have an access db with two tables (i.e. Ta, Tb) where records of Tb relate to records of Ta. When a new record in Ta is created I need to automatically create a new empty record in Tb which relates to the new Ta record. What is the easiest way to do this?

Thank you

Upvotes: -1

Views: 4453

Answers (3)

Chris
Chris

Reputation: 1

From Ribbon:

  1. Create >> Queries >> Query Design

Include the following as query fields

1.1 The uniquely-related field (e.g., UniqueID) from both Ta and Tb.

1.2 At least one other field from each of Ta and Tb (e.g., FieldX_Ta, FieldY_Tb)

1.3 Save (e.g., QueryA).

So there are at least 4 query fields in QueryA now (UniqueID from Ta, UniqueID from Tb, FieldX_Ta1, FieldY_Tb).

Selecting QueryA at Navigation Pane,

  1. Create >> Forms >> Form

2.1 Set default value, for both FieldX_Ta and FieldY_Tb.

2.2 Design as desired and Save (e.g., Form_I).

  1. Opening From_I @Form View

3.1 Go to "New records".

3.2 Fill in forms. Once Save, both Ta and Tb should have new records related via UniqueID.

[I'm using MS Access Version 2304 (Build 16327.20214)].

Upvotes: 0

Roger
Roger

Reputation: 21

Access doesnt have stored procedures but you can use the

After_Insert. event then run an insert query.

populate the insert query with a select from the master Ta to get the max id so you can put that in Tb

Upvotes: 0

Matt Hulse
Matt Hulse

Reputation: 6242

Generally you would accomplish something like this by using database triggers. Unfortunately, I don't believe MS Access supports triggers. If you have the ability to use MS SQL, you would then be able to use triggers.

I found this post that talks about some alternative methods of accomplishing what you're looking for.

Upvotes: 0

Related Questions