Results 1 to 13 of 13

Thread: combo box

  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: combo box

    I have question about combo box. In the table, i have ID and Name. When i use Combo44.value it will retrieve the ID. How can i retrieve the name. Use combo44.text??? I am not sure. Thanks.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Combo44.Column(1)

    The column property is 0 indexed, so your first column (ID) is 0, the second is 1, etc
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    I forget to post the code. Do you think i still can use Combo44.Column(1)?
    It doesn't work. Please look the following code. Thanks.


    Private Sub Combo44_AfterUpdate()
    Dim SQL As String

    SQL = "SELECT Categories FROM tblFundCategories WHERE Fund = "


    If Not IsNull(Me.Combo44.value) Then
    SQL = SQL & Me.Combo44.value

    Me.List2.RowSource = SQL
    End If

    Me.Refresh
    End Sub

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Since you said you were using the "text" portion of the combo box, you probably need to change it to this:

    Code:
    Private Sub Combo44_AfterUpdate()
    Dim SQL As String
    SQL = "SELECT Categories FROM tblFundCategories WHERE Fund = "
    
    If Not IsNull(Me!Combo44.value) Then
      SQL = SQL & "'" & Me!Combo44.Column(1) & "'"
      Me!List2.RowSource = SQL
    End If
    
    Me.Refresh
    End Sub
    Inspiration Through Fermentation

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Why build the SQL outside of the if statement? It looks like you only need if the field is not null. In which case I would put all of your SQL in the if statement. Else, I'd do like;
    Code:
    Private Sub Combo44_AfterUpdate()
    
    Dim strSQL As String
    strSQL = "SELECT Categories FROM tblFundCategories"
    
    If Not IsNull(Me!Combo44.value) Then
      strSQL = strSQL & " WHERE Fund = '" & Me!Combo44.Column(1) & "'"
      Me!List2.RowSource = strSQL 
    End If
    
    strSQL = strSQL & ";"
    
    Me.Refresh
    
    End Sub
    Though maybe I'm missing something, wouldn't be the first time.
    Me.Geek = True

  6. #6
    Join Date
    Mar 2004
    Posts
    660
    Sorry, it didn't show up the categories. It didn't work for me. Can you help again.

  7. #7
    Join Date
    Mar 2004
    Posts
    660
    In the form, i have combo box called fund. The row source is: SELECT tblFund.FundID, tblFund.Fund FROM tblFund;

    column count 2
    Bound column 2

    Hope this will let you understand what i did.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    and what does that have to do with combo44?
    Inspiration Through Fermentation

  9. #9
    Join Date
    Mar 2004
    Posts
    660
    combo44 is the name of Fund combo box

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try putting

    MSGBOX strSql
    before
    Me.Refresh

    What does the msgbox say when you Update the combo box?
    Inspiration Through Fermentation

  11. #11
    Join Date
    Mar 2004
    Posts
    660
    Thank you very much! I got it work. Before the where should have a space. Thanks again.

  12. #12
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by yyu
    combo44 is the name of Fund combo box
    Hi yyu,

    Just one little tip to help you in future DB design. It makes it much easier to understand and figure out controls if you name them a bit simpler. For your ComboBox for the field Fund, I personally would suggest naming it something like: cboFund

    That let's most know it is a ComboBox and the name of it is Fund. It helps others as well as yourself later on in maintenance. Hope this helps you some.

    have a nice one,
    Bud

  13. #13
    Join Date
    Mar 2004
    Posts
    660
    Thanks you for your suggestion. I have another question about the linked combo box.

    As in the data entry form, i have this fund linked combo box, when it update, it will update the categories based on that fund. In the code we have:

    Me.Refresh

    But in this data entry form. I have several fields are require to enter values. When i change the fund, it gave me error messag, fistname field required. Actually it is the next fielf after fund combo box. I think the Me.Refresh cause the problem. Whe it refresh, it will check the required field. Is that any way to fix it. Thanks.

    Private Sub cboFund_AfterUpdate()


    Dim strSQL As String
    strSQL = "SELECT Categories FROM tblFundCategories"

    If Not IsNull(Me!cboFund.Value) Then
    strSQL = strSQL & " WHERE Fund = '" & Me!cboFund.Column(1) & "'"
    Me!List2.RowSource = strSQL
    End If

    strSQL = strSQL & ";"

    Me.Refresh


    End Sub

Posting Permissions

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