Limiting combobox selection in subform

[ilikecats]

I've got the problems from my previous thread entirely solved. (I ended up ditching the query and just pulled all the fields from the table itself.)

Now I've got another problem that I'm a little confused on. This form is intended to be a subform for another form.

The main form (Neighbourhoods) has an unbound combobox that allows the user to jump to a certain record. When this is done, the subform (Households) filters and displays only records from the Households table that are associated with the current Neighbourhood record. (I just used the wizard to link the subforms, which suggested "Households for each record in Neighbourhood using ID.")

This part is functioning correctly, and I've even managed to set up an OnCurrent event to make it update correctly if I use the navigation buttons instead.

On the Households subform, I have another unbound combobox to jump to a specified Household record. However, it displays all households. Attempting to jump to a household not in the current filter just silently fails.

Ideally, I'd prefer it to only show the households associated with the current Neighbourhood in the main form.

I've followed a tutorial to set up a cascading comboform in a test database, and it looks like what I am try to do, but I'm a little puzzled on how to get the changes to jump to a subform (or if it even applies for unbound navigation comboboxes).

(I've attached a screenshot in the zip file, in hopes that it can explain better than I can. Yes, I'm designing an Access database to keep track of Sims, mostly as an excuse to learn it. Also, please mentally replace any instance of 'families' with 'households,' I sometimes interchange the words.)

Thanks for help.

 

[theDBguy]

Hi. If you're trying to filter a form or a subform, you can use its Filter property. You said the first combo is filtering the main form fine, then just apply the same thing you're doing there into the subform's combobox. If you can post your db, we can show you how to do it.

 

[ilikecats]

The subform is filtering correctly when I select an option in the mainform. It's just the combo-box that's not filtering.

Unfortunately my database would exceed the 2 MB zip size unless I removed all the pictures first.

 

[theDBguy]

The subform is filtering correctly when I select an option in the mainform. It's just the combo-box that's not filtering.

Unfortunately my database would exceed the 2 MB zip size unless I removed all the pictures first.

Can you post the code for the first combobox? How big is your db after you do a C&R and then zip it?

 

[June7]

Yes, unbound combobox can be cascading. Normally cascading comboboxes are on same form.

Be aware that subforms load before main form so main form data is not available to subform when first loads.

Why have another Neighborhood combo on subform?

Post combobox RowSource and VBA code or provide db for analysis.

 

[ilikecats]

I'm removing all the images from a copy of the database now in hopes that I can get the zip file under 2MB.

 

[ilikecats]

Here it is.

Beware that it's still a bit of a mess of half-finished forms and clumsy attempts at SQL and VBA. I'm pretty much learning this as I go.

The relevant forms are [Neighbourhoods & Households] and [Households_subform].

EDIT: Fixed version of database in post below

 

[theDBguy]

Here it is.

Beware that it's still a bit of a mess of half-finished forms and clumsy attempts at SQL and VBA. I'm pretty much learning this as I go.

The relevant forms are [Neighbourhoods & Households] and [Households_subform].

Hi. The form in the db does not quite match the one in the image you posted earlier. I don't see the navigation buttons, so I can't tell if the number of records are changing. Can you please give a step-by-step instruction on how to reproduce the problem? Thanks.

 

[theDBguy]

Okay, I think I get some of it now. The first combobox (on the main form) simply navigates the form to the first matching record. The second combobox (on the subform) is doing the same thing. So, if you really want to "filter" the subform to a specific household, then you should not use SearchForRecord.

 

[ilikecats]

There were a few minor changes in-between that screenshot (taken a few days ago). I put the record-navigation back here (and fixed a mistake I made while preparing the database for upload).

Steps to reproduce:

1. Open Neighbourhoods and Households.
2. Use the first comboform on the left side to 'Desiderata Valley.' Note that the filter of subform changes to show the Households associated with that.
3. Check the combobox at the very top (right below the non-indicative title). It displays all the households. I'd like to filter that.

EDIT: Apologies. I'm not very good at explaining things at times. :banghead:

 

[theDBguy]

There were a few minor changes in-between that screenshot (taken a few days ago). I put the record-navigation back here (and fixed a mistake I made while preparing the database for upload).

Steps to reproduce:

1. Open Neighbourhoods and Households.
2. Use the first comboform on the left side to 'Desiderata Valley.' Note that the filter of subform changes to show the Households associated with that.
3. Check the combobox at the very top (right below the non-indicative title). It displays all the households. I'd like to filter that.

EDIT: Apologies. I'm not very good at explaining things at times. :banghead:

Okay, see if this does what you want.

 

[MajP]

Private Sub HouseholdPicker_Enter()
  Dim strSql As String
  If Not IsNull(Me.Parent.NhoodCombo) Then
    strSql = "Select Household from HouseholdSort where Neighbourhood = '" & Me.Parent.NhoodCombo.Column(2) & "'"
  Else
     strSql = "Select Household from HouseholdSort"
  End If
   Me.HouseholdPicker.RowSource = strSql
End Sub

 

[June7]

Don't need to reference Neighbourhood combo on main form. Can reference the foreign key on subform.

Neighbourhood in subform RecordSource is a number type.

Another option is combo RowSource:
SELECT HouseholdSort.Household FROM HouseholdSort WHERE Neighbourhood=[Neighbourhood];

Then in subform Current event:
Me.HouseholdPicker.Requery

Suggest not having exact same field name in multiple tables. For instance, Neighbourhood in Neighbourhoods could be NeighbourhoodName and in Households could be NeighbourhoodFK.

 

[ilikecats]

Sorry for the slow to respond. I think I have it working now. Thanks everyone.

I'll try to change the fields to be more unique.