Solved

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

Posted on 2014-03-25
3
675 Views
Last Modified: 2014-03-26
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
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 

Author Comment

by:SteveL13
ID: 39953624
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
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39954613
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
 
LVL 84
ID: 39955629
<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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question