HAving Problems with Stored Procedure and Output Value

Need another set of eyes.  Trying to pass a single parameter to a stored procedure and get an output value to a VB code.  The procedure counts all occurences of equipment belonging to a repair center.  Currently I nothing returned.

Stored Procedure:

Alter Procedure SP_PM_ALL

                        @RC char(4), -- This is the input parameter.
                        @Count int output  -- This is the output parameter.


-- Get the priority, scheduled, and completion dates and
-- assign it to the output parameter.

SELECT  @Count = count (dbo.f_Equipment.FM_RISK_FACT)

FROM dbo.f_Equipment

WHERE (dbo.f_Equipment.FM_RC_CODE = @RC)

return @Count

My VB code is:

    Dim strRC As String
    Dim Priority As Double
    Dim Count As Integer
    Dim objCmd As New ADODB.Command
    Dim objCn  As ADODB.Connection
    Dim objRs As ADODB.Recordset

'Populate Date Range and Repair Center From Switchboard Form
    BeginDate = Forms![F_Switchboard]![Month]
    EndDate = Forms![F_Switchboard]![Years]
    RC = Forms![F_Switchboard]![RC]
    Set dbs = CurrentDb
'Count All Equipment
    Count = 0

  'Returns Total of All Records
    'Set the Command object properties
      With objCmd
        .CommandText = "SP_PM_ALL"
        .CommandType = adCmdStoredProc
        Set .ActiveConnection = objCn
        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 0)
        .Parameters.Append .CreateParameter("RC", adVarChar, adParamInput, 4, strRC)

        .Parameters.Append .CreateParameter("Count", adInteger, adParamOutput)
    End With
    With objRs
        .CursorLocation = adUseClient
        .Open objCmd, , adOpenDynamic, adLockReadOnly
        Set .ActiveConnection = Nothing
    End With
    'Execute the Command object
        Exec_SP_PM_All = objCmd.Parameters("RETURN_VALUE")
    'Dereference the database objects
        Set objCn = Nothing
        Set objCmd = Nothing
98    MsgBox " Count: " & Count

Suggestions, comments, punt?

Thanks...  Brent

Who is Participating?
Steve BinkConnect With a Mentor Commented:
Where is your table dbo.f_Equipment?  Is the field FM_RC_CODE a number or string data type?  Since we're not using strRC, we can use it for some debugging.  Make this change:

    MyCount = 0
    set db = CurrentDB
    strRC = "SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount " & _
                "FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = '" & _
                 Forms![F_Switchboard]![RC] & "'"
    Debug.Print strRC
    set rs = db.OpenRecordset(strRC)

As it runs, it will print out the SQL for this query to the Immediate window.  You can view this window by pressing CTRL-G or selecting "View | Immediate Window" from the menu bar.  Post the result here, and try pasting it in the query builder to see what records Access is returning.
Steve BinkCommented:
I don't know alot about your project, but is there a reason you want to send such a simple task over to a stored procedure?  Why not build the Count SQL in VB?  

       .CommandText = "SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount " & _
                                 "FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = '" & strRC & "'"
        .CommandType = adCmdText
        etc., etc.

bjwebb556Author Commented:
I am so new to this that I don't even know that I don't know.

I was planning on building even more complex queries later, however, this looks like somethin I might handle.

Q1: Is there a good reference.

Q2: How do I call the query, it looks incomplete.

Q3: What variable contains the value or how do I get it into a value that I can use in VB.  Is this the variable MyCount?  Me!CI1 = MyCount

Q4: I suspect I need to declare MyCount as well.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Steve BinkCommented:
A1:  I have a great reference called "Beginning Access 2002 VBA" from Wrox Publishing.  They make many other books in the series, and all the texts I've seen from them have been outstanding for beginners and techies alike.  Downside?  It doesn't explain ADO...in fact, it hardly even mentions it.  Not a big problem unless you are forced to use ADO.

A2-A4: I don't normally program using ADO in Access (or anything else, really), so I can't be of much help there unless I sat down and tested some code myself.  For DAO, however, here's some sample code that should do what you need.  If you prefer to use ADO, tell me and I'll take the time to convert this to what you need.  I am assuming you are connecting to currently open database.  If you need to connect to an external database, I will need to know the type of database and where it is physically located.

Public Function GetRecordCount as Integer
' I declared a function because you seem to need the count value.  If you don't need the return value outside of
' of this function, you can change this to a sub.

