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

    Question Unanswered: function output for a combo box?

    Can anyone advice me on how to do the last part to get the output of the following half-finished function to populate a combo box on (the current..) a form?

    Thanks.

    ***************
    Public Function fBoughtStocks(ric As String, stock_name As String) As String
    On Error GoTo Err_fBoughtStocks

    Dim strSQL_bs As String
    Dim strCliNo As String

    strCliNo = Me.client_number

    strSQL_bs = "(SELECT DISTINCT tbl_buy.client_number, tbl_buy.ric, tbl_stocks.RIC 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.client_number, tbl_buy.ric"

    DoCmd.RunSQL strSQL_bs

    Exit_fBoughtStocks:
    Exit Function

    Err_fBoughtStocks:
    MsgBox Err.Description
    Resume Exit_fBoughtStocks

    End Function

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Try this-

    Public Function fBoughtStocks(ric As String, stock_name As String) As String
    On Error GoTo Err_fBoughtStocks

    Dim strCliNo As String

    strCliNo = Me.client_number

    Combox1.RowSource = "(SELECT DISTINCT tbl_buy.client_number, tbl_buy.ric, tbl_stocks.RIC 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.client_number, tbl_buy.ric"


    Exit_fBoughtStocks:
    Exit Function

    Err_fBoughtStocks:
    MsgBox Err.Description
    Resume Exit_fBoughtStocks

    End Function

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by Rockey
    Try this - Combox1.RowSource
    Thanks,.maybe it will work now. However, I am unsure about just how to use a function in conjunction with a combobox like this. Should I enter
    fBoughtStocks(ric, stock_name)
    as the actual row source for the combo box? That does not produce any results.

    As a control, when I enter
    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 ORDER BY tbl_buy.ric, tbl_buy.client_number

    directly as the row source, not using a function, and hence omitting the where clause, it populates the list, although then regardless of the client.

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Thumbs up

    Originally posted by kedaniel
    However, I am unsure about just how to use a function in conjunction with a combobox like this.
    Aiaiai, sooo focused on trying to use a function that I loose sight of the road...: Function not needed, I use the GotFocus event of the control, works just like I want it to, here's the working code, fyi:

    Private Sub cboRIC_GotFocus()
    On Error GoTo Err_cboRIC_GotFocus

    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)"

    cboRIC.Requery

    Exit_cboRIC_GotFocus:
    Exit Sub
    Err_cboRIC_GotFocus:
    MsgBox Err.Description
    Resume Exit_cboRIC_GotFocus
    End Sub

    Thanks for pointing it out, Rockey

    kedaniel

Posting Permissions

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