Access 2003 - How to auto-insert records into a table when a record is inserted in a different table

Posted on 2011-04-25
Medium Priority
Last Modified: 2012-08-14
I am trying to create a very simple Access database for scheduling visits.  What I would like to have happen, is when a new record is inserted into the tblBaseline, that at the time that record is inserted/saved that all future visits (6 months apart) get inserted into a second table: tblScheduledVisits.

For example, if I insert this record into tblBaseline:
StudyID = 1001, baseline_visit_date = 12/1/2010

I want to have the following records inserted into tblScheduledVisits:
StudyID = 1001, visit_date = [baseline_visit_date] + 6 months
StudyID = 1001, visit_date = [baseline_visit_date] + 12 months
StudyID = 1001, visit_date = [baseline_visit_date] + 18 months

all the way up to a 5 year period.

I have no idea how to code something like this.  Is anyone able to help?
Question by:sah18
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 35461399

you will need vba codes to do that

sub InsertRecords(id as long, dDate as date)

dim rs as dao.recordset, j as integer
set rs=currentdb.openrecordset("tblScheduledVisits")

for j= 1 to 10  ' < 10=(5*12)/6
    rs!StudyID = id
    rs!visit_date= dateadd("m", 6 * j,dDate)


end sub

call the sub insertRecords passing the id and the baseline date after the record was created

Author Closing Comment

ID: 35493410
This is exactly what I needed.  Thank you very much!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question