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
Expand|Select|Wrap|Line Numbers
- 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;
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
Expand|Select|Wrap|Line Numbers
- 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