Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: Function returning the select statement instead of the resulting value...

    See the code below... - at this line:
    If strRICbal < Me.txtamount_of_shares_sold Then...

    - I cannot seem to get the resulting one-cell-only-value
    from the balance select query into the strRICbal...
    When debugging, the "value" for strRICbal at that point
    is the very Select statement, not its result, hence
    I get a Type Mismatch error.

    This is pretty basic I think, but my poor brain does not
    get it. How do I get the function to return the resulting number/value into strRICbal instead of the select statement, so that I can do the comparison?

    D.

    I have the following sub:

    Dim strCLIno As String
    Dim strStock As String
    Dim strStockID As String
    Dim strBalance As String
    strCLIno = Me.Parent.ID_client
    strStockID = Me.cboStock
    ' return the
    strStock = "(SELECT tbl_stocks.stock FROM tbl_stocks " _
    & "WHERE (tbl_stocks.id_stock = " & strStockID & "))"

    strRICbal = fRICbal(strCLIno, strRICno)

    If strRICbal < Me.txtamount_of_shares_sold Then
    ' do something here if I only can get the balance compared

    Else:
    Me.txtamount_of_shares_sold.SetFocus
    MsgBox "Well, you cannot sell more than you have... ;-)", vbOKOnly, "Cannot sell this amount."
    ' opening Balance form for this client on this line...
    Exit Sub
    End If


    There is a global variable in the main module:
    Dim gRICbal as String

    And the function looks like this:
    Function fRICbal(strCLIno As String, strRICno As String) As String
    On Error GoTo Err_fRICbal

    Dim strSQL As String
    strSQL = "(SELECT qry_balance.Balance " _
    & "FROM qry_balance " _
    & "WHERE (((qry_balance.client_number)= " & strCLIno & ") AND ((qry_balance.stock_id)= " & strStockID & ")))"

    gRICbal = strSQL
    fRICbal = gRICbal

    ' and then some error handling at the end.

    *********
    The function actually returns the Select statement as the string, not the resulting value of the select statement...

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi kedaniel,

    I noticed a couple of things that looked a little odd.

    1) You send the string strRICno to the function fRICbal, but strRICno is never set to anything, and it's not declared as a variable in your subroutine.

    2) strRICbal isn't declared as a variable. Its name makes me think it should contain a string, but I think you intend for it to contain a value.

    3) Function fRICbal is declared as a string, but it should be declared as a double, since you're expecting to have a value returned.

    4) strRICbal is an argument in your function fRICbal, but the function doesn't ever refer to that variable. Instead, fRICbal refers to strStockID, which is never declared anywhere within the function.

    Try this:
    • Replace your function fRICbal with the following.
    • Declare the variable that stores the results of fRICbal as a double.
    • Be sure to pass the correct values to fRICbal.
    Code:
    Function fRICbal(strCLIno As String, strStockID As String) As Double
       Dim varBalance As Variant
    
       If strCLIno <> "" And strStockID <> "" Then
          varBalance = DLookup( _
             "Balance", _
             "qry_balance", _
             "client_number=" & strCLIno & " AND stock_id=" & strStockID)
          If (Not IsNull(varBalance)) Then
             fRICbal = varBalance
          End If
       End If
    
    End Function
    Hope that helps,
    JT

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Thanks! :-)

    When I run the code, I get the following error message:
    "Run-time error 2001":
    You canceled the previous operation."

    What does that mean?

    The debugger marks these lines:

    varBalance = DLookup( _
    "Balance", _
    "qry_balance", _
    "client_number=" & strCLIno & " AND stock_id=" & strStockID)

    (varBalance is empty at that moment)

    D.

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179

    hmmm...
    I have no answers, just more questions...

    Are client_number and stock_id both numeric?
    What are the values of strCLIno and strStockID when the error occurs?
    What is the SQL in the query qry_balance?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •