Need help printing a report based on user input 1

[GeeKrOb]

I just converted my mdb to adp, I use a form that consists of checkboxes to determine which employees' reports print. When the user chooses the employees and click "Print Reports", a series of message boxes appear asking for a begin date and an end date. My question is, what would be the easiest way to get my report to work as it did before I converted it to adp? I tried creating the stored procedure:

ALTER PROCEDURE dbo.PhoneLogRptSp
@begin_date datetime,
@end_date datetime,
@who nvarchar(25)
AS SELECT dbo.PHONECALL.callerAcctNum, dbo.PHONECALL.callDate, dbo.PHONECALL.callTime, dbo.PHONECALL.callerLName, dbo.PHONECALL.callerFName,
dbo.PHONECALL.callerAddress, dbo.PHONECALL.CALLERCITY, dbo.PHONECALL.callerState, dbo.PHONECALL.callerZip,
dbo.PHONECALL.callerHomePh, dbo.PHONECALL.callerWorkPh, dbo.PHONECALL.callerCompany, dbo.PHONECALL.callReason,
dbo.PHONECALL.OPID, dbo.Employees.empName
FROM dbo.PHONECALL INNER JOIN
dbo.Call_Employee_Link ON dbo.PHONECALL.callID = dbo.Call_Employee_Link.callID INNER JOIN
dbo.Employees ON dbo.Call_Employee_Link.empName = dbo.Employees.empName
WHERE (dbo.PHONECALL.callDate BETWEEN @begin_date AND @end_date) AND (dbo.Employees.empName LIKE @who)

This works if I disregard my form with the checkboxes and just enter the begin date, end date and who manually, but I have to enter all the data for each employee seperately.

I tried passing in perameters via code but it doesn't seem to work:

Dim begin_date As Date
Dim end_date As Date
Dim who As String

begin_date = Format(InputBox("Enter beginning date of log to print (mm/dd/yyyy).", "Begin Report Date", DATE), "medium date")
end_date = Format(InputBox("Enter ending date of log to print (mm/dd/yyyy).", "Begin Report Date", DATE), "medium date")
who = "some_employee"

Dim db As Database, rst As ADODB.Recordset


Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim param1 As ADODB.Parameter, param2 As ADODB.Parameter, param3 As ADODB.Parameter

' Connect
Set cnn = CurrentProject.Connection

Set cmd.ActiveConnection = cnn

' Set up a command object for the stored procedure.
cmd.CommandText = "PhoneLogRptSp"
cmd.CommandType = adCmdStoredProc

Set param1 = cmd.CreateParameter("@begin_date", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = begin_date
Set param2 = cmd.CreateParameter("@end_date", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = end_date
Set param3 = cmd.CreateParameter("@who", adVarChar, adParamInput, 25)
cmd.Parameters.Append param3
param3.Value = who

' Execute command to run stored procedure
cmd.Execute

DoCmd.OpenReport reportname:="Phone Log Report", view:=acViewPreview

I know this still doesn't utilize the checkbox form, but once I get this working I can then easily put the results of the checkboxes into the variables.

 

[RoyVidar]

Here's one thing to try, in the on open event of the report ...

[tt]Dim begin_date As Date
Dim end_date As Date
Dim who As String

begin_date = Format(InputBox("Enter beginning date of log to print (mm/dd/yyyy).", "Begin Report Date", DATE), "medium date")
end_date = Format(InputBox("Enter ending date of log to print (mm/dd/yyyy).", "Begin Report Date", DATE), "medium date")
who = "some_employee"

me.recordsource = "exec PhoneLogRptSp '" & format$(begin_date, "yyyy-mm-dd") & "', '" & format$(end_date, "yyyy-mm-dd") & "', '" & who & "'"[/tt]

But alter the last part of the SP to:

[tt]... (dbo.Employees.empName LIKE '%' + @who + '%')[/tt]

I don't much like the input boxes though, I'd preferred picking those values from a form in stead (for executing the command object, though it shouldn't be necessary here, I'm not sure I remember correctly, but I I'm not sure how the adDBDate thingie works, perhaps try adDate in stead?

Another little tip - just at the last couple of pages of posts here in this forum there are some interesting threads/tips on passing paremeters to SP's - have a search and a read;-)

Also - when working with the checboxes, you'll most likely run into some amusements. They are probably bit fields (0 and 1) while checkboxes are perhaps 0 and -1. Haven't worked much with it, but I wouldn't be surprised if you get to use the CBool() function ...

Roy-Vidar

 

[GeeKrOb]

Thanks Roy, worked perfect. Now I can take your other information into consideration and get this thing working.

 

[GeeKrOb]

Ok, I got rid of the user input boxes and now use a date picker for the date which works great, but now I am having a problem creating the report for mulitple employees at one time. Say we have 100 employees and I want the report to print for 80 of the employees based on certain criteria. So instead of @who being one employee I want it to contain multiples, for example:
instead of the WHERE clause looking like...

WHERE (dbo.PHONECALL.callDate BETWEEN @begin_date AND @end_date) AND (dbo.Employees.empName LIKE '%' + @Who + '%')

I want it to look like...

WHERE (dbo.PHONECALL.callDate BETWEEN @begin_date AND @end_date) AND (dbo.Employees.empName LIKE 'emp1' OR dbo.Employees.empName LIKE 'emp2')

I thought about creating a string in vb to pass to the sp that looked like...

dim spSQL as String
spSQL = "emp1' OR dbo.Employees.empName LIKE 'emp2"
Me.RecordSource = "exec PhoneLogRptSp '" & Format$(begin_date, "yyyy-mm-dd") & "', '" & Format$(end_date, "yyyy-mm-dd") & "', '" & spSQL & "'"

and setting my sp as...

WHERE (dbo.PHONECALL.callDate BETWEEN @begin_date AND @end_date) AND (dbo.Employees.empName LIKE @spSQL)

but it doesn't work, and suggestions?

 

[cmmrfrds]

You can pursue the string method if you want, but here is an alternate method of using a temporary table in sql server. Temp tables are created by putting a # in front of the table name. i.e. #myemployees. A temp table will hang around on the same connection until the connection is closed or the table is dropped. On the connection before executing your SP, send some sql to create the #temp table.

Something like.
cmd.execute "CREATE TABLE #Myemployees (myid varchar(20)) "
set up a loop to insert employee names. Then.
cmd.execute "yourSP"

In the SP join the your table to the #myemployees table on the myid.

Drop the temp table either in the SP or on the connection with a separate statement.
cmd.execute "Drop #myemployees"

I like this method since it is open-ended and flexible.