Unable to display values in ComboBox using SQL query from table

[Gurvinder]

I am trying to get 2 comboboxes working by using values from a single
table each combobox using one field each.

SQL Query which shows up fine in datasheet view but nothing shows up
in Form Combobox for either one.
<snip>
SELECT DISTINCT Source.dr FROM Source ORDER BY Source.dr;
</snip>

Once I get it working I would like to restrict the second combobox
using values picked from first one.

This is similar kind of code I have found examples of listed below.
Would thos work.
<snip>
' ***** Code Start *****
Private Sub cboPersonID_AfterUpdate()
Me.txtPersonName.Value = Me.cboPersonID.Column(1)
End Sub
' ***** Code End *****
</snip>

Thanks,
Gurvinder Singh

 

[Ken Sheridan]

Gurvinder:

For your first combo box its RowSourceType property should be Table/Query,
its BoundColumn property should be 1, its ColumnCount property should be 1
and its ColumnWidths property left blank. Its RowSource property can be the
name of the query or you can enter the SQL directly as the RowSource
property, i.e.

SELECT DISTINCT dr FROM Source ORDER BY dr;

Change its Name property to something meaningful like cboDr.

The second combo box should have its properties set up as the first but its
SQL property should reference the first combo box, e.g.

SELECT SomeField FROM SomeTable WHERE dr = Form!cboDr ORDER BY SomeField;

This assumes that the second table, SomeTable in my example, has a foreign
key field also called dr which references the dr field in the Source table.
Note that you can use the Form property to refer to the current form in this
case rather than referencing the form by name.

In the AfterUpdate event procedure of cboDR set the second combo ox to Null
and requery the second combo box with:

Me.YourSecondComboBox = Null
Me.YourSecondComboBox.Requery

substituting the real name of the second combo box of course.

If this is a bound form you should also requery the second combo box in the
form's Current event procedure, again with:

Me.YourSecondComboBox.Requery

In this case don't set it to Null, though.

Ken Sheridan
Stafford, England