473,326 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Updating a field's value based on another field

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!

Feb 21 '07 #1
4 29066
On Feb 21, 3:13 pm, "Haas C" <haas...@yahoo.comwrote:
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

Feb 21 '07 #2
On Feb 21, 5:13 pm, "Haas C" <haas...@yahoo.comwrote:
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.

Feb 21 '07 #3
On Feb 21, 3:13 pm, "Haas C" <haas...@yahoo.comwrote:
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

Feb 21 '07 #4
On Feb 21, 4:13 pm, "Haas C" <haas...@yahoo.comwrote:
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

Feb 21 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Steve | last post by:
Does any anyone have a procedure for a query where a calculated field returns the previous record's value in another field. For example: A F A Z F M Z The primary key is in random...
1
by: Emmanuel | last post by:
Hi, I have a webform with some input controls in it. Some of these controls have validation controls assigned. So far so good. I would like to have a validator which validates a field only if...
3
by: skinnybloke | last post by:
Hi - I have the following VB function within MS Access which is called via a query. How do I modify this code so that it will only do the replacement based upon the value of another field on the...
1
by: abprules | last post by:
Can somebody help me with this scenario? I want to update a date field when another field has a certain value entered. It is something like this: When the PDetType field ( from tlkpERegStatus...
1
by: sillyr | last post by:
Hi I wanted to make a statement in a form to take a value from one field if the first field has no value. I have a form created from two tables. Each table has a field called Haul number. On the...
14
by: mjvm | last post by:
HI, I have had a search for the answer to this question, but I can't transfer what I am reading to my database. I don't know enough about the language required, but have been able to get my...
3
by: BobbyD1120 | last post by:
I have created a inventory tracking database and I want to show/hide certain fields based on the device category. The deviceCategory field is a lookup field to a table that lists all the different...
1
by: Totti | last post by:
Hello, I am new to MS Access (2007) and am trying to create a hyperlink in a table to Google maps without much luck. The URL I need to use in my hyperlink is...
4
by: nedryerson | last post by:
Hi, I'm trying to get a certain field to appear only when a value from another combobox field is selected. Specifically, when "Sample Rejected" is selected in the field "PcrLabResults," I would...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.