adding a record to several tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a main table that I use to hold general information, the primary key
is an auto number. I also have several other tables to enter corresponding
data regarding each record also with an autonumber as the primary key. Each
table has a form for dat entry purposes.

I want to establish a way to add a record to the main table, and have the
corresponding record added to each of the other tables.
I'm trying to establish something along the lines of a parent/child
relationship, but I want the forms opened in seperate windows and not
included as subforms of the main form.

currently, I can add records to the main form. but I cannot add records to
the other forms. the error message I recieve is "cannot add or change a
record because a related record is required in table 'License Information".

Does anyone know how to relate these tables together so that once a new
record is created in the main form, it is also created in the other forms?
 
Access (JET) lacks triggers, so you cannot do this automatically.

If all entries are made through a form, you can use the AfterInsert event of
the form to execute a series of SQL statements to append records to the
related tables as well.

It is fairly unusual to need to do this is several related tables.
(Wondering if the structure is normalized.)
 
Allen Browne said:
Access (JET) lacks triggers, so you cannot do this automatically.

If all entries are made through a form, you can use the AfterInsert event of
the form to execute a series of SQL statements to append records to the
related tables as well.
Click to expand...

all entries for the main table are made through one form, that is correct. I
am not certain how to insert SQL statements to an existing form though. I
assume it would be under the command for add new record. I am also not overly
familar with the language. If you could provide a sample, I would appreciate
it.
It is fairly unusual to need to do this is several related tables.
(Wondering if the structure is normalized.)
Click to expand...

I've run tests on the set-up, and there were no suggestions on how to
combine, or further seperate the tables.
The database is used for tracking documents, and timelines for the various
filings associated with each document. I have a table for each timeline.
Every document does not require the same timelines, however, there is no way
to determine what documents will require specific timelines until the process
has already begun.
I began with all the timelines and documents included in one table, with
queries to pull out pertinant information for reporting purposes, but I
reconsidered this design and created the seperate tables so I could use my
queries for more specific things such as searching for records within a
specified time frame.

again, if you could provide insite into the SLQ language, I would appreciate
it.

Thank you!
 
Q1. Append statement
You can mock up a query by typing literal values into the query design grid,
change it to an Append query (Append on Query menu) in query design, and
then switch to SQL View (View menu). That gives you an example of the kind
of string you need to create.

Then your code would be something like this::
Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT ...
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Q2. Design
By timeline, I assume you mean that a document can pass through a series of
stages, but the actual stages are not known ahead of time.

That would require a table of documents, a table of stages, and a table
recording the actual stage listing for a document:
Document table (one record for ecah document):
DocumentID primary key
...

Stage table (one record for each possible stage):
StageID primary key

DocumentStage table (one record for each stage of each document):
DocStageID primary key
DocStageDate Date/time (when the document reached this stage)
DocumentID which documment
StageID which stage
 
Back
Top