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

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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

    With Me.cboValueList
        .RowSource = "SELECT DISTINCT " & Me.cboFieldList & " FROM qtblContractors"
        .Requery
    End With
End Sub

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.

Reply | Quote