automatically adding record in a secondary table

[Lou G]

Greetings:
I searched for an answer, but couldn't find an existing post that met my needs. I'm a long-time Access user, and have created many databases for personal use. However, I'm not a programmer, and have little knowledge of creating VBA. I would greatly appreciate help!

Our agency conducts forensic interviews of children who've been severely abused or sexually assaulted for purposes of criminal prosecution of offenders. In addition, the agency provides counseling and numerous other services to victims and their families. Staff must enter intake information for each child in a case file. They must also keep track of activities done for victims and families, and the amount of time spent on each of these activities.

I have a primary case file table, and a secondary table for tracking these other events--sort of a log. On the day of the interview of the child, the staff spends about 3 hours on various activities before, during, and after the interview. Upon entering a new case file in the primary table, I would like to have a record automatically created in the log table with the case ID from the primary table and an entry of 3 hours activity for that initial work. I'm sure this can be accomplished via some code, but I don't know where to begin to create it...

Here's structure info:

tblCaseFile: Main table - all victim demographic data, and other case-related data is entered and tracked in/from this table.

Primary key: CaseID; this is a text field. (It is not an autonumber because this ID number is assigned by agency staff using a specified format.)
Other fields in this table relevant to my question:

• InterviewDate (short date format)
• InterviewTime (medium time)
• IntakeCompletedBy (number format; this field links to a lookup table with all staff names and corresponding staff IDs -- when a name is selected from the field drop-down, the staffID is stored CaseFile.IntakeCompletedBy)

tblFamilyAdvocacyActivity: log of activities completed on behalf of victims

Primary key: FaaID - autonumber
CaseID - text format; linked to CaseFile.CaseID in a 1-many relationship with referential integrity enforced and cascade delete enabled
Other fields in this table relevant to my question:

• ActivityDate (short date format)
• ActivityTime (medium time format)
• StaffID (number format; also links to same lookup table with staff names/staff IDs -- when name is selected from field drop-down, staffID is stored in FamilyAdvocacyActivity.StaffID
• ActivityType (text format; links to a lookup table with a list of the types of activities and corresponding ActivityIDs -- when an activity type is selected from the field drop-down, the ActivityID is stored in FamilyAdvocacyActivity.ActivityType
• ActivityLength (number format; hours spent by staff on an advocacy activity)

formInitialIntake: used by staff only to enter new cases into tblCaseFile; fields on this form are arranged to match the order of fields on printed intake forms

formEditCaseFiles: used by staff to work with case files after initial data entry; the fields from tblCaseFile are arranged on this form in a format that corresponds to staff needs during on-going case tracking. It is tabbed. One of the tabs is called "Family Advocacy Log" and displays subfrmFamilyAdvLog, which displays fields for tblFamilyAdvocacyActivity.

Can anyone suggest code (sorry, but you'll have to be very patient and quite literal since I've not written code before...) to do the following:
1. After initial data entry of a new record in tblCaseFile (with a new CaseID) using formInitialIntake, clear out all temporary variables that might be in use from other activities in the database, and then gather information from the new record for CaseID, InterviewDate, InterviewTime, and IntakeCompletedBy fields into temporary variables.

2. Automatically create a new record in tblFamilyAdvocacyActivity (FaaID is an autonumber...)

3. Dump the temporary variables into fields within this new record as follows:

a. CaseFile.CaseID to FamilyAdvocacyActivity.CaseID
b. CaseFile.InterviewDate to FamilyAdvocacyActivity.ActivityDate
c. CaseFile.InterviewTime to FamilyAdvocacyActivity.ActivityTime
d. CaseFile.IntakeCompletedBy to FamilyAdvocacyActivity.StaffID
​

4. Enter activity type of "InitialCaseMgt" into FamilyAdvocacyActivity.ActivityType

5. Enter "3" into FamilyAdvocacyActivity.ActivityLength

6. Enter text of "Initial case management activities" into FamilyAdvocacyActivity.Description

Thanks in advance for any help/suggestions/code you may offer!
Lou

 

[Pat Hartman]

Welcome aboard

1. Why have you created temporary variables? You said you haven't written any VBA so I'm assuming that these have something to do with some macro. What are you doing with the macros?
2. It is wrong to automatically create records in related tables when you create a record in the main table. Just create the related records as you have information to add.
3. See question 1.
4. You can set this as the default value for the form control. This control should be a combo that lists all availabe ActivityTypes.
5. Make this a default also
6. Same.

All done, no code

 

[Lou G]

Thanks for your reply, Pat. I must not have been clear in my note, but there is something that needs to be posted into the FamilyAdvocacyActivity table immediately after entering a new case record in the main database -- 3 hours of case management activity. This is an activity record that needs to be included in the activity log for every case so that when we calculate total time spent on case activities we are able to include these 3 hours.

In response to your question about temporary values stored, there are a couple of macros in this database that initiate from buttons. I know it is good practice to first clear out possible temp variables before storing new stuff in them... Just in case there is anything still hanging out there in RAM from a previous function, so that's why I referenced it. I don't know anything about programming in VBA, but I've been working in the field of technology for a long time...a novice with programming, but not with logic... But thanks for asking.

Nothing in the secondary table is a duplication of data from the primary table. The data being stored in the secondary (activity log) is a distinct record for that table, but because it must be entered into the database immediately on the heels of entering the new case file into the primary table, I want to automate that process. I want to use the date and time of the child's interview, because that is when the activity actually does occur.

Hope this makes sense! I really appreciate your interest and feedback!
lou

 

[Pat Hartman]

Sorry I missed the point that this is a log record. Is the insert something you do for EVERY record created on the form or for only some records? If it is for only some records, You'll need to define the conditions and alter the code below to only run when necessary.

You can use DAO to insert the record. I used what I assumed to be control names from the current form in the code below to populate the new record.

Put this code in the AfterInsert event of the Form. If I didn't make any typos, you shouldn't have to change it.

The first three lines define the objects the code will use. The next three assign values to those objects. The rest of the code populates the columns of the recordset using values from the form the code is running in and inserts the record. At the end, it closes the recordset.

Dim db As DAO.Database
Dim td as DAO.Tabledef
Dim rs as DAO.Recordset

Set db = Currentdb()
Set td = db.Tabledefs!tblFamilyAdvocacyActivity
Set rs = td.OpenRecordset
    rs.AddNew
        rs!CaseID = Me.CaseID
        rs!ActivityDate = Me.InterviewDate
        rs!ActivityTime = Me.InterviewTime
        rs!StaffID = Me.IntakeCompletedBy 
        rs!ActivityType = "InitialCaseMgt"
        rs!ActivityLength = 3
        rs!Description =  "Initial case management activities"
    rs.Update
rs.Close