By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

recordset and combo box issues

100+
P: 121
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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM T_ActiveSession
  3. 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
  4. HAVING (((T_ActiveSession.ProjID)=SubFormFieldValue("F_ClientDetails","SF_Session","ProjID")) AND ((T_ActiveSession.SessTypeID)=SubFormFieldValue("F_ClientDetails","SF_Session","sesstypeid")))
  5. ORDER BY T_ActiveSession.SessTypeID;
  6.  
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim dbsCurrent As Database
  3.     Dim dbsLinkedData As Database
  4.     Dim rstQAssignedHrsSum As dao.Recordset
  5.     Dim rstTblAssignHrs As dao.Recordset
  6.     Dim strSeekProjID As String
  7.     Dim strSeekSessionID As String
  8.     Dim nullvalue As Variant
  9.     Dim rstExpr1 As String
  10.     Dim SumOfHrs As Integer
  11.     Dim HrsStore As Integer
  12.     Dim HrsTotal As Integer
  13.     Dim rstProjID As Integer
  14.     Dim rstSessTypeID As Integer
  15.     Dim rstCompletedHrs As Variant
  16.     Dim varbookmark As Variant
  17.     Dim TableAssignhrs As Integer
  18.     Dim SessTypeStore As Integer
  19.  
  20.  
  21.  
  22.   'sets quer recordset and the the table where the data is going to be written
  23.  
  24.     Set dbsCurrent = CurrentDb
  25.     Set dbsLinkedData = DBEngine.OpenDatabase("C:\Database\ClientBooking\ExampleAccess2000")
  26.     Set rstTblAssignHrs = _
  27.         dbsLinkedData.OpenRecordset("T_AssignHours", dbOpenTable)
  28.     Set rstQAssignedHrsSum = _
  29.       dbsCurrent.OpenRecordset("Q_SFormTotalHrs1", dbOpenDynaset)
  30.  
  31.  
  32.     strSeekProjID = Forms![F_ClientDetails]![SF_Session].Form![ProjID]
  33.     strSeekSessionID = Forms![F_ClientDetails]![SF_Session].Form![SessTypeID]
  34.  
  35. With rstQAssignedHrsSum
  36.  
  37. Do
  38. ' loops round and totals up hours ready to be written to table
  39. Do Until rstQAssignedHrsSum.EOF
  40.  
  41.  
  42.         rstExpr1 = rstQAssignedHrsSum!expr1
  43.         HrsStore = HrsStore + rstExpr1
  44.  
  45.         .MoveNext
  46.         HrsTotal = HrsTotal + HrsStore
  47.         HrsStore = 0
  48.  
  49. Loop
  50.  
  51. Loop Until rstQAssignedHrsSum.EOF
  52.  
  53.         .Close
  54.  
  55. End With
  56.  
  57. 'loops table until record is found and writes data to field
  58. With rstTblAssignHrs
  59.  
  60.     Do
  61.  
  62.     Do Until rstTblAssignHrs.EOF
  63.  
  64.  
  65.         rstProjID = rstTblAssignHrs!projectID
  66.         rstSessTypeID = rstTblAssignHrs!SessTypeID
  67.  
  68.  
  69.      If rstProjID = strSeekProjID And rstSessTypeID = strSeekSessionID Then
  70.  
  71.         rstTblAssignHrs.Edit
  72.  
  73.  
  74.         rstTblAssignHrs!completedHrs = HrsTotal
  75.         rstTblAssignHrs.Update
  76.  
  77.         End If
  78.  
  79.         .MoveNext
  80.  
  81.     Loop
  82.  
  83. Loop Until rstTblAssignHrs.EOF
  84.  
  85.         .Close
  86.  
  87.     End With
  88. 'Exit_Command16_Click:
  89.  
  90.  Exit Function
  91. 'Err_Command16_Click:
  92.  
  93.   '  MsgBox Err.Description
  94.   '  Resume Exit_Command16_Click
  95. Exit Function
  96. End Function
  97.  
Jan 23 '09 #1
Share this Question
Share on Google+
4 Replies


DonRayner
Expert 100+
P: 489
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.
Jan 23 '09 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 25 '09 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 25 '09 #4

100+
P: 121
Hi Neopa and DonRayner

I have found a solution

Thanks for your help
Jan 29 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.