conditional form calculations

E

Ed

I'm modifying the sample Northwind database. On the Orders form, I need to
have freight charges determined by the Shipper selection. I've tried using
an If Else statement in the code for the on_Focus event, but it doesn't
update. Any suggestions?
 
T

Tom van Stiphout

On Sat, 24 Jan 2009 18:41:01 -0800, Ed <[email protected]>
wrote:

Is the freight charge a fixed amount that varies by shipper, or is
there a more complicated formula?

-Tom.
Microsoft Access MVP
 
E

Ed

I have shippers set up as UPS and Pickup. If UPS is selected, the Freight
should be 10% of the subtotal. If it is Pickup, the Freight should be 0.00.
 
T

Tom van Stiphout

I am assuming Shippers is a dropdown. In its AfterUpdate event write:
select case Me.myShippersDropdown
case "UPS"
Me.myFreight = Me.mySubtotal * 0.1
case "Pickup"
Me.myFreight = 0
case Else
Debug.Assert "Unknown Shipper"
end select
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP
 
M

Mark Han[MSFT]

Hi Ed,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

After reviewing the reponse to you, I found Tom have given a good exmaple.
Thank you Tom. If your concern is not addressed, please email me. My email
is [email protected](remove online). in order to better assist
you, I would like to re-produce the issue.

If there is anything unclear, please let me know.

Have a nice day!

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: [email protected].
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
M

Mark Han[MSFT]

Hi Ed,

I'm Mark Han. I'm writing to follow up the post.

If I can assis you anthing related to the issue, please let me know.

I look forward to your update. Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: [email protected].
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================
 
L

Linq Adams via AccessMonster.com

You've got 23 forms. You might give us a hint as to which one you're trying
to do this with, or at least the name of your dropdownbox/combobox.
 
L

Linq Adams via AccessMonster.com

Finally found it. You didn't say what the freight was if "Party" was chosen
from the combobox, so I set it as zero; you can change it to suit.

Private Sub ShipVia_AfterUpdate()

Select Case Me.ShipVia.Column(1)
Case "UPS"
Me.Freight = Me![Orders Subform].[Form]![OrderSubtotal] * 0.1

Case "Pick up"
Me.Freight = 0

Case "Party"
Me.Freight = 0
End Select

End Sub

Not really sure about your field named UPSCharges. You can leave it as is, if
you want to show what the charges would be if shipped UPS. Or you can remove
the Control Source you have assigned and replace the above code with

Private Sub ShipVia_AfterUpdate()

Select Case Me.ShipVia.Column(1)
Case "UPS"
Me.Freight = Me![Orders Subform].[Form]![OrderSubtotal] * 0.1

Case "Pick up"
Me.Freight = 0

Case "Party"
Me.Freight = 0
End Select

Me.UPSCharges = Me.Freight

End Sub
 
L

Linq Adams via AccessMonster.com

Meant to add that Tom's code didn't work for a couple of reasons, none of
which he could have known about from your post..

The UPS, Pick Up, etc was the second column in your combobox, not the first.
so

Select Case Me.myShippersDropdown

needed to be

Select Case Me.myShippersDropdown.Column(1)

"Pickup" in the combobox was actually "Pick up" and they have to match
exactly, including the spavce between Pick and up.. In this case, because you
don't have a Compare Option declaration at the beginning of your form code
module, it defaults to Compare Option (binary) so even the case of the
letters has to match.
 
Top