Pass parameter w/o using report input parameter?

[sk1hotpepr]

I have a report that accepts one parameter with a stored procedure for it's record source. I have a frame with four values - "Past Clients", "Current Clients", "Prospective Clients" and "Show All Clients". The first three work fine but I can't seem to get Show All Clients to work with the report and I think it's because it's taking the value of the frame on the form rather than the NULL value I pass to the stored procedure. Is there a way to push the input parameter value into the report on open so I don't need to point it to the frame? Here's my VBA code:

If (Me.Frame26.Value = 4) Then    'rfp followup - default is show all
    cmd.Parameters.Append cmd.CreateParameter("@prospect", adVarChar, adParamInput, 50, Null)
Else
    strTmp = Me.Frame26.Value
    cmd.Parameters.Append cmd.CreateParameter("@prospect", adVarChar, adParamInput, 50, strTmp)
End If

Set rst = cmd.Execute
DoCmd.OpenReport "rptProsType", acViewPreview
DoCmd.Close acForm, "SDClients"
rst.Close
Set rst = Nothing
Set cmd = Nothing

Thanks!

 

[dhookom]

When working with SQL Server stored procedures, I just create pass-through queries and use DAO code to modify the SQL properties. Then use the p-t query as the record source for your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sk1hotpepr]

Duane,

How would this work using ADO??

 

[dhookom]

I don't use ADO so I can't tell you. I try to find the easiest, most flexible solutions and stick with them. DAO seems to work best for me.

It isn't clear how you are using your rst within your code/report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sk1hotpepr]

I learned to late, so ADO is what I've been using. I did some checking on ADO - some more clear than others.

'this is the other part of the code from VBA.
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "spProsType"
cmd.CommandType = adCmdStoredProc

My stored procedure is set up like this:

ALTER PROCEDURE dbo.spProsType
(@prospect integer)
AS SELECT     dbo.SchoolDistrict.SDist, dbo.SchoolDistrict.City, dbo.SchoolDistrict.State, dbo.SchoolDistrict.County, dbo.SchoolDistrict.Phone, 
                      dbo.ClientType.ClntType, dbo.SchoolDistrict.CurrPros
FROM         dbo.SchoolDistrict LEFT OUTER JOIN
                      dbo.ClientType ON dbo.SchoolDistrict.CurrPros = dbo.ClientType.ClntID
WHERE     (dbo.SchoolDistrict.CurrPros = @prospect OR @prospect IS NULL)

It's something with the way the report is getting the parameter and I can't figure out why - especially because I have a similar setup in another form that works fine.

 

[dhookom]

Again, I don't use this type of code but I believe forms can use a record set as a datasource while reports can't. This may have changed in more recent versions.

In your stored procedure, @prospect is data typed as integer so I'm not sure how @prospect IS NULL can ever be true.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sk1hotpepr]

I did some more searching and found this thread:

thread702-940780 Option group for parameter

it solved my problem. It seems funny to have to send parameters this way, but it works. Thanks Duane for your help!