Hey Everyone, I have a fairly simple, but for me advanced question.

Here's the story. I have a calculated text box in a form that is
supposed to read from a table. The table is only 3 records. Looks
like this.

_____________________________
Start | End | Discount
1 |100000 | 0
100001 |400000 | .0025
400001 |2000000 | .0050
_____________________________

That's it simple enough. My problem is that I can't figure out how to
read from this table in the VBA code of the form. This is the code
that I hard coded in to fool the client. hehe.

______________________________
Private Sub GALLONS_LostFocus()

If [GALLONS] >= 1 And [GALLONS] <= 100000 Then
VolumeDiscount = 0
ElseIf [GALLONS] >= 100001 And [GALLONS] <= 400000 Then
VolumeDiscount = ([GALLONS] - 100000) * 0.0025
ElseIf [GALLONS] >= 400001 And [GALLONS] <= 2000000 Then
VolumeDiscount = ([GALLONS] - 400000) * 0.005 + 750
Else: VolumeDiscount = 0
End If

End Sub
______________________________

This is a rigged way to do it and the client will need to update this
information via the table, not the hard coding. Does anyone know a
way to refer to the data in the table rather than hard coding it?

Assume the above table's name is Discount. Put this code in the form's event
procedure:

VolumeDiscount = GetDiscount(x)

This calls a new function GetDiscount, which takes a volume parameter.
Replace x with the necessary volume value (based on a form control?)
In a standard module, insert the following code:

Public Function GetDiscount(dblVolume As Double) As Double
Dim db As Database
Dim rec As DAO.Recordset
Set db = CurrentDb()
'Set recordset to the Discount Table
Set rec = db.OpenRecordset("Discount")
With rec
'Populate recordset
.MoveLast
.MoveFirst
'Loop through all records
Do While Not .EOF
If dblVolume >= .Fields("Start") And dblVolume < .Fields("End") Then
'Set discount to proper amount is above statement is true
GetDiscount = .Fields("Discount")
GoTo CleanExit
Else
'Default discount is 0
GetDiscount = 0
End If
.MoveNext
Loop
End With
'Clean up
CleanExit:
rec.Close
db.Close
Set rec = Nothing
Set db = Nothing
End Function

I think that will do the job.

Wow, thanks for the head start. I would have never found that on my
own. I was wondering if you could help me figure out more how to
implement this into my own DB.

The VolumeDiscount Text box should diplay a calculated answer
depending on what the gallons are. Right now, when the Gallons text
box loses focus it fills in the CORRECT discount rate into the Volume
Discount.

Is there a way to change my code so it will do this:

example: 150,000 gallons is .0025 discount rate ONLY on the gallons
above 100,000. So the text box would display 125 (.0025 * 50,000)

It gets more complicated the higher you go.

Example: 500,000 Gallons is .0050 discount rate ONLY on the gallons
above 400,000. Then 400,000 Gallons is .0025 discount rate ONLY on
the gallons above 100,000. So the text box would display 5750 (.0050 *
100,000 + .0025 * 300,000)

This is what I have so far, which works great, thank you.
__________________________________________________ ________
Private Sub GALLONS_LostFocus()

Dim db As Database
Dim rec As DAO.Recordset
Set db = CurrentDb()
'Set recordset to the Discount Table
Set rec = db.OpenRecordset("Discount")
With rec
'Populate recordset
.MoveLast
.MoveFirst
'Loop through all records
Do While Not .EOF
If GALLONS >= .Fields("Start") And GALLONS <= .Fields("End")
Then
'Set discount to proper amount is above statement is
true
VolumeDiscount = .Fields("Discount")
GoTo CleanExit
Else
'Default discount is 0
VolumeDiscount = 0
.MoveNext
Loop
End With
'Clean up
CleanExit:
rec.Close
db.Close
Set rec = Nothing
Set db = Nothing

End Sub
__________________________________________________

Is it getting complicated yet?

That's why Jesus created nerds.

No not yet!? ;-)

Alright, you'll have to modify this code as you did before to allow for form
controls, events, etc., but here it is:

Public Function GetDiscount(dblVolume As Double) As Double
Dim db As Database
Dim rec As DAO.Recordset
Dim dblRate() As Double
Dim dblBase() As Double
Dim dblTemp As Double
Dim i As Integer, j As Integer
Set db = CurrentDb()
'Set recordset to the Discount Table
Set rec = db.OpenRecordset("Discount")
i = 0
j = 0
With rec
'Populate recordset
.MoveLast
.MoveFirst
'Each record in this table represents a discount bracket (like a tax
'bracket.) Redimension arrays for all possible rates and bases
ReDim dblRate(0 To .RecordCount - 1)
ReDim dblBase(0 To .RecordCount - 1)
'Loop through all records
Do While Not .EOF
'Populate Arrays
dblRate(i) = .Fields("Discount")
dblBase(i) = GetBase(dblVolume, .Fields("Start"), .Fields("End"))
i = i + 1
.MoveNext
Loop
End With
'Calculate discount
For j = 0 To UBound(dblRate)
dblTemp = dblTemp + (dblBase(j) * dblRate(j))
Next
GetDiscount = dblTemp
'Clean up
CleanExit:
rec.Close
db.Close
Set rec = Nothing
Set db = Nothing
End Function

