Total Values from Filter, not using a Query

[andycambo via AccessMonster.com]

Hi,

I am trying to get filtered information from a table and then calculate the
totals. The way I have it set up is as follows:

There are 2 tables, tblMatters and tblTimeRecord. Each Matter has a MatterID
and each Time Record has a FK of tMatterID, this is how the two tables are
linked.

I have 2 buttons on my form. One opens up a new time record form which the
user enters information in and it automatically filters for the records with
the same Matter ID as the user was viewing. This is so when the user is
entering a new Time Record they can only view Time Records for the Matter
they were viewing.

The code I have for this is:

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", , , "tMatterID = " & theMatterID

End Sub

Now my second button. I want it to calculate the fields in Time Record, such
as tTravelTime, tAttendanceTime and view them in a form. I have managed to
get a query to bring up such information but would prefer, if possible, to be
able to do it in SQL. At the moment I have this code:

Private Sub CurrentCosts_Click()
Dim totalTravelTime As Integer
Dim totalWaitingTIme As Integer

totalTravelTime = "(SELECT Sum(tblTimeRecord.tTravelTime)FROM tblTimeRecord
WHERE tblTimeRecord.tMatterID = Me.MatterID;"

totalWaitingTime = "(SELECT Sum(tblTimeRecord.tWaitinglTime)FROM
tblTimeRecord WHERE tblTimeRecord.tMatterID = Me.MatterID;"

DoCmd.OpenForm "frmCurrentCosts"

End Sub

Am I going along the right track with this method? I am currently getting a
run-time 13 error (type mis-match). Once I have the correct values for the
variables I want to insert these into a form which the user can view.

Any help would be appreciated.

Thanks,
Andy.

 

[andycambo via AccessMonster.com]

I've finally cracked it.

totalTravelTime = DSum("[tTravelTime]", "tblTimeRecord", "tMatterID =" &
theFileNumber)

Now to figure how to get the results onto the form.

Any suggestions?

Thanks
Andy.

 

[John W. Vinson]

I've finally cracked it.

totalTravelTime = DSum("[tTravelTime]", "tblTimeRecord", "tMatterID =" &
theFileNumber)

Now to figure how to get the results onto the form.

Any suggestions?

Set the Control Source of a textbox on the form to

= DSum("[tTravelTime]", "tblTimeRecord", "tMatterID =" & [FileNumber])

assuming you have a control named FileNumber on the form... and you'll need no
code at all.