recordset and combo box issues

[phill86]

Hi,

Bare with me this is going to take some explaining and any help is much appreciated

I have a form that stores details of sessions which include start/end date start/end time these sessions are associated with a project so you will have ten sessions associated to a project for example within the form i also have a session type dropdown so there are several different types of sessions that the user can chose from... so the important fields are as follows

ProjID
StartDate
EndDate
StartTime
EndTime
SessTypeID

What i need to do is is sum the total number of hours of a session type with the project ID

I have a query which points to the ProjectID and the sessionTypeID see below

SELECT T_ActiveSession.SessionID, T_ActiveSession.ProjID, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_ActiveSession.SessTypeID, DateDiff("n",[startdate]+[starttime],[enddate]+[endtime]-[break]-[downtime]) AS Expr1, T_ActiveSession.SessComplete, T_ActiveSession.Downtime, T_ActiveSession.Break, T_ActiveSession.StartDate, T_ActiveSession.EndDate
FROM T_ActiveSession
GROUP BY T_ActiveSession.SessionID, T_ActiveSession.ProjID, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_ActiveSession.SessTypeID, T_ActiveSession.SessComplete, T_ActiveSession.Downtime, T_ActiveSession.Break, T_ActiveSession.StartDate, T_ActiveSession.EndDate
HAVING (((T_ActiveSession.ProjID)=SubFormFieldValue("F_ClientDetails","SF_Session","ProjID")) AND ((T_ActiveSession.SessTypeID)=SubFormFieldValue("F_ClientDetails","SF_Session","sesstypeid")))
ORDER BY T_ActiveSession.SessTypeID;
 

I then run a recordset on that query which totals up the number of hours that have been completed for session type1 within project1 for example

so there could be 10 records for session type 1 on various different days and session lengths within project1

the recordset below totals the number of hours worked on a session type 1
and then writes that value to a table.

The problem i have is when a user goes back to a previously saved record and tries to change the session type from session type1 to session type2 for example it sums the total number of hours for session type2 i need to be able to subtract the hours from session type1 in the table

hope my ramblings make sense

regards Phill

 
Dim dbsCurrent As Database
    Dim dbsLinkedData As Database
    Dim rstQAssignedHrsSum As dao.Recordset
    Dim rstTblAssignHrs As dao.Recordset
    Dim strSeekProjID As String
    Dim strSeekSessionID As String
    Dim nullvalue As Variant
    Dim rstExpr1 As String
    Dim SumOfHrs As Integer
    Dim HrsStore As Integer
    Dim HrsTotal As Integer
    Dim rstProjID As Integer
    Dim rstSessTypeID As Integer
    Dim rstCompletedHrs As Variant
    Dim varbookmark As Variant
    Dim TableAssignhrs As Integer
    Dim SessTypeStore As Integer
 
 
 
  'sets quer recordset and the the table where the data is going to be written
 
    Set dbsCurrent = CurrentDb
    Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ExampleAccess2000")
    Set rstTblAssignHrs = _
        dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
    Set rstQAssignedHrsSum = _
      dbsCurrent.OpenRecordset("Q_SFormTotalHrs1", dbOpenDynaset)
 
 
    strSeekProjID = Forms![F_ClientDetails]![SF_Session].Form![ProjID]
    strSeekSessionID = Forms![F_ClientDetails]![SF_Session].Form![SessTypeID]
 
With rstQAssignedHrsSum
 
Do
' loops round and totals up hours ready to be written to table
Do Until rstQAssignedHrsSum.EOF
 
 
        rstExpr1 = rstQAssignedHrsSum!expr1
        HrsStore = HrsStore + rstExpr1
 
        .MoveNext
        HrsTotal = HrsTotal + HrsStore
        HrsStore = 0
 
Loop
 
Loop Until rstQAssignedHrsSum.EOF
 
        .Close
 
End With
 
'loops table until record is found and writes data to field
With rstTblAssignHrs
 
    Do
 
    Do Until rstTblAssignHrs.EOF
 
 
        rstProjID = rstTblAssignHrs!projectID
        rstSessTypeID = rstTblAssignHrs!SessTypeID
 
 
     If rstProjID = strSeekProjID And rstSessTypeID = strSeekSessionID Then
 
        rstTblAssignHrs.Edit
 
 
        rstTblAssignHrs!completedHrs = HrsTotal
        rstTblAssignHrs.Update
 
        End If
 
        .MoveNext
 
    Loop
 
Loop Until rstTblAssignHrs.EOF
 
        .Close
 
    End With
'Exit_Command16_Click:
 
 Exit Function
'Err_Command16_Click:
 
  '  MsgBox Err.Description
  '  Resume Exit_Command16_Click
Exit Function
End Function
 

[DonRayner]

the recordset below totals the number of hours worked on a session type 1
and then writes that value to a table.

Is there a paticular reason that you need to write this data to a table? You would be much better off using a query to calculate the totals as you require them. That way each time the query is run you would have a freshly updated set of totals.

[NeoPa]

This seems a very straightforward problem (not sure why such complex explanation).

This illustrates the very common problem you get when you fail to use the database as a database and try to make it into a transaction processing system.

This issue is often referred to as Normalisation. Check out Normalisation and Table structures for a fuller explanation.

[NeoPa]

By the way, it's possible to use Access as a transaction processing system. It's actually quite flexible that way, but as it's an RDBMS, and it gives you all that goes with that, you'd need a fairly good reason to want to go what is essentially the more difficult route.

[phill86]

Hi Neopa and DonRayner

I have found a solution

Thanks for your help