' Declare the variables.  Note I changed Count to MyCount.  strRC is unnecessary.
    Dim strRC As String
    Dim MyCount As Integer
    Dim db as DAO.Database
    Dim rs as DAO.Recordset

    ' Instead of using strRC, I'll insert the control's value directly into the SQL.
    ' strRC = Forms![F_Switchboard]![RC]
    ' Initialize MyCount, set up our database object and open the recordset.
    MyCount = 0
    Set db = CurrentDb
    set rs = db.OpenRecordset("SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount " & _
                  "FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = '" & _
                   Forms![F_Switchboard]![RC] & "'")

    ' The recordset now hold one field in one record.  Grab the count, then close everything down.
    MyCount = rs!MyCount
    ' This is where you could continue processing.  What do you want to do with the MyCount value?  Put it here!
    ' Otherwise, return the value, and ALWAYS clean up after yourself.  You return the value by:
    ' <FunctionName> = <Value>

    set rs = Nothing
    set db = Nothing
    GetRecordCount = MyCount

End Function

You would use this function like this:

Public Sub Example()
Dim x

x = GetRecordCount
msgbox "There are " & x & " records."

End Sub
bjwebb556Author Commented:
I attempted to incorporate the code.  I get the following message:

There are  records.

Is it not finding the data or is the select not passing the correct values.
bjwebb556Author Commented:
I appreciate the help

This is a adp project (MS Access frontend, SQL Backend) with the database located on a SQL Server. (I am trying to debug with a database located on my home machine, I have loaded a Microsoft product  to have it act like a server.  

The final project will reside on several individual machines and access the TMA satabase on the TMAServer\shstma.  THis will be a custom report writer tool (readonly).

I have several repair centers "RC" char(4)

SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = 'SLCH - PE'

Looks like it is passing the variable correctly.

Would it be smart to verify the table connection or will we get an error message.

Hmmm... can we modify this to print to the debug window dbo.f_Equipment.FM_RISK_FACT values?

bjwebb556Author Commented:
Hmmm...  I used the debugger to report the db value.  It is reports "nothing" at the time of the Select call.  Could it be that this value is not being passed?
Steve BinkCommented:
The table you are reading from is in a different database, so now we have to build a connection to it.  From here you have two options, linking and connection through code.

By linking, I mean simply linking the tables to the remote database.  That means you'll let Access handle the connection state and you just query the local link for the table, which then gets passed on to the server in which the real table resides.  It is fairly simple to do through the [ File | Get External Data... ] wizard from the menu bar.  Downside is the users can screw you up at this point.  If they mess with the linked tables, well...let's just say things can  go downhill very quickly

If you want to build the connection through code, that's pretty easy too.  Again, there are a few ways to accomplish this, and this is but one of them.  Since you will have multiple client machines with a single server, I'm showing an example for an ODBC connection.  It's been awhile since I've done this, so this may need some editing/fine tuning.  This becomes easier if you wanted to create a DSN on each client, but that is an unnecessary step, and can be pretty tiresome with many clients.

' Declare the local variables
Dim cn as DAO.Connection
Dim ws as DAO.Workspace
Dim rs as DAO.Recordset

' Create a workspace using ODBC.  The workspace name is "MyODBCWorkspace".
' It is being created by the username "admin" with a blank password.  Change this if necessary.
' You can change adUseODBC to adUseJet if you prefer to work with Jet instead.

set ws = DBEngine.CreateWorkspace("MyODBCWorkspace","admin","",adUseODBC)
' The next step is not necessary, but can be helpful in working with the workspace
DBEngine.Workspaces.Append ws

' Now open the connection.  "Conn1" will be the name of the connection.  dbDriverNoPrompt tells
' Windows to generate an error if the connection string is wrong (as opposed to popping up the
' drive window.  The missing parameter is for read-only or shared access, defaulting to shared.
' Last is the ODBC connection string...you will need to edit this to use your own information.
set cn = ws.OpenConnection("Conn1", dbDriverNoPrompt, , _
            "ODBC;Driver={SQL Server};Server=MyServerName;Database=myDatabaseName;" & _

' Now we can open the recordset!  This part should look familiar.  :)
strRC = "SELECT Count(dbo.f_Equipment.FM_RISK_FACT) as MyCount " & _
                "FROM dbo.f_Equipment WHERE dbo.f_Equipment.FM_RC_CODE = '" & _
                 Forms![F_Switchboard]![RC] & "'"
Debug.Print strRC
set rs = cn.OpenRecordset(strRC)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.