Check IsNull on a function in module

[skyline666]

Hi,

Im using Access 2003 Project and SQL Server 2005. I have a connection screen which lets the user pick a platform from a combo box and then all projects for that platform are displayed in a list box. The user then chooses that project and presses a command button which opens up a new form called RiskRegister (RR), with a subform called Risk. This subform displays all risks for that platform/project. The PlatformID taken from the connection screen is also put into a text box (called txtPlatform) on the RR form.

I have made a report that displays the top 20 risks for the chosen platform, and opens up from a command button, this all works fine. What I want to do is have a message box telling the user if there are no risks for that platform, and so doesn't open up a blank report. The PlatformID is stored in the Risk table for each risk.

I have a function in a module which runs a stored producedure. The stored procedure selects everything from Risk where a PlatformID will be equal to the textbox on the RR form.

Here is the SP:

alter PROCEDURE [dbo].[usp_RiskSelectPlatform]
@PlatformID int
AS
set nocount on
SELECT * FROM Risk WHERE PlatformID = @PlatformID
set nocount off

Here is the function:

Function RiskSelectPlatform(lPlatformID As Long)

    Set CurCon = Application.CurrentProject.Connection
    Set oCommand = New ADODB.Command
    Set oCommand.ActiveConnection = CurCon
    oCommand.CommandText = "usp_RiskSelectPlatform"
    oCommand.CommandType = adCmdStoredProc
    With oCommand
        .Parameters.Append .CreateParameter("@MitigationActionID", adInteger, adParamInput, 4, lPlatformID)
    End With
    oCommand.Execute
End Function

In the on click event on the report command button, I tried this code with no success (i get a syntax error):

if isnull(RiskSelectPlatform me.txtPlatform) then
    stDocName = "Top20ByPlatform"
    DoCmd.OpenReport stDocName, acViewPreview, , , , Me.txtPlatform
Else
    MsgBox "No Risks to show for this Platform", vbOKOnly, "No Risks"
    Exit Sub
End If

I also tried this Forms!Risk!PlatformID = Me.txtPlatform in place of the isnull part, but get the error "Access cant find the form 'Risk'...". What I am trying to do is to check if the PlatformID in me.txtPlatform is equal to any of the records in the table Risk, NOT the subform Risk on the RR.

Any help will be appreciated on how to do this.

Many Thanks,

Andrew

 

[CaptainD]

Shouldn't this read:

If isnull(RiskSelectPlatform(me.txtPlatform)) Then

 

[skyline666]

Hi CaptainD,

Yes it should, thanks, also:

.Parameters.Append .CreateParameter("@MitigationActionID", adInteger, adParamInput, 4, lPlatformID)

should of been:

.Parameters.Append .CreateParameter("@ProjectID", adInteger, adParamInput, 4, lPlatformID)

Thanks again,

Andrew