G
Guest
Hello! I am following some great instructions on how to automate data entry
with a macro, but their example is too narrow for what I need to accomplish.
Their example identifies all towns in one county list, auto-populates the
County field based on the entry in Town, and advances the user to the Zip
code field using a macro with conditions.
I'd like to take this principle much further. I have my main table of
employees (Employees) that can apply for 3 types of insurance, and I have a
second table (Coverage Lines) that identifies the 3 different types of
insurance available (VLI, VLA, and VLD are the 3 unique product IDs and are
set up as key identifiers).
On my form, the first field for the first type of insurance (voluntary life,
or VLI) asks what amount of insurance the employee is applying for (the field
is [VLIApp], and I have named the text box txtVLIApp). The next step is to
evaluate whether the amount applied for exceeds 2 tests for the maximum you
can get without having to go through medical evidence of insurability (EOI
test). The 2 tests are "3x salary rounded up to the nearest $1,000" and
"$100,000" (for VLI), and the test criteria are housed in the Coverage Lines
table under the record VLI, so the condition macro has to "find" the VLI
entry, retrieve the two test parameters, retrieve the employee's salary,
multiply it by 3 and round it up, etc.
If the amount applied for falls under those two thresholds, I would like the
macro to copy the VLIApp value to the VLIBen field, then for the VLI_EOI
field (which simply asks what date EOI was approved), it will be a
zero-length string ("") as approval is not needed.
If either test fails (threshold is exceeded), then the VLI_EOI field must
remain null and the VLIBen field must be limited to the lesser of the 2 tests
(so for VLI, it must use the lesser of the employee's salary times 3 rounded
up to the nearest $1,000 or $100,000).
Then the macro should GoTo the next insurance type on the form, VLA.
If the amounts do exceed the EOI levels and we do find that EOI is approved,
then when our user enters the approval date, it needs to ensure the amount
applied for does not exceed the maximum allowed by the plan. This again
requires 2 tests: cannot exceed 5x salary and cannot exceed $300,000. If
both tests are met, VLIBen is updated with the newly approved amount (it was
limited to the EOI max until a proper approval date is entered).
Any help or direction to a good resource is appreciated.
with a macro, but their example is too narrow for what I need to accomplish.
Their example identifies all towns in one county list, auto-populates the
County field based on the entry in Town, and advances the user to the Zip
code field using a macro with conditions.
I'd like to take this principle much further. I have my main table of
employees (Employees) that can apply for 3 types of insurance, and I have a
second table (Coverage Lines) that identifies the 3 different types of
insurance available (VLI, VLA, and VLD are the 3 unique product IDs and are
set up as key identifiers).
On my form, the first field for the first type of insurance (voluntary life,
or VLI) asks what amount of insurance the employee is applying for (the field
is [VLIApp], and I have named the text box txtVLIApp). The next step is to
evaluate whether the amount applied for exceeds 2 tests for the maximum you
can get without having to go through medical evidence of insurability (EOI
test). The 2 tests are "3x salary rounded up to the nearest $1,000" and
"$100,000" (for VLI), and the test criteria are housed in the Coverage Lines
table under the record VLI, so the condition macro has to "find" the VLI
entry, retrieve the two test parameters, retrieve the employee's salary,
multiply it by 3 and round it up, etc.
If the amount applied for falls under those two thresholds, I would like the
macro to copy the VLIApp value to the VLIBen field, then for the VLI_EOI
field (which simply asks what date EOI was approved), it will be a
zero-length string ("") as approval is not needed.
If either test fails (threshold is exceeded), then the VLI_EOI field must
remain null and the VLIBen field must be limited to the lesser of the 2 tests
(so for VLI, it must use the lesser of the employee's salary times 3 rounded
up to the nearest $1,000 or $100,000).
Then the macro should GoTo the next insurance type on the form, VLA.
If the amounts do exceed the EOI levels and we do find that EOI is approved,
then when our user enters the approval date, it needs to ensure the amount
applied for does not exceed the maximum allowed by the plan. This again
requires 2 tests: cannot exceed 5x salary and cannot exceed $300,000. If
both tests are met, VLIBen is updated with the newly approved amount (it was
limited to the EOI max until a proper approval date is entered).
Any help or direction to a good resource is appreciated.