HELP!!! Need to know basic VBA stuff

[jessedh]

Hi, I have query that needs to calc. commission based on a variety of factors. basically an employee has a commission structure based on the product. If they reach a certain level of sales, there structure changed. Since I have 5 different structures, and 50 employees, I knwo I have to do this in VBA. How do I open the db and tables I need? How would I go about having a counting function in VBA? Would an IF statement suffice, or do I need a SELECT...CASE, or something even more complex?

Thanks
Jesse

 

[jessedh]

I have tried this code:
Private Sub cmdListRecordsets_Click()

Dim dbs As Database
Dim rst As Recordset
Dim strDBName As String
Dim intCount As Integer
Dim strTable As String

strTable = "Orders"
strDBName = "D:\Documents\Northwind.mdb"
Set dbs = OpenDatabase(strDBName)
intCount = dbs.Recordsets.Count
Debug.Print intCount & _
" recordsets in current database (before opening a recordset)"
Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
intCount = dbs.Recordsets.Count
Debug.Print intCount & _
" recordsets in current database (after opening a recordset)"

For Each rst In dbs.Recordsets
Debug.Print "Open recordset: " & rst.Name
Next rst

End Sub


and I get an error of "User Defined Type Not Defined"

Help me!!

 

[Geekmomz]

Jesse,
I think the easiest way to do this is to have a table with your commission structure defined, (i.e., 1, 20%, 2, 17%, etc.), and have one of these options be part of the record for each sales rep. For historical purposes, you should also make the level part of the order record (at the macro level or the detail level, according to your company's structure), so that you can, for example, see how much a given rep made in commission each year for five years.

So, in other words, rep J is at commission level 3. An order is entered for him, with the default commission at level 3 (hide it to prevent changes). Then, to create a commission report, you pull together all orders by a rep, check the commission level for each order, check the percentage or flat rate per order, multiply, and sum.

 

[jessedh]

Thanks for your help. The problem is that it isn't a flat commission structure, but very dynamic in the fact that out of the 50 sales people there are about 7 "clusters" of structures each with its own set of parameters. New sales people are also allowed to design their own structure if they want.

I decided to go for a multi-level query, calling a function to compute actual commission due on each loan then bringing it all together in a report with grouping.

Thanks again
jesse