I’ve hit a problem with my database that I am creating.
At this current stage I have a table called Matters, each Matter has a MatterID which is being used to link the Matter table and the Time Record table together.
I have two buttons on the Matter form which run SQL code. One opens a time record form and inserts the currently viewed MatterID and FileNumber which the user then can add the necessary information for recording time. It works but if possible I would like to put a filter on so that it only shows Time Records for the same MatterID. Here is my code I have now
Private Sub TimeRecord_Click()
Dim theFileNumber As String
Dim theMatterID As Long
Dim strSQL As String
theFileNumber = Me.mFileNumber
theMatterID = Me.MatterID
strSQL = "INSERT INTO tblTimeRecord"
strSQL = strSQL & "(tMatterID, tFileNUmber)"
strSQL = strSQL & "VALUES ('" & theMatterID & "', '" & theFileNumber & "');"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "frmTimeRecord", acNormal
DoCmd.GoToRecord acDataForm, "frmTimeRecord", acLast
At the moment when the Time Record form open it is going to be showing all Time Recorded records. Is it possible to just show the ones with the same MatterID as the currently viewed matter using theMatterID variable?
My second problem is… I want to take all the Time Recorded records for the currently viewed matter and total them together. Such as total travel time, total attendance time etc. Again I will need to filter using the MatterID (from tblTimeRecord) but not totally sure how I am going to do this.
I have this code at the moment but I’m not sure if I am going the right way with this. If someone could point me in the right direction that would be great.
Private Sub CurrentCosts_Click()
Dim fMatterID As Long
Dim SQLStr As String
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1
SQLStr = "SELECT mAttendanceTime, mTravelTime"
SQLStr = SQLStr & " FROM tblTimeRecord"
SQLStr = SQLStr & " WHERE ([tMatterID] = fMatterID)"
I would like to open a form named frmCurrentCosts which will show the total of times for the Matter which I can then do the calulations so I get get the current costs. Would the best way to do it be, create a temporary table, insert all the times from the filtered matter, total the times together and do calculations then delete temporary table?
I would base the second form on a totals query that summed the fields you wanted and grouped on the Matter, and of course restricted to the specific Matter (you can use the technique above for that as well).
This method will not work for my second problem. This is because the button is on frmMatters (which uses tblMatters) but the information I need is on a table called tblTimeRecord. So although this method works if I open the Time Record Form and do my calculations for that Matter on there, if I open a different form which has no table (as all I want to do is take the values from the Time Record table, filtered by the MatterID, and calculate the totals which do not need to be saved) it does not work as there is no table to filter.... all the information is on tblTimeRecord.
Like I said, I would base that form on a totals query that grouped on MatterID and summed the relevant fields (based on tblTimeRecord). Then when you open it using that technique it will display the totals for that MatterID.
Thanks for your advice. I've had a look around the internet and found some examples. I've manged to create a query that calculates the totals for each field.
Now I would like to do this same but in SQL so that I can give selected variables a value which I can then use to populate a form.
At the moment I have this code:
Private Sub CurrentCosts_Click()
Dim totalTravelTime As Integer
totalTravelTime = ("SELECT Sum(tblTimeRecord.tTravelTime) FROM tblTimeRecord WHERE tblTimeRecord.tMatterID = Me.MatterID;")
Here I am tying to get the total sum of tTravelTime where the Time Records Matter ID (the FK - tMatterID) is the same as the currently viewed Matter (Me.MatterID). I then want to use the variables to populate the form frmCurrentCosts.
I am fairly new to SQL language and I still unsure of the correct syntax to use. At the moment I am getting a type mis-match error (run-time 13).
Any help on the above select statement will be much appreciated.