(As a beginner) I am trying to do 2 things

1) On a form, calculate a field (sales total) based on a lookup range – i.e. form has a sales price (data entry) and a total (calculated control that refers to a table - tblRate)
The tblRate has 4 fields - LowPrice, HighPrice, SalesRate, CommRate
The first 2 records are $0, $4.99, 30%, 40% and $5.00, $6.99, 40%, 50%
The idea being that if the sales price is $3 look it up in the tblRate (in this case - between $0 and $4.99 and multiply the sales price by 30% etc.)

Given that there may be many records in the tableRate, how do I code the calculation on the form without using a lot of code and with the ability to add more records easily in the tblRate.

2) Once the form is calculated – what’s the easiest way to write the major form fields to a new table (I know you don’t usually store calculated fields- but the rate changes frequently so I need to have a history transaction file) – how do you write the code to store the ‘output’ of the calc fields

Many thanks for your help

For the first thing, there's a couple of things that you can do. I am assuming a simple screen example where you have textboxes named SalesPrice, SalesRate, CommRate, SalesCalcComm, and CommCalcComm, as well as a button to retrieve the values called cmdRetrieveSalesCommRates for simplicity.

The first is to use a domain function named DLookup() which would work something like this:

Private Sub cmdRetrieveSalesCommRates_Click()

If Not IsNumeric(Me.txtSalesPrice) Then

   Me.txtSalesRate = DLookup("SalesRate", "tblRate", "LowPrice <= " & Me.txtSalesPrice & " AND HighPrice >= " & Me.txtSalesPrice)

   Me.txtSalesCalcComm = Me.txtSalesPrice * Me.txtSalesRate * .01

   Me.txtCommRate = DLookup("CommRate", "tblRate", "LowPrice <= " & Me.txtSalesPrice & " AND HighPrice >= " & Me.txtSalesPrice)

   Me.txtCommCalcComm = Me.txtSalesPrice * Me.txtCommRate * .01

Else

   MsgBox "Sale Price must be a numeric value"

End If

End Sub

You can use the DLookup() function for any field used like this on the screen. DLookup() has 3 arguments - the first is the name of the column that you want to retrieve data from, the second is the name of the table, and the third is the WHERE condition. In the example, you can see where I use string concatenation to build the WHERE condition using a mixture of literal values (LowPrice <=, etc) combined with the values retrieved from the screen. Keep in mind that you may want to first check that the txtSalesPrice field on the screen does not contain a numeric value before running the code.

The second way you can do this is to use ADO code, which could be more helpful in a situation where you need to retrieve multiple column values from a table - you can do something like this:

Private Sub cmdRetrieveSalesCommRates_Click()

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset

Dim strSQL As String

strSQL = "SELECT * FROM tblRate " & _
         "WHERE tblRate.LowPrice <= " & Me.txtSalesPrice & _
         " AND tblRate.HighPrice >= " & Me.txtSalesPrice

Set cnn = CurrentProject.Connection
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

If rst.BOF And rst.EOF Then
   MsgBox "Cannot locate rates for specified sales price"
   rst.Close
   cnn.Close
   Exit Sub
End If

Me.txtSalesRate = rst!SalesRate
Me.txtSalesCalcComm = Me.txtSalesPrice * Me.txtSalesRate * .01
Me.txtCommRate = rst!CommRate
Me.txtCommCalcComm = Me.txtSalesPrice * Me.txtCommRate * .01

rst.Close
cnn.Close

End Sub

Either of these examples would work - it just depends on your preference. If you do like DLookup(), you may want to look into the DCount() and DSum() functions as well which are both available in VBA.

As far as the history transaction file goes, I am assuming that you want to create a posting of each sale. I would highly recommend that along with other pertinent info that you need, that you store the date/time, price, and the rates in the transaction file. You can always recalculate the sale and commission amounts using the stored rates at that time. Grant you, your point on not storing calculated fields is a good one - in the case of keeping a history transaction log though, keeping a "snapshot" of the rates at that moment in time of the sale could actually be beneficial.

Hope that helps.

Warren :D