Automatically Inserting Records within an Access table

[Andrew]

Hello All,

Can someone help me?

I have 3 tables in an ACCESS 2007 database, SUBJECTS (the parent table),
TRACKING (the main table with most of the data), and PAGES.

SUBJECTS contains all records with subject information sorted using SubjectID.
PAGES consists of static data of 10 records that will never change.
TRACKING consists of all the data for all SUBJECTS (linked and in a
one-to-many relationship with SUBJECTS via SubjectID) with all associated
PAGES.
For example, if a Subject is entered into SUBJECTS, I want the TRACKING
table to pre-populate 10 new records (from PAGES) associated with that
SubjectID. Then when another subject is added to SUBJECTS, another 10 records
and so on.

I know this needs to be pull data via query, but I am at a loss as to how to
do it.

Can someone suggest the correct code to use?
I would very much appreciate it.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access does not automatically add data to a table when another table is
changed. You'll have to write a VBA routine to do this.

Create a form for the Subjects info. Use the AfterUpdate event of the
form to run the query that adds data to the Tracking table. When the
Subjects info is saved (by moving to another record, closing the form,
or hitting Shift-Enter) the form's AfterUpdate event automatically runs.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSTg3NIechKqOuFEgEQLxHgCgsKWGwsFAx2Pgn9X48XFdTI+8NHYAoOl+
H9DmwqlSoxUeedz8lEEBVFwC
=q65n
-----END PGP SIGNATURE-----

 

[Andrew]

Awesome...that is where I was headed next...

Would this sequel code work within the SUBJECTS VBA code?
--
Private Sub Form_AfterUpdate()
Insert into TRACKING (Visit, PageNum, PageDesc)
Select Visit, PageNum, PageDesc
From CRF
Where PageNum < 1000
End Sub
--

The records (10 of them) are numbered through PageNum and are static. The
last numbered record is 999.

Thank You.

Andrew

 

[John W. Vinson]

For example, if a Subject is entered into SUBJECTS, I want the TRACKING
table to pre-populate 10 new records (from PAGES) associated with that
SubjectID. Then when another subject is added to SUBJECTS, another 10 records
and so on.

Why?

Adding empty "placeholder" records to a table is almost NEVER necessary. What
are you going to do with these records once you have them?

If you just want to *see* each Subject's information together with each row of
Tracking, then you can use a "Cartesian Query" - add SUBJECTS and TRACKING to
the query design grid with *no* join line. You'll see all possible
combinations.

If you have a really good reason to store worthless data, then you can change
this query to an APPEND query and append it to PAGES.

 

[Andrew]

You are exactly right. These ARE place holder records to be used to verify
SUBJECT casebook pages coming in house. There are other fields within each
TRACKING record in order to assign statuses etc...

The key here is to have these placeholders in place so when the record
arrives in house, their status is assigned. Statuses can be "Not Expected",
"Received" etc...

 

[John W. Vinson]

You are exactly right. These ARE place holder records to be used to verify
SUBJECT casebook pages coming in house. There are other fields within each
TRACKING record in order to assign statuses etc...

The key here is to have these placeholders in place so when the record
arrives in house, their status is assigned. Statuses can be "Not Expected",
"Received" etc...

So every page has every possible status from the beginning!?

In my experience placeholder records entered with the intent of editing them
later... often never get edited. If you need to assign status, why not just
assign the status?

 

[David W. Fenton]

In my experience placeholder records entered with the intent of
editing them later... often never get edited.

It's easy enough to do a Cartesian join, present that to the user
and allow it to be editable so such that no record is created until
the many side of the join is edited. I've done it, but it's been so
long I forget the exact trick to make it work. It might be an outer
join rather than a cartesian product, now that I think of it. If
anyone's interested, I'll look it up.

The bottom line: with this method, it *looks* like all the records
are already there, but they aren't until you actually edit them.

 

[John W. Vinson]

It's easy enough to do a Cartesian join, present that to the user
and allow it to be editable so such that no record is created until
the many side of the join is edited. I've done it, but it's been so
long I forget the exact trick to make it work. It might be an outer
join rather than a cartesian product, now that I think of it. If
anyone's interested, I'll look it up.

The bottom line: with this method, it *looks* like all the records
are already there, but they aren't until you actually edit them.

I think I've done that once or twice, a long time ago... IIRC it was indeed an
Outer Join. I'll be interested to see what you find!

 

[Andrew]

Yeah. Every record will have a status attached later on.

