Function Help

[James]

Ok, I have a function(see below) that prompts for a username, and a date, it
then runs a function based on that user and date. I want to create a report
that will show the info for ALL the users. So I've made a query that
selects all distinct usernames for a date you enter. How would i adjust
this function to get it to use the usernames from my query instead of
prompting for a username, and how would i get it to print out all the data?

Thanks



Function AddTimes()
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
Dim Time1 As Date
Dim time2 As Date
Dim TimeAccum As Integer

'InputUserName = InputBox("Please enter a username")
'InputDate = InputBox("Please enter a Date")

Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("Qry_AddTimes")
qdfParmQry("Please Enter a Username:") = ClientUserName

' or try this alternate method
' to pass the parameter

qdfParmQry![Please Enter a Date:] = logDate
Set rs = qdfParmQry.OpenRecordset()






'Set db = CurrentDb
'Set rs = db.OpenRecordset("WebProxyLog")

rs.MoveNext
Do
rs.MovePrevious
Time1 = rs!logTime
rs.MoveNext
time2 = rs!logTime
If DateDiff("n", Time1, time2) < 5 Then
TimeAccum = TimeAccum + DateDiff("n", Time1, time2)
End If
rs.MoveNext
Loop Until rs.EOF
AddTimes = TimeAccum
Debug.Print AddTimes
End Function

 

[Wayne Morgan]

Since you're also wanting to print this out, why not use a Report? Base your
report on the query, supplying the appropriate date when prompted. The
report would automatically give you all records returned by the query. The
function could be in a calculated textbox on the report. You could keep the
value of the previous record in a Static variable in the Format event of the
Detail section of the report. If you have multiple records for one person,
you could group on the person then have a running sum in the detail section
set to Over Group.

 

[James]

Ok, and I would do this how?

Since you're also wanting to print this out, why not use a Report? Base your
report on the query, supplying the appropriate date when prompted. The
report would automatically give you all records returned by the query. The
function could be in a calculated textbox on the report. You could keep the
value of the previous record in a Static variable in the Format event of the
Detail section of the report. If you have multiple records for one person,
you could group on the person then have a running sum in the detail section
set to Over Group.

--
Wayne Morgan
MS Access MVP

Ok, I have a function(see below) that prompts for a username, and a

date,

it
then runs a function based on that user and date. I want to create a report
that will show the info for ALL the users. So I've made a query that
selects all distinct usernames for a date you enter. How would i adjust
this function to get it to use the usernames from my query instead of
prompting for a username, and how would i get it to print out all the data?

Thanks



Function AddTimes()
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
Dim Time1 As Date
Dim time2 As Date
Dim TimeAccum As Integer

'InputUserName = InputBox("Please enter a username")
'InputDate = InputBox("Please enter a Date")

Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("Qry_AddTimes")
qdfParmQry("Please Enter a Username:") = ClientUserName

' or try this alternate method
' to pass the parameter

qdfParmQry![Please Enter a Date:] = logDate
Set rs = qdfParmQry.OpenRecordset()






'Set db = CurrentDb
'Set rs = db.OpenRecordset("WebProxyLog")

rs.MoveNext
Do
rs.MovePrevious
Time1 = rs!logTime
rs.MoveNext
time2 = rs!logTime
If DateDiff("n", Time1, time2) < 5 Then
TimeAccum = TimeAccum + DateDiff("n", Time1, time2)
End If
rs.MoveNext
Loop Until rs.EOF
AddTimes = TimeAccum
Debug.Print AddTimes
End Function

 

[Wayne Morgan]

Go to the Modules tab of the database window. If you don't have a module
there that you would like to add this too, create a new one and copy/paste
in the function. Save the module. Give it a descriptive name of your choise
or use the default. If you change the name, don't give it the name of
anything else, including the name of procedures in the module.

Once you have done that, you can call this function in your query instead of
calling Replace as you currently are. Just replace the word "Replace" with
"MyReplace" in the query. If you are using more than the first three
parameters for the Replace function, MyReplace will need to be modified
slightly.

 

[Wayne Morgan]

Disregard my previous reply, I was thinking about a different message.

 

[Wayne Morgan]

Ok, let's start with the parameters. Since you're wanting to print everyone,
the user name shouldn't matter. For the date, there are a couple of options.
You could simply leave the parameter in the query and when the report uses
the query, you'll be prompted for the date. You could also point the
parameter to a control on a form for the value or set the parameter in code,
provided you then open a recordset on the querydef in that code. Part of
this will depend on where "logDate" comes from in your code. All code
examples are untested.

To use the code you have add the following:
Dim prm As DAO.Parameter
'After the Set qdfParmQry statement add
Set prm = qdf.Parameters![Please Enter a Date:]
prm = logDate
Set rs = qdfParmQry.OpenRecordset()

Before your rs.MoveNext (the first one before the Do loop) you need to check
to see if there are any records and if so, are there at least 2. Replace
this rs.MoveNext with the following:
If Not (rs.EOF And rs.BOF) Then
rs.MoveLast 'fully populate the recordset
If rs.RecordCount < 2 Then
MsgBox "This won't work"
'Do what you need to fix the problem
End If
rs.MoveFirst
rs.MoveNext
Else
Msgbox "There are no records"
'Do what you need to fix the problem
End If
---------------------------------------------------

Report Method:
Without seeing everything you're doing, I'm doing a lot of guessing here,
but hope it'll give you some ideas.
In the Declaration section of the report's code (where it says Option
Compare Database):
Dim intRecordCount as Integer

In the Format event of the Detail Section:
Static dtePreviousTime As Date
Me.txtPreviousTime = dtePreviousTime
'Skip the first record
If intRecordCount = 0 Then
Me.txtPreviousTime = 0
intRecordCount = intRecordCount + 1
End If
dtePreviousTime = [TimeField]
End Sub

In the Format section of the Group Header:
intRecordCount = 0

The textbox in the detail section will probably be hidden (Visible = No).
You would then add another textbox to show the calculation.
Control Source of Textbox:
=IIf(txtPreviousTime = 0, 0, DateDiff("n", txtPreviousTime, [TimeField]))
Set the Running Sum of this textbox to Over All or Over Group, as desired.
You could also have two textboxes, one set to Over Group and one set to Over
All.

In the Grouping and Sorting option of the Report, place the field for the
Person's Name, sort Ascending, and tell it Yes for a Group Header. Place a
textbox in the group header on the report and bind it to the field for the
person's name.