Go Premium for a chance to win a PS4. Enter to Win


VB6 and rules applied to running totals

Posted on 2000-05-16
Medium Priority
Last Modified: 2010-05-02
A complex problem (well I think soanyway) involving date sensitive variables
I need to collect information on16 or so events of 9 different types. Each event type has a value associated with its duration ('points' as integer in my code) but its the running totals that are a problem.
For each of the 9 types of event there are a different set of rules governing how the totals are allocated depending on dates. These are

Type1            1 point per hour of event, no daily or yearly limits
Type2            1 point per hour of event, 5 max per day, no yearly limit
Type2a            As above but need to be distinguishable from Type2
Type3            5 points per event, max 10 per year
Type3a            As above but need to be distinguishable from Type3
Type4            Variable points per event, max 10 per year
Type5            1 point per hour of event, no day limit, max 10 per year
Type6            1 point per hour of event, 5 points per day, 10 points per year
Type7            Variable per event, max 50 points per event, no date/time limits

I have designed and written the code for the GUI and also the code to successfully generate the points per event (ie I can generate the points without problem providing I don't have to apply any of the above rules). I intend to store information relating to each event in a database (using data controls) for later printing out a summary, which I think I can do although I haven't actually done it yet. What I cannot do at all is conceptualize how I approach the problem of the keeping the running totals so that all the Type rules are applied correctly and in such a way that I can manipulate them for report purposes at a later stage. Despite much bleeding from the ears and furrowing of the brow I'm no further and I have been at this for weeks now.
All help very, very gratefully received!!

Neill (Newbie in case it wasn't obvious!)
Question by:babysnake
  • 2
  • 2
LVL 28

Expert Comment

ID: 2814284
well it appears each type will be governed by different functions to determine its values.  what i would do is create a single function that took, as a parameter, the type to be used.  for example,

Private Function  myFunc(aType As Integer)
    Select Case aType
        Case 1
            'do calculations based on this type
        Case 2
            'do calculations based on this type
    End Select
End Function

you could even write separate functions for each type that would be called to perform the calculations and call them from your select case structure, e.g.,

Case 1
    Call ProcessType1

where ProcessType1 was a function you wrote to calculate the values based on that particular type
LVL 32

Accepted Solution

Brendt Hess earned 400 total points
ID: 2814560
I'd approach it similarly to how AzraSound did, but would put all of the conditionals into the function that logged the event (recorded the points).

A lot of the actual methodology depends on how you are recording the information.  My example here is based on these assumptions:

SQL Server or Access DB
ADO connection
Record layout similar to this:

A lot of simplifying has gone into the code as well - assumes aConn is an already open ADO connection to the logging DB, for example.

Function LogEvent(UserID as Integer, EventID as Integer, Optional Points as Integer) As Boolean

Dim aRS as ADODB.RecordSet
Dim aCmd as New ADODB.Command
Dim Pts As Integer
Dim sSQL As String
Dim dtNow As Date
Dim OkToAdd as Boolean

' If points is other than 1, pass the value
If IsMissing(Points) Then
   Pts = 1
   Pts = Points
End If

dtNow = Now

sSQL = "INSERT INTO Log_Table VALUES (" &  UserID & ", " & EventID & ", '" & Format(dtNow, "mm/dd/yyyy hh:nn:ss") & "', " & Pts & ")"

Select Case EventID
Case 1, 7
      OKToAdd = True

Case 2, 20   ' 20 = your Type 2a
   ' I am assuming that you do not know if the event has happened yet this hour.
   Set aRS = New ADODB.RecordSet    ' Note: This could be better done with Stored Procedures
   aRS.CursorLocation = adUseClient
   aRS.Open "SELECT Sum(EventPoints) as Pts FROM Log_Table WHERE UserID = " & UserID & " AND EventID = " & EventID & " AND EventDateTime > '" & Format(DateAdd("h",-1,dtNow),"mm/dd/yyyy hh:nn:ss") & "'", aConn, adOpenForwardOnly, adLockReadOnly

   If aRS.RecordCount = 0 Then  ' Nothing this hour
      ' Assuming that your period is the last 24 hours from now, not just in the current calendar day.
      aRS.Open "SELECT Sum(EventPoints) as Pts FROM Log_Table WHERE UserID = " & UserID & " AND EventID = " & EventID & " AND EventDateTime > '" & Format(DateAdd("d",-1,dtNow),"mm/dd/yyyy hh:nn:ss") & "'", aConn, adOpenForwardOnly, adLockReadOnly
      If aRS.RecordCount <> 0 Then  ' Data Exists
         OKToAdd = aRS!Pts < 5
         OKToAdd = True
      End If
      OKToAdd = False
   End If
   Set aRS = Nothing

Case 3, 30   ' 30 = your Type 3a

   'Safety check on points - force to 5
   Pts = 5

   Set aRS = New ADODB.RecordSet    ' Note: This could be better done with Stored Procedures
   aRS.CursorLocation = adUseClient
   ' Assuming that it is ten points per calendar year, not in the last year from now
   aRS.Open "SELECT Sum(EventPoints) as Pts FROM Log_Table WHERE UserID = " & UserID & " AND EventID = " & EventID & " AND EventDateTime >= '01/01/" & Format(Year(dtNow),"0000") & "'", aConn, adOpenForwardOnly, adLockReadOnly

   If aRS.RecordCount = 0 Then  ' Nothing this year
      OKToAdd = True
      OKToAdd = aRS!Pts < 10
   End If
   Set aRS = Nothing

' I leave cases 4, 5, 6 as an excersize for the student :)

End Select

If OKToAdd Then
   With aCmd
      .ActiveConnection = aConn

      .CommandText = sSQL
      .CommandType = adCmdText
   End With
End If        

LogEvent = OKToAdd
End Function

Author Comment

ID: 2814826
Thanks for the incredibly swift help!

To bhess1

I'm connecting to an Access97 db(cped.db; data in table 'data') using database controls (is this DAO, ADO??). I know all the events have happened and when as the data is added retrospectively.

The year runs from April to April always (the cycle over which we submit our returns) and as you correctly surmise I'm also collecting data along the lines of Date, Type, Length, Points and some free text notes (fields in 'data')

Thanks again

LVL 32

Expert Comment

by:Brendt Hess
ID: 2815097
Well, the controls could be either DAO or ADO, so I don't know... If you aren't specifying a provider name (just an access table name), then it's DAO.

If you are using the Enterprise or Professional editions, and can creat Database and Recordset variables in your code, then I can help you with coding this.  If not, well, I've actually never worked with a datacontrol.  Others could help far more than I.

Author Comment

ID: 2815148
Thanks again

I'm using the learning edition (no surprise there then) but I have experimented before with recordsets and database variables in code and got them to work. As far as I can see the datacontrol merely saves you having to initialise the dbengine, workspace etc as the properties of the datacontrol do the same thing. You can then, it seems, use Recordset variables to assign values to specific fields in the table identified by the datacontrol.

So if I can use the code example you gave to work out how to apply the Rules, then I can update the db. It was always applying the rules (particularly involving the date function) that was going to lead to tears.

Anyway I'll crack on with what I've got and see how I do. I might get back if I get horribly stuck.....

Many, many thanks

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question