Private Function GetBase(ByVal dblVol As Double, _
ByVal dblstart As Double, ByVal dblEnd As Double) As Double
'This function computes the correct base upon which to apply rate.
GetBase = 0
Dim dblTemp As Double
'If Volume is below the beginning of the bracket, then base is zero
If dblVol < dblstart Then
Exit Function
End If
'If Volume is above the end of the bracket, then the base includes the
'entire range of the bracket.
If dblVol >= dblEnd Then
GetBase = dblEnd - dblstart
Exit Function
Else
'If Volume is somewhere in the middle of the braket's range, then the base
'is Volume minus the starting point of the bracket.
GetBase = dblVol - dblstart
End If
End Function

The above code is broken down into two functions. The first loops through
all discount brakets in the Discount table and assigns that Discount's rate
to an array containing all rates. Likewise, it assigns a base upon which to
apply the rate to an array containing all bases. The GetBase function calc
the base. See comments in that code for a detailed explanation of how it
works.

Johnny

Thanks Johnny, I'm closer, I have one more question then I promise to
go buy a VB book and read it cover to cover.

It looks like the code does everything it needs to. My problem is
triggering it to run. I've been using the lost focus of the Gallons
Text box.

And, I was wondering if you could write into the code as if it were in
my database. I'm having trouble understanding it written the way it
is. I'm just learning. For example, the code needs to calculate in
real time, on the same form are 2 text boxes. Gallons and
VolumeDiscount. When I type in a number in the gallons it should
display in the VolumeDiscount text box. I'm just confused as to what
is what in the code. I'm sure if I knew those two things I could
piece together the rest. Feel free just to put them right into the
code. I'll give you a dollar.

But yea, that's it. How to trigger it from normal motions of a form
and what is the source and what is the result.

This is getting too far into it. I should have just updated the code
whenever they updated the table... I could have snuck in after hours..
they'd never know. I think i'm babbling again..

Might I suggest Beginning Access VBA published by Wrox
(www.wrox.com)
I'm sure it's on Amazon.

I would not used the lost focus event to trigger the discount.
Instead, I
think the after update event is better because it fires when the data
in the
text box is changed and updated. With lost focus, ever time the use
tabs off
the text box, it fires. If you allow manual discounts aside from
standard
discounts already discussed, the user's input in the discount field
would be
erased every time the gallons text box lost focus.

Anyway, put the code we've already discussed into a standard module.
That is,
go to the VBE, click the Insert menu, and then click module. Name it
something
you'll remember.

For the purpose of the following example, I've created a form called
frmDiscount, which has two text boxes: txtVolume and txtDiscount.
Right click
on txtVolume, choose Build event, choose code builder. The default
event is
before update, change that to after update, and insert the following
code:

Private Sub txtVolume_AfterUpdate()
With Me
.txtDiscount = GetDiscount(.txtVolume.Value)
End With
End Sub

That's it. When the user updates the value in the txtVolume text box,
after
update fires and passes the value of txtVolume to the GetDiscount
function.
The rest is history.

Additional observences:
You should probably incorporate some simple data validation in
txtVolume's
Before Update event to ensure that values like -100 don't get passed
to the
GetDiscount function (although, GetDiscount defaults to 0). More
troubling is
the possibility that the user could type "What's Up" in txtVolume.
This will
throw and error. And as yet, it is unhandled. I can try to help you
with
error handling, but this is a big subject that may best be handled at
the
theoritical level (i.e. - reading a book on VB programming),
especially if you
have not had any previous exposure to it.

Johnny, I had VB in college, 3 years ago. I got a C- and squeezed
through. It was Access where I had shined. I had no idea they were
so closely knit until I took on this project.

I'll take care of validation and everything through access during my
cleanup before I deliver the database.

But I have to tell you it works, i don't know how it works, but it
works. I stare at it blank faced trying to find a signal flow... I'll
have to read up on the With and the Me stuff. But I thank you so much
for taking the time and helping out a fellow like myself. You are a
programming superhero.

One more quick question for you.

I think the code is skipping the very first record. Like, 100,001
should be .0025. 100,002 is coming up as .0025. Then it counts up
from there, as far as what is displayed. I think it's skipping the
very first record of every discount bracket. Because when I get high
numbers into the 3rd bracket it is .075 cents short. Meaning it
missed one .0025 and one .0050. This is no big deal, the client will
never get numbers that high, but I can't figure what is missing the >=
or the <= to include that first record.

Thanks again.