VB6 and rules applied to running totals

Posted on 2000-05-16
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

bhess1 earned 100 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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

830 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