Auto Update Form Field Value Based on Subform

[ImASig]

I am making a database for real estate companies to keep track of their agents. Their commission rate is determined by the money value of their sales per year. The commision rate must autoupdate when they hit a certain point.

What I have done is create a crosstab query totaling their yearly sales (agents are the row, year is the column) and binded that to a subform in the agents form. What I would like is for possibly some VB code to look at the total in the subform for the current year and update the commission rate when it hits certain levels. I thought about doing this through an update query but there are some catches that make things more complicated. For one the rate resets to the lowest value at the beginning of a new year. Another is that if you hit a certain number in two consecutive years then you start the new year at a higher rate. For these reasons I think VB would be the way to go.

I'm not asking for the entire code to be written for me. I'm not a great programmer but I know the basics. If someone could just give me an example of how to write some vb code to update a form field based on a subform and how to deal with the date syntax in my examples then I think I can weed through the rest. Also if I'm going about this the wrong way then please speak up as well.

 

[ImASig]

I'm fighting through this as best I can on my own. I have managed to write some VB code that updates the commission to the correct value for the current year. One problem is I'm having to do it as a click on a command button instead of autoupdate. I couldn't seem to get this code to work in the beforeupdate or afterupdate sections of the comrate form field. The other problem is I had to hardcode the year. I would like to use the year as a variable for the current year. I have made a string variable using DatePart that gets the current year but Access doesn't recognize the variable as a valid field name. Below is my code with the DatePart variable in as the field name. It works fine with the current year [2004] in the place of [ThisYear]. Any suggestions?

Dim ThisYear As String
ThisYear = DatePart("yyyy", date)

If Me.YEARLY_SALES_HISTORY_subform1.Form![ThisYear] >= 53500 Then

Me.comrate.Value = 0.76

ElseIf Me.YEARLY_SALES_HISTORY_subform1.Form!["ThisYear"] >= 32000 Then

Me.comrate.Value = 0.66

ElseIf Me.YEARLY_SALES_HISTORY_subform1.Form!["ThisYear"] >= 17000 Then

Me.comrate.Value = 0.57

Else

Me.comrate.Value = 0.54

End If

 

[willir]

ImASig

I would suggest creating a table for your commission rates. This would allow you to change the rules and rates without having to change your code. A guess...

tblRates
RateID - pk
RateYear - allows review of past rates
LowLimit
HiLimit
Rate

You can then use DLookUp or SQL statement or a query or code or a function call to find the correct rate...

Example: [tt]
SELECT Rate from tblRates
Where RateYear = Year(date())
And ThisYear >= LowLimit
And ThisYear < HiLimit [/tt]

For example, as part of your code...

Dim strWhere as String, decRate as Decimal

strWhere = "RateYear = " & Year(date()) & " And " & _
& ThisYear & " >= LowLimit And " & ThisYear " < HiLimit "

decRate = DlookUp("[Rate]", "tblRates", strWhere)

Also, if you are having problems with a cross tab query, you can also use an array, and load the specific values seperately.

Dim dbs as DAO.database, rst as DAO.recordset
Dim decSales as Decimal, intCount as Interger
Dim strWhere as String
Dim MyReport () as Varient
'0 - sales rep
'1 - sales
'2 - rate

set dbs = currentdb()

set rst = dbs.openrecordset("YourSalesRepTable")

intCount = rst.RecordCount
ReDim MyReport (3, intCount)

With rst
    .movefirst
    intCount = 0
    While not .EOF
       MyReport(intCount, 0) = !LastName & ", " & !FirstName
       strWhere = "RepID = " & !RepID
       MyReport(intCount, 1) = DSum("[ThisYear]", "SalesTable", strWhere)
       strWhere = "RateYear = " & Year(date()) & " And " & _
       & ThisYear & " >= LowLimit And " & ThisYear " < HiLimit "
       MyReport(intCount, 2) = DlookUp("[Rate]", "tblRates", strWhere)

       intCount = intCount + 1
       decSales = decSales + MyReport(intCount, 1)

       .movenext
    Loop

End With

rst.Close

'Then write your array to a temp table, or display on a form.

I have tried to provide several examples / approaches in this snippet of code.

Richard

 

[PHV]

Have you tried this ?
If Me.YEARLY_SALES_HISTORY_subform1.Form.Controls(ThisYear)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244