• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

Trying to limit the selection choices in 2nd combobox based on selectiong in 1st combobox

Main form - frmCustomers
Subform (shown in datasheet view) = subfrmPets

On the subform are two comboboxes.  I'm trying to limit the selections in the 2nd combobox (cboBreed), by the selection in the 1st combobox, (cboSpecies).

So in the rowsource for the 2nd combobox I have: (criteria in query designer)...

SELECT tblSpeciesBreeds.Breed, tblSpeciesBreeds.Species FROM tblSpeciesBreeds WHERE (((tblSpeciesBreeds.Species)=[Forms]![subfrmPets]![cboSpecies])) ORDER BY tblSpeciesBreeds.Breed;

But nothing is showing up.

What am I doing wrong?

--Steve
0
SteveL13
Asked:
SteveL13
1 Solution
 
SteveL13Author Commented:
By the way, if I open the subform by itself in single form view the "filter" works fine.  It must be the way I'm referring (or not referring) to the form/subform.

??
0
 
PatHartmanCommented:
Two problems.
1. References to subform objects need to include the parent form -
Forms!mainform!subform.Form!yourcontrol
2. In the BeforeUpdate event of the first combo, you must requery the second combo.
Me.cboRight.Requery
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<No points please>

When you refer to a Subform, you must refer to the SubForm CONTROL, and then refer to the Form property of that control:

[Forms]![subfrmPets].Form![cboSpecies]

If the Subform CONTROL on the parent form is named "subfrmPets" then this will work. If it's named something like "Subform1", this will fail.

Access defaults the name, depending on how you insert the Subform Control. If you drag the control from the designer toolbox, Access will name it something like "Contol1". If you drag a form and drop it onto another, Access will add a subform control, and will name it the same as the form you're using for a Subform.

BTW, your code works when the subform is opened directly because it's part of the Forms collection (ie. the "[Forms]!" part of the equation. When you use a form as a Subform, it's not part of the Forms collection, so you cannot refer to it using the "Forms!" syntax.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now