Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Unanswered: Populate list box with combo box

    Probably a beaten horse.

    I am unable to get my list box to populate from a combo box selection (MS Access 2003). I have a main form, which contains tabs. The list box is in one of the tabs; the combo box is not. I want to the user to make a selection from the combo box and have the list box automatically populate. I have tried requerying after update and it still will not populate.

    List box query:

    SELECT Query2.Date, Query2.Title, Query2.Category, Query2.Investigator
    FROM tblClaim INNER JOIN Query2 ON tblClaim.[Claim Number] = Query2.[Claim Number] WHERE (((tblClaim.[Claim Number])=[Forms]![frmClaim]![Combo144])) ORDER BY Query2.Date;

    If I remove the "s" in "Forms" then a combo box pops up. When I type an entry, it will populate in the list box with the correct information. However, it will not work automatically.

    Any suggestions?

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    On the basis that this is all in frmClaim form, then you could try something like this code in the after update event of the ComboBox
    Code:
    Dim sql As String
        
        If Me.Combo144 & "" = "" OR Me.Combo144 = 0 Then Exit Sub
        
        sql = "SELECT Query2.Date, Query2.Title, Query2.Category, Query2.Investigator"
        sql = sql & "FROM tblClaim INNER JOIN Query2 ON tblClaim.[Claim Number] = Query2.[Claim Number] "
        sql = sql & "WHERE (((tblClaim.[Claim Number])= " & Me.Combo144 & ")) "
        sql = sql & "ORDER BY Query2.Date"
        
        Me.YourListControl.RowSource = sql
        
        Me.YourListControl.Requery
    Note: this presupposed that [Claim Number] is a numeric field otherwise apostrophise are required round Me.Combo144 (ie. '" & Me.Combo144 & "' )

    HTH

    MTB

  3. #3
    Join Date
    Oct 2010
    Posts
    3
    Hi Mike,

    Thanks for the quick response. I entered what you sent me but no success. It appeared to recognized it because I did not get an error.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    You do not say what actually happens!?

    Try it without the IF stamen and see if that provokes any response.

    MTB

  5. #5
    Join Date
    Oct 2010
    Posts
    3
    Mike,

    Would you consider taking al ook at it if I send it to you?

    I figured out how to connect the list box to the combo box if the combo box has a "value" or from a query. However, it WILL NOT connect if the combo box is used to look up a record on a form, which is what I want it to do. I tried what I want using a subform in a datasheet view and it works fine but the columns in the datasheet can't be locked. Hence the reason that I want to use a list box.

    Dan

Posting Permissions

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