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

    Question Unanswered: Passing on the result of a SELECT statement in a function?

    I have a Function that appearently works from within a subform.

    However, when I compile the code, I get error messages about invalid use of me. I reckon it is not strange since there is no form referred to in a public function, but the code RUNS, it works... Me.Parent is non-existent from the function when not accessed/triggered from the form I guess.

    Then, after for example commenting the code, just to check for more compile problems, it also complains about
    cboRIC.RowSource ... - variable not defined, If I declare cboRIC an error message about invalid qualifyer (cboRIC).

    How should I change the reference , should I use Forms!xxx or something like that?

    I must pass the string in a different way I guess, any advice?


    Calling the function from a combo box's row source property like this:
    =fBoughtStocks(RICNO, RIC_name, CID)


    Public Function fBoughtStocks(RICNO As String, RIC_name As String, CID As String) As String
    On Error GoTo Err_fBoughtStocks

    Dim strCliNo As String

    strCliNo = Me.Parent.cboSelectClient

    cboRIC.RowSource = "(SELECT DISTINCT tbl_buy.ric, tbl_stocks.RIC, tbl_buy.client_number FROM tbl_buy INNER JOIN tbl_stocks ON tbl_buy.ric = tbl_stocks.ID_stock WHERE tbl_buy.client_number =" & strCliNo & " ORDER BY tbl_buy.ric, tbl_buy.client_number)"

    Exit_fBoughtStocks:
    Exit Function

    Err_fBoughtStocks:
    MsgBox Err.Description
    Resume Exit_fBoughtStocks

    End Function

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if strCliNo means string then
    WHERE tbl_buy.client_number =" & strCliNo & " ORDER

    needs to read:

    WHERE tbl_buy.client_number ='" & strCliNo & "' ORDER
    or
    WHERE tbl_buy.client_number =""" & strCliNo & """ ORDER
    whichever of the two equivalent syntaxes you prefer.

    izy
    currently using SS 2008R2

Posting Permissions

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