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?
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
MsgBox "Well, you cannot sell more than you have... ;-)", vbOKOnly, "Cannot sell this amount."
' opening Balance form for this client on this line...
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...
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.
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.
Function fRICbal(strCLIno As String, strStockID As String) As Double
Dim varBalance As Variant
If strCLIno <> "" And strStockID <> "" Then
varBalance = DLookup( _
"client_number=" & strCLIno & " AND stock_id=" & strStockID)
If (Not IsNull(varBalance)) Then
fRICbal = varBalance