Problem with third cascading combo box - please help.

[warren0127]

I have a requirement that will need three cascading combo boxes - the second box should only display the contents that match or are relevant to the first combo box, etc.

I followed this tutorial:

https://www.youtube.com/watch?v=SpMyGlEInGs



It seems the first and second combo boxes cascade and works fine. However, the third combo box remains blank after the second combobox selection has been made.

I have three tables: Customer, CustomerSubOrg, and PORs. I have setup lookup fields that are using Primary keys for two of the tables (CustomerSubOrg and PORs). However, I ensured that the primary key fields display as the "Names" of each instead of the numbers. I also ensured I went back and changed their data type to "Text" instead of "Number" so the text appears (which seems to have worked for box #2).

I created two queries:



Does anyone know what I am missing, to get the third combobox to display its contents? I've attached some additional screen shots below. I know the problem is within the second combobox -- perhaps the query needs some adjustment?

Thank you much.

[CJ_London]

have a look at this link - same requirement, different solution

http://www.access-programmers.co.uk/...d.php?t=275155

[ItsMe]

I would start by checking the Column Width, Column Count, and Bound Column properties of your combo. Maybe you can post that info here. Your query is going to look at the value of your combo which is determined in the Bound Column, in conjunction with the Row Source and other properties
.

[CJ_London]

duplicate post here

http://www.access-programmers.co.uk/...=1#post1427666

[warren0127]

Apologize about the duplicate post - I was just looking for as much help as possible because I have been stuck on this for days. Appreciate the links - I will check them out - thank you.

[warren0127]

Thanks for the reply. When I click the second drop down menu, and within the properties, it shows:

Column Count 1
Column Width 1"
Bound Column 1


Thanks,
- Warren

[ItsMe]

I would start by changing some properties on the second combo. You want to make sure your second combo is working correctly before moving forward. You may have to take a step back and look at the first combo, too.

Post your SQL for the first combo here. You can find it within the Row Source property field.

[warren0127]

Hi ItsMe,

Thanks for your response. Please note it appears the two Queries I had built and linked in the second and third comboboxes seem to be doing the work.


Here is the SQL for the first combobox:

SELECT Customers.CustomerID, Customers.CustomerOrg
FROM Customers;



Here is the SQL for the SubOrg Query that is linked to the second combobox (when I created the combobox, as instructed in the video, it told me to choose a query to use for it):

SELECT CustomerSubOrg.SubOrg, CustomerSubOrg.CustomerID
FROM CustomerSubOrg
WHERE (((CustomerSubOrg.CustomerID)=[Forms]![frmMain]![cboCustomer]));


And here is the SQL for PORs Query that is linked to the third combobox:

SELECT PORs.PORName, PORs.SubOrgID
FROM PORs
WHERE (((PORs.SubOrgID)=[Forms]![frmMain]![cboSubOrganization]));


Thanks,
- Warren

[ItsMe]

For the second combo try ...
SELECT CustomerSubOrg.SubOrg, CustomerSubOrg.CustomerID, CustomerSubOrg.SubOrgID
FROM CustomerSubOrg
WHERE (((CustomerSubOrg.CustomerID)=[Forms]![frmMain]![cboCustomer]));

Column Count 3
Column Width 1", 0, 0
Bound Column 3

...
and the following properties for the third combo box.

Column Count 2
Column Width 1", 0
Bound Column 2

[warren0127]

Hi ItsMe,

I made the modifications to your suggestion - and it still is not working. When I select the second dropdown, now, the selection doesnt "stick" and the box clears as soon as the selection was made. Also, the third combobox remains empty as well.

Perhaps I need to still adjust the bound/width columns. The video tutorial I had initially followed may have had other settings that were not transparent to the audience. I'm so confused

Thanks,
- Warren

[ItsMe]

Here you go, I used the SQL and properties from post 9 and added some code behind the form to requery.

Me.cboSubOrganization.Requery
Me.cboSubOrganization.Value = ""
Me.Combo4.Requery
Me.Combo4.Value = ""

[warren0127]

ItsMe,

It seems to have worked now, much appreciated for your help on this! I disregarded the two queries I originally built, and just put the SQL statements in the row sources for the comboboxes - worked like a charm with your VBA code as well. It seems your VBA code simplified my initial code for the "AfterAction" functions that the tutorial originally provided.

A few questions for you, if you dont mind, since I am trying to learn as much as possible and understand what certain syntax statements actually "mean".

1) For the column width, column count, and bound count - what exactly do these do for the combobox, and why did we need to adjust them?
2) The VBA code you provided - can you explain what this is doing to the form/combobox? Requery seems pretty straightforward - but still dont understand it completely.. And the "cbo.SubOrganization.Value = " " <== what is it telling it to do?

Thank you sir!
- Warren

[ItsMe]

1) For the column width, column count, and bound count - what exactly do these do for the combobox, and why did we need to adjust them?

These properties, along with the rowsource, are the most critical.
Column Count - This is the number of columns your combo will consider. If you bring three fields/columns into your SQL, you would want your combo to recognize the same number of columns. SELECT Field1, Field2, Field3 would dictate Column Count = 3.

Column Width - Different columns may require different widths. For example, an address may require a wider column than a first name. Also, you may not want to display every column. Use the value, "0", to hide a column. The first column displayed should be the column you want the user to see and use as the main reference to the records. This first column will be considered as the user types characters into the combo.

Bound Column - This column, within the combo, determines the field associated with the Value Property of the Combo Box Control. You can retrieve the value from any combo using an expression. However, the Value property is associated with the Bound Column Property. MyValue = Me.ComboOne.Value ... This property is what other objects within access will consider unless you explicitly define the column within your expression. Me.ComboOne.Column(0) ... This index is zero based. 0 is the first column and 1 is the second column.

The VBA code you provided - can you explain what this is doing to the form/combobox? Requery seems pretty straightforward - but still dont understand it completely.. And the "cbo.SubOrganization.Value = " " <== what is it telling it to do?

A Form Object has both the Requery and the Refresh methods available to them. A Requery will take a trip back to the table while a Refresh will visit only the Controls within the form and the form's Recordset, assessing changes changes via User Input. Controls only have the Requery method available and will take a trip to the table(s). If you Requery a combo it will execute the SQL and retrieve the data from the table, observing the current values and status of the table(s) and fields within.

If you cause the Value of your Combo to equal an empty string (""), you are, in essence, bringing its Value back to a Null state (Access creates the Null behind the sense). Understand that the Value effects what is displayed to the user. In the case of a Combo that is bound to a field within a table, it will affect the value of said field, too. In the case of an Unbound Combo, a combo that does not have a field bound to it, the VBA that does the Requery should suffice. In other words, if the combo is effecting change to a value in a table (a bound combo vs. an unbound combo), you would probably want to assure the value in that table is the same value the user sees and use the ="" thing.

[warren0127]

Hi ItsMe,

Much appreciated for you to take the time to explain how everything works and the meaning behind everything. This has definitely helped educate a Newb like myself!! Unfortunately every book I have read so far does not cover any of this. Thanks again

[ItsMe]

You bet...