Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Combo List column not populating when chosen (XP SP2)

    I have a table (tbl_target) with two columns, Target & Product, both primary required keys. (Products are on another table and are validated in tbl_target) I have a form based on the main table (FAQ) which has both Target and Product. When the user moves to the Target field I want one of two things to happen. First choice = since the form already knows the product, have the combo box show only the appropriate targets for the product chosen based on the combinations in tbl_target. I couldn't figure out how to do that as I'm quite the newbie. Second choice = change the default combo box to display both fields from tbl_target so the user can choose the correct one. This works as long as Target is the first column, but I want Product to be so they can quickly find just the appropriate targets for the product. No matter what "Bound Column" property value I choose (0,1,2) it returns the first column from the query which populates the combo box.

    So, two questions...
    1) What am I doing wrong?
    2) How can I make what I consider the better choice, choice 1, work?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo List column not populating when chosen (XP SP2)

    You'll have to write a bit of code for choice 1.
    Open the form in design view.
    Select View/Code (or click the Code button on the toolbar).
    Enter the following code; I have used cboTarget as the name of the combo box bound to Target and txtProduct as the name of the text box bound to Product; substitute the names you have used on your form. I have assumed that Product is a text field.

    <font face="Georgia">Private Sub UpdateCombo()
    cboTarget.RowSource = "SELECT * FROM tbl_Target WHERE Product = " & Chr(34) & Me.txtProduct & Chr(34)
    End Sub</font face=georgia>

    If Product is numeric, you can omit & Chr(34) twice (they represent quotes around the value of txtProduct; they are not needed for a numeric value).

    For the second choice, you would have to create a query based on tbl_Target that selects Product as first field and Target as second field. and order it by Product. Use this query as row source of the combo box.

    Switch back to Access.
    Click in the form window, but outside the form itself (you may have to scroll or enlarge the window to do that); this selects the Form object.
    Activate the Properties window (View/Properties).
    Click the Events tab.
    Click in the On Current event.
    Select Event Procedure from the dropdown list.
    Click the Build button (the button with ... to the right of the event.
    Type UpdateCombo, so that you see

    <font face="Georgia">Private Sub Form_Current()
    UpdateCombo
    End Sub</font face=georgia>

    Switch back to Access.
    Select the text box bound to the Product field.
    Click in the AfterUpdate event of this text box (in the Properties window)
    Select Event Procedure from the dropdown list.
    Click the Build button (the button with ... to the right of the event.
    Type UpdateCombo, so that you see

    <font face="Georgia">Private Sub txtProduct_AfterUpdate()
    UpdateCombo
    End Sub</font face=georgia>

    Switch back to Access.
    Close and save the form.

  3. #3
    Lounger
    Join Date
    Dec 2002
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo List column not populating when chosen (XP SP2)

    Bryan,

    You can do the first by adding one line of codes. As you said, the form knows the product, so create a module for OnCurrent and write the following:

    Private Sub Form_Current()

    comboTarget.RowSource = "SELECT Target, Product FROM tbl_target WHERE [Product] = '" & Me.Product & "'"

    End Sub

    Be careful about WHERE clause. I am assuming Product is a string, so the value, Me.Product, should be wrapped by single quotation marks (which may not be clear on this page). If Product is a number, the line should be

    comboTarget.RowSource = "SELECT Target, Product FROM tbl_target WHERE [Product] = " & Me.Product

    About your 2nd question, if you just want to show Product in the combo box, go to Column Widths and set it to

    0"; 2"

    so it will show Product instead of Target, but the value of the combo is still Target (first column).

    Shlomo

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: Combo List column not populating when chosen (XP SP2)

    Thanks very much! This works exactly as I'd hoped. The detailed instructions really helped since I know nothing <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Is Me. the value in memory for a column? What does the Private Sub Form_Current() do?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: Combo List column not populating when chosen (XP SP2)

    Thanks for the help. It worked.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo List column not populating when chosen (XP SP2)

    You can refer to controls on the form (or report) that is running the code in several ways:

    ControlName
    Me!ControlName
    Me.ControlName

    Me returns a form object that represents the form running the code. The advantage of using Me. is that when you type the period, Access will display a list of all properties and methods of the form, including all controls.

    The OnCurrent event occurs each time another record gets the focus, that is, if you move to another existing record or create a new record. The Form_Current() procedure is the event handler for this form - it gets executed whenever another record gets the focus. This ensures that the row source of the combo box is updated when you navigate through records.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: Combo List column not populating when chosen (XP SP2)

    Cool. Thanks. This is fun!

    Bryan

Posting Permissions

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