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

Posted on 2011-04-25
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 119

    Accepted Solution


    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
    LVL 9

    Author Closing Comment

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

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now