Filter combo box from another combobox (1 Viewer)

I spent hours on this and I’m defeated. I expect the answer is simple, if not I’ll just have to think of another approach. But if anyone knows the answer (and understands what I’ve written, I’ve tried to be clear and simple!) you will make me very, very happy!

I have three tables, simplified for explanation:

Table: Countrys (Based on ISO3166-1)
Fields: CountryID, CountryName

Table: Subdivisions (Based on ISO3166-2)
Fields: SubdivisionID, SubdivisionName, CountryID

Table: Locations (Based on UN/LOCODE)
Fields LocationID, LocationName, CountryID, SubdivisionID

The problem I can’t figure out is the Form for ‘Locations’ Table. (And I have this same scenario for other forms, so would be very useful to understand the how to get this right)

If I select an option from the ‘Country’ combo box (cbocountry) on the Locations form, then I want the Subdivision combo box (cbosubdivision) to filter records to show the same CountryID.

I’ve tried adding 'criteria' to the CountryID field in query builder for the cbosubdivison: [Forms]![locations]![cbocountry]

And VBA to the cbocountry:

Private Sub cbocountry_Change()
cbosubdivision.Requery
End Sub

And whilst that works, it filters all the records on a continuous or split form, so that’s no good.

Do I need to clear the filter on record change somehow? Although I expect that would fix the issue when moving to one record to another. But not solve the continous/split form problem.

And if so not use continuous or split forms? Instead using a subform and a separate datasheet or continuous form to find and select a record?

Not quite sure how to go about that yet, but if that’s what’s needed...

Or is there some clever VBA code I need to learn that I haven't found in my searches?

Have you tried searching on Cascading Combo Boxes?

Yeah, I think that's were I got the bits I've done already. I'll search again and deeper and see if I can find something more appropriate. Appreciate the reply RuralGuy!

Here's a video for you: http://www.datapigtechnologies.com/flashfiles/combobox2.html

If your combo boxes are on continuous forms then changing the record source on one will change it on every detail line.

The normal route would be to put the search boxes at the top of the form in the header.
If you want to display a combo box on the detail section you can - but don't change it's record source.

Thanks again. But this video only shows what I've already done. My combo boxes worked. (Although my approach had slightly different coding). I've changed to the coding used in the video but I still have the same problems.

The problems I have are when changing records the filter does not reset, And all the records are being filtered on the split form.

With nearly 50,000 location records, I'm using a split form to help find a record. The benefits of sorting all the records in country and/or location order, filtering records for part of the data, etc. are needed with so many records.

When a record is selected on the split form this populates the single record form to make any amendment, or just view additional data hidden on the split form.

The filters used in these cascading combo boxes filters all the records.

So when looking at all the records, the (subdivision) field disappears on the split form showing only the (subdivisions) field data of the current record.

i.e. If the current record (the first record when opening the form) selected was a UK location, the subdivision of all other countries vanish and only the UK (subdivision) fields are shown. Due to this cascading combo box filter approach.

Additionally if I select the next record, a US location, the subdivision field on the single form is blank because it is still filtering UK subdivisions.

Whilst the could be resolved by clearing the filter when changing records (haven't tried to do this yet) I'm sure this will not solve the problem when looking at all the records on the split form as this will just change the filter and show only the subdivisions of the US record or whatever record is selected.

So I am thinking that a split form is not the correct approach?

Maybe I need a subform so the filter on the one record form does not affect the continuous form data. And clear the filter when changing records?

I'm not a big fan of the Split Form.

I'm going to try this:

www DOT experts-exchange DOT com/articles/5950/Cascading-Combos-on-Continuous-Forms-and-Datasheets DOT html

And see if I can decipher the code and make work with a view only continuous form linked to a subform for the single record form with edit, add and delete enabled.

Fingers crossed I can get it to work!