Cascading Combo Boxes - Trouble with Locking Behavior

[JCW]

I am still having problems with my cascading combo boxes.



I have 3 boxes: cboClientName, cboMatterName, and cboMatterID. CboClientName looks to a client table with client names which each have a unique clientID. CboMatterName and cboMatterID are drawn from a matters table which associates matters names and unique matter ids with the client's id.

I have the following code to select the client name in a combobox and then limit a second combobox to only those matters associated with that client id. I have written the following code to do so in the private sub cboClientName_AfterUpdate():


cboMatterName.RowSource = "SELECT ClientID, MatterName FROM tMatters WHERE ClientID = [cboClientName] ORDER BY MatterName;"
Me.cboMatterName.Requery

The second combobox (cboMatterName) populates perfectly with a restricted list of the matters for that client and I scroll through the list. However, regardless of which matter name I select, the cboMatterName combobox selects and shows only the first matter.

I have checked and the form is set to "Allow edits - Yes" and the comboboxes are all set to "Enabled - Yes" and "Locked - No".

Can anyone tell me what I am missing here and why the second combobox seems to lock on the first value no matter which name is selected? Ultimately I trying to get to the point, where the selection in any one of the comboboxes auto-populates the other two, but all comboboxes remain changeable at anytime.

Thanks for your help.

[June7]

The cboMatterName combobox should not retrieve ClientID field. Try:

SELECT MatterID, MatterName FROM tMatters WHERE ClientID = [cboClientName] ORDER BY MatterName;

Why use VBA to set RowSource? Just put that SQL in the property then only the Requery needs to be in code.

Don't understand why there would be 3 comboboxes.

What is the RowSource for cboClientName?

[JCW]

Thank you very much for the help. I am getting there.

I created the following VBA code and it now populates the Matter and MatterNo boxes when I select a client name and matter name, both of which change and update perfectly.

cboMatterName.RowSource = "SELECT MatterID, MatterName FROM tMatters WHERE ClientID = [cboClientName] ORDER BY MatterName;"
cboMatterName.Requery
cboMatterName = cboMatterName.ItemData(0)
cboMatterNo.RowSource = "SELECT MatterID FROM tMatters WHERE MatterID = [cboMatterName] ORDER BY MatterID;"

Now I need to add a control to the cboMatterNo to do the reverse. In other words, if someone opens the form and instead of selecting by client and matter, happens to know the matter no and chooses that, I want the client and matter names to populate. I thought it would be merely working backward from the above, but I am again stumped. I don't understand why I can't set the row source of cboClientName to select the client name from the client table (tclients) where the matterid is chosen in cboMatterNo.

I tried the following code, but I then get an error message asking me to enter a parameter value for MatterID. And when I enter the MatterID, I get the first client name, but not the client name associated with that matter no.

cboClientName.RowSource = "SELECT ClientID, ClientName FROM tClients WHERE MatterID = [cboMatterNo];"
cboClientName.Requery
cboClientName = cboClientName.ItemData(0)

The cboMatterNo rowsource is "SELECT tMatters.MatterID, tMatters.MatterName, tMatters.ClientID FROM tMatters;

I figured that the expression "ClientID = [cboMatterNo]" returns the ClientID associated with the MatterID I input, and that the cboClientName should then select the ClientName from the tClients table for that ClientID. But I am obviously missing something. Is my problem due to needing to join the tclients and tmatters tables somehow? The tClients table has two columns consisting of ClientName and ClientID. The tMatters table has columns with ClientID, MatterName and MatterID.

Thanks.











cboMatterNo.Requery
cboMatterNo = cboMatterNo.ItemData(0)

[June7]

Yes, combobox RowSource can be a query that joins tables.

If you set combobox RowSource to a join query in order to show clientID, clientName, and MatterID, the client will repeat if there is more than one matter for each client.

[JCW]

Why doesn't this work?

cboClientName.RowSource = "SELECT ClientID, ClientName FROM tClients WHERE ClientID = [cboMatterNo];"
cboClientName.Requery
cboClientName = cboClientName.ItemData(0)

Shouldn't this select and display the specific ClientID and ClientName from the tClients table based on the ClientID contained in the tMatters table that is associated with the MatterID record chosen in cboMatterNo? The cboMatterNo query contains three columns -- MatterID, MatterName and ClientID -- so doesn't [cboMatterNo] include all of the records for any given row such that ClientID=[cboMatterNo] should return the client number associated with the matter number selected in cboMatterNo?

P.S. If I hard number "WHERE ClientID = xxxx" then it populates the cboClientName combobox fine. So I guess the problem I have is that "WHERE ClientID = [cboMatterNo]" is not returning the ClientID associated with the selected MatterID chosen in cboMatterNo.

[June7]

Assuming ClientID is in the 3rd column of cboMatter and it is not the BoundColumn, the value of cboMatter is MatterID, not ClientID.

If you want to refer to some value other than the one that provides the combobox its value, must reference that column by its index. Index begins with 0 so the 3rd column is index 2.

SELECT ClientID, ClientName FROM tClients WHERE ClientID = [cboMatterNo].[Column](2);

[JCW]

I think I am getting close but this is really frustrating.

If code cboClientName.RowSource = "SELECT ClientID, ClientName FROM tClients WHERE ClientID = 15233;" the combobox populates. If I code cboClientName.RowSource = "SELECT ClientID, ClientName FROM tClients WHERE ClientID = [cboMatterNo].[Column](2);" it does not.

As a double check, I put in this line of code Temp = [cboMatterNo].[Column](2), with Dim Temp as Variant, and it shows "15233" when I debug it.

Do you have any additional thoughts about where I am going wrong?

You help is greatly appreciated.

[June7]

Setting RowSource programmatically does work, however, I've never tried to programmatically set with a column index reference. AFAIK, it should work. If you want to provide db for analysis, I will look at it this weekend.

[JCW]

Thanks to your help and little trial and error, I resolved the issue.

This code worked: cboClientName.RowSource = "SELECT ClientID, ClientName FROM tClients WHERE ClientID = " & [cboMatterNo].[Column](2) & ";"

I am not sure why the "& ... &" was required. Does that mean that the ClientID is being viewed as a string instead of an integer? Where would that be defined? I am trying to modify an someone else's work.

Thanks again June7!

[June7]

The & is concatenating the actual value of the combobox column into the SQL statement. So the working solution is to programmatically set the RowSource property with the 15233 value in the SQL statement, instead of the variable reference to combobox column. I know referencing a combobox column in a textbox ControlSource works. However, I just remembered that referencing a combobox column in an Access query object will not work. That must be why the RowSource fails with reference to combobox column.