Cascading Combo Boxes

[JCW]

Looking for help on cascading combo boxes

I have two tables: tclient and tmatter. The tables share a common key based on the clientId.

I have a form with two combo boxes cmbMatterNo for the matter name and cmbClientSearch for the client name. When I select an item from the cmbMatterNo it runs a query on the tmatter table that returns the clientID and the matterNo, with the clientID being the bound column. I want to synch the cmbClientName based on the selection of the matterNo in the cmbMatterNo combobox,

I have written the following code:

cmbClientName.RowSource = "SELECT tClients.ClientID, tClients.ClientName " & _


"FROM tClients " & _
"WHERE tClients.ClientID = '" & cmbMatterNo.Value & "' " & _
"ORDER BY tClients.ClientName;"

When I select the cmbMatterNo item it returns the clientID as the cmbMatterNo.Value, but the cmbClientName box nevertheless is empty and does not show any of client name from the tclients table.

Any help on what I am doing wrong would be greatly appreciated.

Thanks.

[June7]

Set cmbClientName properties:

ColumnCount: 2

ColumnWidths: 0";1.5"


Is cmbMatterNo an UNBOUND (no ControlSource) combobox?

[JCW]

cmbClientName is already set to 2 columns, with widths of 0";4", and cmbMatterNo is unbound.

(Also my reference in the thread to cmbClientSearch was a mistake. It should be cmbClientName).

[JCW]

Solved the problem. Deleted the ' ... ' from '" & Me!cboClientName.Value & "' and the problem resolved. Not sure I understand why, but am happy nevertheless.

[June7]

You deleted the apostrophes? Ooops! I should have noticed that.

When constructing SQL in VBA, apostrophes are need to delimit text values for text data type fields. ClientID is apparently a number field so no apostrophes needed. Criteria for date/time type fields would use # delimiter.

However, it is not necessary to set the RowSource with VBA. Can just have the SQL in the RowSource property:

SELECT ClientID, ClientName FROM tClients WHERE ClientID = [cmbMatterNo] ORDER BY ClientName;

Then the VBA just needs to be:

Me.cmbClientName.Requery