Solved

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

Posted on 2014-03-25
3
663 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
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 34

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now