Append nth records to sub-table automatically

[MadsNilsson]

Hi there! This forum has helped me out before, so I hope you guys can help me out with this?
I have one master table with two linked tables. Each time a new record is added to the master table - I want a number of new records to be added to the sub tables.

1. Master table = "Family" (general family information - like private phone/adress)
2. Linked sub-table: "Members" (the members of the family)
3. Linked sub-table: "Assets" (assets to trade).

Each time a new family record is added - I want to add 6 records in the "Assets"-table.

In the master table I have a drop-down list (3-5): I want to add the corresponding number of records in the "Members"-table when the number of members are selected.

The database is going to be used by persons who not now access well, so it would be nice if this could be done without any popups and questions during the append.

 

[dhookom]

Each added record in the related table should have some unique value. You haven't described what values should be added into the Members and Assets tables.

I haven't found any users who couldn't enter a new record at the bottom of a continuous form. There are times when information might be entered into text boxes on the main form and then appended to the related table with the click of a button.

Duane
Hook'D on Access
MS Access MVP

 

[MadsNilsson]

Hi dhookom,

Thanx again for your replies. No values should be added into the members and assets tables (maybe just a number in a field called "Asset number" or "Member number". This is just for creating corresponding records to the number of the family members - and their assets. The family it self will fill in the values.

M

 

[dhookom]

Don't you want to add the foreign key field values? Do you have names of any fields or the controls on the form?

I wouldn't do this without better reason... However, I would have a table [tblNums] with a single numeric field [Num] and records with values 0, 1, 2, 3, 4, 5, ... You can then run an append query in code like below. I don't know what event you would use to do this.

Dim strSQL as String
strSQL = "INSERT INTO Members (FamilyID, MemNumber) " & _
    "SELECT " & Me.FamilyID & ", Num FROM tblNums " & _
    "WHERE Num Between 1 and " & Me.cboNumberToAdd
Currentdb.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP