automatically creating selected number of records in different table

[hepheathie]

Hi,

I am setting up a database to track insurance policies issued, and
I've hit a wall with one item. Each policy can have up to 10 classes,
and each class can have an unlimited number of benefits. I have a
table for the policies that currently links to a table for the
classes, and that links to a table for each benefit applicable to each
class. To enhance this, I'd like to add a field to the policy table
where the underwriter enters the number of classes (x), and then x
number of records is automatically created in the classes table (and
each record that was created is numbered consecutively from 1 to x in
the "class" field). Then, the underwriter can go through each class,
select the benefits in a subform, and enter the pertinent information
about each benefit.

For example, if policy number 12345678 has 5 classes, the underwriter
would select "5" on the policy form, and then 5 records (numbered in
the "class" field 1,2,3,4,5) would be added to the class table.

Any thoughts on how to accomplish this? The way it is now, the
underwriter would have to create a record for each class, number each
class, and take it from there.

Thanks,

Heather

 

[John W. Vinson]

For example, if policy number 12345678 has 5 classes, the underwriter
would select "5" on the policy form, and then 5 records (numbered in
the "class" field 1,2,3,4,5) would be added to the class table.

In practice, it's almost never either necessary nor beneficial to create such
"placeholder" records. They have a nasty habit of never getting filled in.

Any thoughts on how to accomplish this? The way it is now, the
underwriter would have to create a record for each class, number each
class, and take it from there.

Use a Form for the policy, and a Subform for the class. In the subform's
Beforeinsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim lNext As Long
lNext = NZ(DMax("[Class]", "[Classes]", "[PolicyNo] = " & Me!PolicyNo) + 1
If lNext > Parent!HowManyClasses Then
Cancel = True ' don't insert too many classes
Else
Me!txtClass = lNext
End If
End Sub

John W. Vinson [MVP]

 

[hepheathie]

In practice, it's almost never either necessary nor beneficial to create such
"placeholder" records. They have a nasty habit of never getting filled in.

You're probably right. I'll mention it to the underwriting team, but
the final decision is theirs.

Use a Form for the policy, and a Subform for the class. In the subform's
Beforeinsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim lNext As Long
lNext = NZ(DMax("[Class]", "[Classes]", "[PolicyNo] = " & Me!PolicyNo) + 1
If lNext > Parent!HowManyClasses Then
Cancel = True ' don't insert too many classes
Else
Me!txtClass = lNext
End If
End Sub

John W. Vinson [MVP]

Thanks for this! I'll play around with it and see if I can get it to
work.

Heather