create a variable in access adp from "select count" statement

  • Hello,

    All I am trying to do is return a single number into a variable in order to check if there are records in a table that match certain criteria. My query works fine in SQL Server, but it's not filling the variable in access. Can you help me? - Rob

  • Can you post the query?

  • Dim cmdSQL As ADODB.Command

    Set cmdSQL = New ADODB.Command

    cmdSQL.CommandText = "select count(OrderQty) from RFQOrderQtys where RFQNumber = " & Me.RFQNumber

    cmdSQL.CommandType = adCmdText

    Dim rsSQL As ADODB.Recordset

    Set rsSQL = New ADODB.Recordset

    rsSQL.Open cmdSQL

    Dim intCount as int

    intCount = rsSQL.RecordCount

    MsgBox (intCount & " Records.")

  • What value do you get in variable intCount? If it returns -1 then it means the recordset object is opened in dynamic mode. Change the CursorType property of recordset object to adOpenStatic.

    --Ramesh


  • I think I am going about this all wrong. I am developing in a data project(.adp). Do I still need to create a connection in order to return a recordset, or is the connection already established via the properties of the project? This is beyond the scope of my normal responsibilities and I am very new to this. I just want to check if there is data in a table based on an ID number and return a true/false. It sounds easy to me, but I have no idea how to do it. Is this even the right place to ask for this help? - Rob

    I changed to this and it worked. Sorry if this post was not in the right forum. - Rob

    Dim rsSQL As New ADODB.Recordset

    Dim strSQL As String

    strSQL = "select OrderQty from RFQOrderQtys where RFQNumber = " & Me.RFQNumber

    rsSQL.Open strSQL, CurrentProject.Connection

    If rsSQL.BOF And rsSQL.EOF Then

    MsgBox "You Need to enter an order Qty!", vbCritical, "ERROR"

    Exit Sub

    Else

    End If

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply