Cascading Combobox Blank After Form Reopen

[thundermane]

Hi everyone,

Sorry if this was asked before, but all I could find were posts regarding not working comboboxes.

I have two combo boxes on the detail of a subform. Combo1 is a value list, called Type, with 2 columns, Boundcolumn 1, and the list something like "1;Main Service;2;Extra;3;Products".

Combo2 is called Subtype, 3 columns, Boundcolumn 1, and I use a SELECT CASE statement in the After Update event of Combo1 to change the Rowsource of Combo2. Other textboxes and their fields are populated according to the choices made in Combo2.

The majority of the data will be 'Main Service' for Combo1, which essentially has no subtype, meaning Rowsource for Combo2 would be blank with it and its ControlSource field storing the value 0. But for the few cases where Combo1 is 'Extra' and 'Product', Combo2 would store numeric values taken from 2 different tables that I set as Rowsources for each. They're both working, the values are stored and Combo2 shows them correctly upon data entry, but after closing the form and reopening it, Combo2 shows blank whenever Combo1 shows Extra or Product.

I know this is probably because I intentionally left the Rowsource of Combo2 blank when I designed the form. But then I figured I'd set the Rowsource to an IIf statement to get the same effect as the SELECT CASE in the After Update event. It didn't work, it still shows blank.

Then after serious googling, one site I saw had me put the same SELECT CASE statement in the On Current event of the subform. Still didn't work.

Any ideas? I am using Access 2007.

 

[vbaInet]

I don't know what you are doing with the RowSource of Combo2 neither am I sure if you set the Control Source property of Combo2. Where does the IIF() function come into place? Do you have any code in the Load event of the form that relates to Combo1 or Combo2?

 

[thundermane]

Both Combo1 and Combo2's ControlSource are tied to their respective fields, namely Type and Subtype, both numeric. They are both on a subform. But since Combo2's value can either be 0 (if Combo1's value is 1) or come from 2 different tables (if Combo1's value is either 2 or 3) I left its RowSource blank.

The IIf() was only a temporary solution that I placed into Combo2's RowSource. I removed it because it didn't work.

I don't have any Load event for this subform at all. I did however, place a Current event on this subform as my last solution. The Curernt event is the same Select Case statement I use on Combo1's AfterUpdate event to populate Combo2's RowSource. It didn't work either.

 

[thundermane]

On a whim, just now I tried that same Select Case statement on the Load event. It doesn't work either. Combo2 remains blank where there is existing data. Though clicking on that blank Combo2 suddenly shows it.

 

[vbaInet]

I still don't completely understand your setup. In your 10th post, you will be able to upload files so upload a stripped down version of your db (with the relevant objects) and I'll have a quick look and tell you what's going on.

 

[thundermane]

I believe I can attach a zip of the file. Please check this out.

Open the main form 'Transac Cont' and look at the details below on the first record. On the 1st detail, the Type is 'Main Service' and Subtype is correctly blank. But on the 2nd, the Type is 'Extra' but Subtype is blank as well. Only when you click on this Subtype combobox will it show correctly as 'extra19'.

It shows the same behavior on other records that have the detail type 'Extra' which is blank at first. Why doesn't it immediately show the correct Subtype? How can I make them show up immediately?

 

[vbaInet]

You can't have cascading combo boxes in a Continuous form or Datasheet form like that. It needs a workaround.

Have a look at this link (Continuous Form Combo):

http://www.databasedevelopment.co.uk/examples.htm

 

[thundermane]

That Continuous Form Combo example you gave may not be working right on my Access 2007. I changed the first record to Category4 then clicked on drop down arrow for Service but it still shows the choices for Category1.

On my database file though, changing Type immediately populates the correct Subtype.

What you're saying though, is that I should combine the Extra and Products tables into one and then group them together using 'Type' and 'Subtype' fields? And then create an extra textbox to contain the name while the combobox will contain the usual choices and numeric values?

 

[vbaInet]

Just had a look at your code and I can see what you're doing. You just can't do what you're doing in a Continuous form or Datasheet. Also you need to re-think your design and structure. Saving values from two different tables into one field is wrong.

 

[thundermane]

OK, thanx. I didn't know that's wrong. I just figured, since I want to implement a rudimentary inventory function for Products later on that I just have to separate that table from Extra.

Despite my wrong way of doing things, I noticed a couple of things over the weekend:
1. When I sort Type as descending, meaning Products get listed first, everything shows up correctly.
2. When I use use Debug.Print on cboType (combo1) and cboSubtype (combo2) in the Current event of that subform, they all show the same values: whatever is the value assigned to them by the first detail line of the first main record, repeated by detail records x main records.

This is baffling. I guess I can blame it as another Access 2007 quirk.

 

[vbaInet]

I think I've mentioned it a few times already, you can't tie an unbound combo box to different row sources and expect to see different results on each row in a continuous form or datasheet view. It's not an Access 2007 flaw, it's just the way it has always been.

Try this, drop a textbox on your form and make it unbound. Open your form in datasheet view or continuous and enter a number in the box. See how it repeats in every single row?

 

[thundermane]

Yes, but my combobox is not unbound. It is bound to its own underlying field in the subform. Only the RecordSource is blank at certain times.

Anyway, I do understand your point. I've done all your recommendations. I'm just faced with other problems at the moment.

Thanx again for the help.

 

[vbaInet]

I was using bound inter-changeably. The Row Source keeps changing so that's affecting the combo box on all records.

But changing a bound combo box to different row sources is uncommon. What you would normally find is filtering the row source of the combo box.

Let us know how you get on.