Updating a field's value based on another field

[Haas C]

Hi all,

I was wondering if any of you guys can help me out with this:

I have two fields on a form: one field is Premium and the other is
Brokerage. Whatever amount is in the Premium field is multiplied by
10% in the Brokerage field. If I change the amount in the Premium
field, the Brokerage amount should change accordingly as well. I know
I can do this in a query with a simple calculation set up; however,
there are exceptions to the 10% rule. Therefore, I'll need the ability
to change the amount in the Brokerage field from time to time - with a
Query, that won't be possible. What is the easiest way to acomplish
what I want. I am a newbie so if you can provide me with step-by-step
instructions, it will be most appreciated.

Thanks!

[Ron2006]

On Feb 21, 3:13 pm, "Haas C" <[e-mail]:

Hi all,

I was wondering if any of you guys can help me out with this:

I have two fields on a form: one field is Premium and the other is
Brokerage. Whatever amount is in the Premium field is multiplied by
10% in the Brokerage field. If I change the amount in the Premium
field, the Brokerage amount should change accordingly as well. I know
I can do this in a query with a simple calculation set up; however,
there are exceptions to the 10% rule. Therefore, I'll need the ability
to change the amount in the Brokerage field from time to time - with a
Query, that won't be possible. What is the easiest way to acomplish
what I want. I am a newbie so if you can provide me with step-by-step
instructions, it will be most appreciated.

Thanks!

In the after update event for Premium add code.

if isnull(me.premium) then
me.brokerage = 0
else
me.brokerage = me.premium * .10
endif

I would suggest that you put that .10 in some table somewhere, and
change your code to something like this

(again in the afterupdate)
actPercent = dlookup ("[tblPercent]", "Query that gets percent from
table")
if isnull(me.premium) then
me.brokerage = 0
else
me.brokerage = me.premium * actPercent
endif

And use that method whereever you might change the value. That way
you don't have to hunt down all the places when the percentage goes
to .12 - just change the table entry.

Ron

[storrboy]

On Feb 21, 5:13 pm, "Haas C" <[e-mail]:

Hi all,

I was wondering if any of you guys can help me out with this:

I have two fields on a form: one field is Premium and the other is
Brokerage. Whatever amount is in the Premium field is multiplied by
10% in the Brokerage field. If I change the amount in the Premium
field, the Brokerage amount should change accordingly as well. I know
I can do this in a query with a simple calculation set up; however,
there are exceptions to the 10% rule. Therefore, I'll need the ability
to change the amount in the Brokerage field from time to time - with a
Query, that won't be possible. What is the easiest way to acomplish
what I want. I am a newbie so if you can provide me with step-by-step
instructions, it will be most appreciated.

Thanks!

Take a look in the help files for a topic called or similar to "Set
the value of a control based on the value of another control by using
Visual Basic". You'll probably want to apply what you learn to the
Premium fields AfterUpdate event.

[Jana]

On Feb 21, 3:13 pm, "Haas C" <[e-mail]:

Hi all,

I was wondering if any of you guys can help me out with this:

I have two fields on a form: one field is Premium and the other is
Brokerage. Whatever amount is in the Premium field is multiplied by
10% in the Brokerage field. If I change the amount in the Premium
field, the Brokerage amount should change accordingly as well. I know
I can do this in a query with a simple calculation set up; however,
there are exceptions to the 10% rule. Therefore, I'll need the ability
to change the amount in the Brokerage field from time to time - with a
Query, that won't be possible. What is the easiest way to acomplish
what I want. I am a newbie so if you can provide me with step-by-step
instructions, it will be most appreciated.

Thanks!

Haas:
Here is some code I've used in one of my forms, modified (I think) to
your needs. It leaves the Brokerage field editable in the event you
want to manually change it. Go to the properties of your Premium
field on your form. Click on the Event tab and select [Event
Procedure] in the After Update box. Then, click on the button with
the ... to the right of the After Update box and paste this where it
dumps your cursor:

Dim Response
Dim Msg as String
Dim Title as String

If IsNull(Me!Premium) Then
Msg = "Would you like to reset the Brokerage amount for this
record to zero?"
Title = "Reset Brokerage Amount?"
Response = MsgBox(Msg, vbYesNo, Title)
If Response = vbYes Then
Me!Brokerage = 0
Else
Exit Sub
End If
Else
If Not IsNull(Me!Premium) Then
Me!Brokerage = Me!Premium * .10
End If
End If
HTH,
Jana

[jonceramic]

On Feb 21, 4:13 pm, "Haas C" <[e-mail]:

Hi all,

I was wondering if any of you guys can help me out with this:

I have two fields on a form: one field is Premium and the other is
Brokerage. Whatever amount is in the Premium field is multiplied by
10% in the Brokerage field. If I change the amount in the Premium
field, the Brokerage amount should change accordingly as well. I know
I can do this in a query with a simple calculation set up; however,
there are exceptions to the 10% rule. Therefore, I'll need the ability
to change the amount in the Brokerage field from time to time - with a
Query, that won't be possible. What is the easiest way to acomplish
what I want. I am a newbie so if you can provide me with step-by-step
instructions, it will be most appreciated.

Thanks!

Haas,

The answer depends on _when_ you'll be changing the Premium values or
rewriting the rules. Generically, I can think of 3 ways you might be
needing this...

If you are calculating at the time of Premium entry based on the
current rules, and storing that forever (like a receipt), then you
should apply the equations to set Brokerage using the "OnUpdate" or
"OnInsert" event of the Premium Field. You could do this with VBA if/
then statements, or you could do this with Macro conditions.

If you're looking to store the Premium values and create a table of
Brokerage values, but then change those Brokerage values when the rule
changes... then in that case, I would actually trigger an update query
using Where clauses or iif expressions.

Finally, if you just want to view Brokerage values on a report, then
I'd just use an iif statement with your rules in a text box (as
opposed to actually having a field called "Brokerage").

It might also help if you tell us the busienss rules you use in
addition to the 10% rule.

Jon