Form / Subform Question

[Joe Williams]

I posted this before but possibly I was not being clear enough, so please
forgive:

I have a main form that lists Employees. A subform is attached to this main
form that lists the different benefit deductions each employee has, listed
as a percentage. The fields for this subform are Employee ID, Benefit ID,
and percentage deduction.

My problem is this: When a new employee is entered, they should
automatically have a certain default entry for benefit ID and percentage
deduction. Then if more are needed to be added, they can be added at this
time. But I need the first child record to be automatically inserted when
the main record is inserted.

How can I accomplish this?

Thanks

Joe

 

[Allen Browne]

If all employees are entered through your Employee form, you can use the
AfterInsert event procedure of the form to execute an Append query statement
to insert the related record.

Presumably you have a Benefit table, with BenefitID as primary key, listing
the possible benefits. Add a yes/no field to this table named (say)
IsDefault. Check this box for the record(s) that should be added by default.

The event procedure will look like this:
Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Now the important thing is getting the SQL statement into the 2nd line of
the procedure. To get the SQL statement:
1. Mock up a query into the Benefit table.

2. Change it to an Append query (Append on Query menu).
Access will ask which table to append to. It is the subform's table.

3. Drag IsDefault into the grid, and in the Criteria row enter:
True

4. Drag the other fields into the grid. For any fields that are not there
(such as EmployeeID) you can just type some test value into the Field row.

5. Once you have it mocked up, switch to SQL View (View menu). You can now
see the SQL statement you need for the event procedure above.