Create new and populate with subtables - too complicated???

[carriey]

I may have promised something I can't deliver here. What I need to do is if
a certain box in a table/form is checked then I need it to create a new
record in another table and populate some data. From what I've been reading
this is possible however, there are also subtables involved on either side
and if certain boxes are checked in the one subtable/subform, it also needs
to populate those into the other subtable/form. Am I expecting too much?

Tbl_MAIN PK: Record_ID

Subtbl_Internal_Inspections PK: IntInsp (RecordID is one-to-many)
Subtbl_IntInspection_Deficiencies: PK: IntDefID (IntInspID is one-to-many)

Subtbl_Obligations_MAIN PK: Oblig_ID (Record_ID is many-to-many through
TBL_Junction)
Subtbl_Obligations_Deficiencies PK: ObligDefID (Oblig_ID is one-to-many)

There is a Main Form which has a subform for each of Internal_Inspections
and Obligations. Each of those has a subform for the Deficiencies.

What I need to happen is that if in Internal_Inspections, the box
Self_Dec_Required is checked it will automatically create a new record in
Subtbl_Obligations_MAIN and fill in the following:

Oblig_Status = Open
Oblig_Date = Insp_Rcvd (from Internal Inspections)
Employee = Employee (from Internal Inspections)
Oblig_Type = Self Declaration (this is a drop down - not sure if that matters)
Internal_Insp = Yes

The Subtbl_Int_Inspection_Deficiencies also includes a Self_Dec checkbox
because only some of the deficiencies will require a Self Dec. For each one
that is checked yes, I would like it to create a new OblDefID in the
Subtbl_Obligations_Deficiencies and fill in the following (linked to the
Oblig_ID) from the Subtbl_Int_Inspection_Deficiencies table

Deficiency = Deficiency
Deficiency_Comments = Deficiency_Comments
Ext_Granted_To = Due_Date

There are lots of other unrelated fields in either table that don't need to
be populated. If it's even possible to do this, my assumption is that I will
need to do it by coding on the forms but I would really appreciate any advice
that you can give.

 

[Scott Lichtenberg]

Carrie,

Adding records to tables via code is exactly what Access is made to do, and
it isn't all that difficult. I would strongly recommend that you invest in
a book on Access and VBA. Here's some code that might point you in the
right direction. It is going to add a record in the "Deficiencies" table
based on values on a main form linked to your "Inspections" table. I've
changed some of the names of your fields and tables to make the code read
more clearly.

Dim db as Database
Dim rs as Recordset

'Open a recordset using the deficiencies table.
Set db = CurrentDb
Set rs = db.OpenRecordset("Deficiencies", dbOpenDynaset)

'Add a new record
rs.AddNew 'Begins the process of adding a record

'Set fields.
rs!InspectionID = Me!InspectionID 'The Me object refers to your
active form.
rs!SomeDateField = Date 'Set a date field to today
rs!AnotherField = Me!InternalInspectionSubform.Form!AnotherField
'Note syntax for getting data from a subform.
rs!LookupField = DLookup("MyColumn", "MyLookupTable", "InspectionID = "
& Me!InspectionID 'Use a Dlookup to get data

rs.Update 'Write the record

rs.Close
Set rs = Nothing

You can open as many recordsets and add as many records as you'd like. You
can put a command button on your form, and run the code in the OnClick
event.

Hope this helps.

 

[carriey]

Thanks a lot Scott - I am going to give this a try - I appreciate you
responding