Solved

VB6 and rules applied to running totals

Posted on 2000-05-16
5
158 Views
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!)
0
Comment
Question by:babysnake
  • 2
  • 2
5 Comments
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
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
        etc....
    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
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 100 total points
Comment Utility
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:
   UserID
   EventID
   EventDateTime
   EventPoints

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
Else
   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
      aRS.Close
      ' 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
      Else
         OKToAdd = True
      End If
   Else
      OKToAdd = False
   End If
   aRS.Close
   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
   Else
      OKToAdd = aRS!Pts < 10
   End If
   aRS.Close
   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
      .Execute
   End With
End If        

LogEvent = OKToAdd
End Function
0
 

Author Comment

by:babysnake
Comment Utility
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

0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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.
0
 

Author Comment

by:babysnake
Comment Utility
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now