Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem Populating Combo? (A2k (9.0.4402) SR-1)

    Problem Populating Combo?

    A2k (9.0.4402) SR-1

    I have a Criteria Form with two combo boxes

    FieldCombo ValueCombo
    ---------------------- ------------------
    strBusinessCity Albany
    Buffalo
    Newark
    Rochester

    strBusinessState NJ
    NY

    The operator selects a table field name from the FieldCombo box.

    I then want to populate the ValueCombo box with the distinct values of the field selected (FieldCombo) as illustrated with the examples above.

    Having a problem building the Row Source in the Query Grid for ValueCombo

    This is what I have, the following does not return any rows:

    SELECT DISTINCT [Forms]![frmCriteriaContractor]![FieldCombo] AS Expr1 FROM qrytblContractor;

    The following works. I

  2. #2
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem Populating Combo? (A2k (9.0.4402) SR-1)

    Can you clarify some things? Are you saying you want the user to select a state from combo "A", which will then dynamically populate combo "B" with the appropriate cities? Seems like, for one thing, you need a "where" clause in your sql. Something like : Select city from tablex where state = ComboA (the value in ComboA that the user has selected)

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Problem Populating Combo? (A2k (9.0.4402) SR-1)

    Hi John,
    If I understand you correctly, you need some code to do what you want. Something like this in the AfterUpdate event of your field list combo should do it:
    Private Sub cboFieldList_AfterUpdate()
    <pre> With Me.cboValueList
    .RowSource = "SELECT DISTINCT " & Me.cboFieldList & " FROM qtblContractors"
    .Requery
    End With
    End Sub
    </pre>

    where cboFieldList is the name of your fieldnames combo, cboValueList is the name of your other combo and qtblContractors is the name of your query/table.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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