AfterUpdate Code Help

[BardsSweetie]

Hi all,

I have 3 tables (and forms) - tblFee6, tblFee61 & tblFee62. They all have
the same fields - MinAmount & Fee.

Each Fee field in each form has to have a different calculation based on an
EndingBid. (Is this right? I have to do the expressions in Control Source in
the Form?)

Ex 1: EndingBid is $9.99 - I need the FinalValueFee to be EndingBid*.0525.

Ex 2: EndingBid is $30.00 - I need the FinalValueFee to be
EndingBid*.0275+1.31

Ex 3: EndingBid is $1500.00 - I need the FinalValueFee to be
EndingBid*.0150+28.12.

I couldn't figure out how to get the three different calculations in the
same place so I separated them. If someone could help and let me know what
I'm doing wrong, it would be appreciated.

Do I need to enter all calculations into one Query and base my AfterUpdate
on that query?

In the Forms tblFee6, tblFee61 & tblFee62 All the expressions work nicely -
IN THE FORM.

How can I get the FinalValueFee to work by choosing which calculation to
perform based on the EndingBid?

You guys have been great!!

Thanks,

Kathleen

 

[Brian Bastl]

Hi Kathleen,

Since nobody has stepped in to offer assistance, I'll put in my 2 cents.
Answers/ comments in-line.

I have 3 tables (and forms) - tblFee6, tblFee61 & tblFee62. They all have
the same fields - MinAmount & Fee.

There is absolutely no valid reason for you to have 3 identical tables. You
should really stop right here, right now, and read up on and at least
partially understand the concept of "Normalization." Without a properly
structured database, you'll continually be beating your head against the
wall, so to speak.

Each Fee field in each form has to have a different calculation based on an
EndingBid. (Is this right? I have to do the expressions in Control Source in
the Form?)


Ex 1: EndingBid is $9.99 - I need the FinalValueFee to be EndingBid*.0525.

Ex 2: EndingBid is $30.00 - I need the FinalValueFee to be
EndingBid*.0275+1.31

Ex 3: EndingBid is $1500.00 - I need the FinalValueFee to be
EndingBid*.0150+28.12.

Easy enough to do if you have a FeeSchedule look-up table with a structure
similar to the following:

tblFeeSchedule:
FeeID* <autonum> ' * denotes table's primary key (PK)
MinValue <currency> 'lower range for fee calculation
MaxValue <currency> 'upper range for fee calculation
Multiplier <double> 'multiplier for a number which falls between MinValue
and MaxValue
FixedFee <currency> 'surcharge for number which falls between MinValue and
MaxValue

Your records would then look like this:
ID; MinValue; MaxValue; Multiplier; FixedFee
1; $0.01; $9.99; 0.525; $0.00
2; $10.00; $50.00; 0.0275; $1.31

I couldn't figure out how to get the three different calculations in the
same place so I separated them. If someone could help and let me know what
I'm doing wrong, it would be appreciated.

Do I need to enter all calculations into one Query and base my AfterUpdate
on that query?

In the Forms tblFee6, tblFee61 & tblFee62 All the expressions work nicely -
IN THE FORM.

How can I get the FinalValueFee to work by choosing which calculation to
perform based on the EndingBid?

You can then use DLookup() function to determine the multiplier and
surcharge to use in your expression depending upon which dollar range your
EndingBid falls into.

HTH,
Brian

 

[BardsSweetie]

Hi Brian,

Thanks bunches!! It didn't take long to fix the tables.

I tried writing the DLookup() function, but I can't get it right. I'm not
sure what I'm doing wrong or if I'm leaving something out. My fields are:

FeeID
MinAmount
MaxAmount
Multiplier
FixedFee

Did I assume correctly that the code has to be written in the AfterUpdate()?

This is what I wrote ... just can't get it right.