As these casebook pages come in, they will be assigned either "Received",
"Blank", or "Not Expected", as well as tracking information about them (who
sent them in, who received them in house, etc...)

I need to create the placeholders so that they are available to the user for
assigning statuses once a subject has been created and the pages come in.

I'm trying to write the code in VBA in the AfterUpdate() event, but am
running into an issue with the syntax. It keeps giving me an "Expected: end
of statement error", code as follows...

Private Sub Form_AfterUpdate()

INSERT INTO CRFTrackTrans (Visit, PageNum, PageDesc)
SELECT Visit, PageNum, PageDesc FROM CRF

End Sub

 

[John W. Vinson]

Yeah. Every record will have a status attached later on.

As these casebook pages come in, they will be assigned either "Received",
"Blank", or "Not Expected", as well as tracking information about them (who
sent them in, who received them in house, etc...)

I need to create the placeholders so that they are available to the user for
assigning statuses once a subject has been created and the pages come in.

I'm trying to write the code in VBA in the AfterUpdate() event, but am
running into an issue with the syntax. It keeps giving me an "Expected: end
of statement error", code as follows...

Private Sub Form_AfterUpdate()

INSERT INTO CRFTrackTrans (Visit, PageNum, PageDesc)
SELECT Visit, PageNum, PageDesc FROM CRF

End Sub

VBA is one language; SQL is a different language. You're inserting a valid SQL
query into a VBA procedure - it won't work, es ist als ich plötzlich in
Deutesch schreib.

Create and save an Append query to append the data you want, and run the query
from your event using the Execute method:

Private Sub Form_AfterUpdate
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("MyAppendQueryName")
qd.Execute dbFailOnError
Proc_Exit:
Exit Sub
Proc_Error:
<do your error handling>
Resume Proc_Exit
End Sub

 

[David W. Fenton]

I think I've done that once or twice, a long time ago... IIRC it
was indeed an Outer Join. I'll be interested to see what you find!

I'm not sure if this is going to be helpful, but the SQL for the
subform that has all the possibilities displayed has this
recordsource:

SELECT [GenericName] & IIf(Not IsNull([SubType])," -- ") & [SubType]
AS Medication, qryMedsFilter.RegSortOrder,
qryMedsPatientFilter.PatientMedsID, qryMedsPatientFilter.MedsID,
qryMedsPatientFilter.PatientID, qryMedsPatientFilter.HistoryID,
qryMedsPatientFilter.Status, qryMedsPatientFilter.Created,
qryMedsPatientFilter.Updated, qryMedsPatientFilter.UpdatedBy,
qryMedsFilter.MedsID AS LookupID FROM qryMedsFilter LEFT JOIN
qryMedsPatientFilter ON qryMedsFilter.MedsID =
qryMedsPatientFilter.MedsID ORDER BY qryMedsFilter.RegSortOrder;

And the source queries are:

SELECT tblMeds.MedsID, tblMeds.GenericName, tblMeds.OtherNames,
tblMeds.SubType, tblMeds.RegSortOrder, tblMeds.Registration,
tblMeds.Followup, tblMeds.UNOS FROM tblMeds
WHERE
(((tblMeds.Registration)=[Forms]![frmRegistry]![chkRegistration])
AND ((tblMeds.UNOS)=[Forms]![frmRegistry]![chkUNOS])) OR
(((tblMeds.Registration)=[Forms]![frmRegistry]![chkRegistration])
AND ((tblMeds.Followup)=[Forms]![frmRegistry]![chkFollowup]) AND
((tblMeds.UNOS)=[Forms]![frmRegistry]![chkUNOS]));

And:

SELECT tblPatientMeds.PatientMedsID, tblPatientMeds.MedsID,
tblPatientMeds.PatientID, tblPatientMeds.Status,
tblPatientMeds.Created, tblPatientMeds.Updated,
tblPatientMeds.UpdatedBy, tblPatientMeds.HistoryID, ([HistoryID]<>0)
AS HistoryFilter FROM tblPatientMeds
WHERE
(((tblPatientMeds.PatientID)=[Forms]![frmRegistry]![fldPatientID])
AND ((([HistoryID]<>0))=[Forms]![frmRegistry]![chkHistory]));

In all cases, the forms are filtered based on values drawn from the
parent form of the subform where the list is displayed.

I'm pretty sure I got this from someone else, but I can't find the
sample database that I seem to recall having downloaded from
somewhere.

If the above is too confusing, I'll try to abstract the main
structure from it and repost something simpler.