Private Sub EndingBid_AfterUpdate()
DLookup("[MaxAmount]", "[tblScheduleEnd]", "[MaxAmount] <=
Forms![AuctionSales]![EndingBid]")
End Sub

Can you help me out with the correct way it should be written?

Thanks,

Kathleen

 

[Brian Bastl]

Kathleen,

I've been thinking about it, and in this particular case, perhaps you want
to store the FinalValueFee. That way you can change the fee schedule anytime
you like, but it won't affect the final price on items that were sold before
you changed the it. So in this case, I'd create a field in your AuctionSales
table for FinalValueFee, and bind your control to it. Then..... your code
could look like this:

'begin code
Private Sub EndingBid_AfterUpdate()

Dim mltp As Double
Dim ff As Currency

mltp = DLookup("Multiplier", "tblScheduleEnd", "[MinValue] <= " &
Me.EndingBid & " AND [MaxValue] >= " & Me.EndingBid)

ff = DLookup("FixedFee", "tblScheduleEnd", "[MinValue] <= " &
Me.EndingBid & " AND [MaxValue] >= " & Me.EndingBid)

Me.FinalValueFee = (Me.EndingBid * (1 + mltp)) + ff

End Sub
'end code

HTH,
Brian

 

[BardsSweetie]

Brian,

What do you mean by "bind my control to it" ... "it" being the FinalValueFee
in the table?

Thanks,

Kathleen

 

[Brian Bastl]

What I mean is for you to add a field to your AuctionSales table, and name
it somthing like FinalValueFee. Then in design view, add a text box to your
AuctionSales form, name it FinalValueFee, and set its control source to the
field in your table by selecting it from the dropdown. This is what is
referred to as "binding".

HTH,
Brian

 

[BardsSweetie]

Ok ... gotcha ... will let you know how it goes.

Kathleen

 

[BardsSweetie]

Hi Brian,

It didn't work. No error messages or any popups either.

What is the "Me." thing? Wouldn't that have to be changed to something else?
In another code I had to change the "Me." to the field that the calculation
was going to. In that case it was the InsertionFee field.

What do I do now?

Thanks,

Kathleen

 

[Brian Bastl]

I guess you need to tell me where you are storing the data and the name of
the form and control into which you're doing the calculation.

Brian

 

[BardsSweetie]

The name of the form (and table) is AuctionSales.

The control I'm doing the AfterUpdate on is EndingBid.

I'm not sure what you mean by where I'm storing the data, but I copied and
pasted the coding into Microsoft Visual Basic that comes up when I click to
put code in AfterUpdate.

If that's NOT what you meant just let me know.

Thanks ... you've been great!!

Kathleen

 

[Brian Bastl]

Kathleen,

Did you add the field named FinalValueFee <data type = currency> to your
AuctionSales table as I suggested? And did you also add a text box on your
AuctionSales form with the same name? The code will work then. If you did
not, and you just want to change the value of EndingBid, then you'd alter
the code I gave you to:

'begin code
Private Sub EndingBid_AfterUpdate()

Dim mltp As Double
Dim ff As Currency

mltp = DLookup("Multiplier", "tblScheduleEnd", "[MinValue] <= " &
Me.EndingBid & " AND [MaxValue] >= " & Me.EndingBid)

ff = DLookup("FixedFee", "tblScheduleEnd", "[MinValue] <= " &
Me.EndingBid & " AND [MaxValue] >= " & Me.EndingBid)

Me.EndingBid = (Me.EndingBid * (1 + mltp)) + ff

End Sub
'end code

To answer your question about Me: Me is the vba shortcut reference to the
name of your form in the form's code module. For example, you have a form
named AuctionSales, and on this form you have a text box named EndingBid. To
refer to EndingBid on this form, in this form's coding module, you'd use
Me.EndingBid or Me!EndingBid .... I prefer the former with the <dot> as
opposed to the "!". So the code that I gave you assumes that you have a text
box on your AuctionSales form with the name of EndingBid.

If on the other hand, you want to refer to EndingBid from within the coding
module of a different form, you'd use the following syntax:

Forms!AuctionSales!EndingBid

HTH,
Brian

 

[Brian Bastl]

Kathleen,

if you'd like to send me a stripped down copy of your database, I'll be glad
to take a look at it to help you along a bit.

x<at>y<dot>z
x=bastel
y=alltel
z=net

Brian