asked on

Access cascading combo boxes

I have used cascading comboboxes several times, but cannot get through this problem. I am trying to have a form select the appropriate audit by property and year.

I have a tblProperty with PropertyID (PK), PropertyNameShort, and ProjectStatusID
I have tblPropertiesAudits with PropertyAuditID (PK autonumber), PropertyID and ValidDate

So my property table will have data that looks like:
PropertyID PropNameShort ProjectStatus
15 Green Street 9

and my audit table will have data that looks like:
PropertyAuditID PropertyID Valid Date
25 15 12/31/2008
26 15 12/31/2009

In the form header of my form frmPropAuditMaster I have two combo boxes in question: cboPropertySelect and cboAuditYEarSelect

The row source for cboPropertySelect is:

SELECT tblProperty.PropertyID, tblProperty.PropNameShort, tblProperty.ProjectStatusID
FROM tblProperty
WHERE (((tblProperty.ProjectStatusID)=4 Or (tblProperty.ProjectStatusID)=9 Or (tblProperty.ProjectStatusID)=10 Or (tblProperty.ProjectStatusID)=11))
ORDER BY tblProperty.PropNameShort;

With the bound column set to "1" or PropertyID

I have tried all manner of approaches trying to get my cboAuditYearSelect to update after the selection of the property--it should show the available audits for the selected property.

For example, I thought that using the following row source for cboPropertySelect would work combined with a ":Me.cboAuditYearSelect.Requery" in the afterupdate of the cboPropertySelect (the master combo box)

SELECT tblPropertiesAudits.PropertyAuditID, tblPropertiesAudits.PropertyID, tblPropertiesAudits.ValidDate
FROM tblPropertiesAudits
WHERE (((tblPropertiesAudits.PropertyID)=[Forms]![frmPropAuditMaster]![cboPropertySelect]));

I am a beginner, so there is probably something simple I misunderstood. Thank you.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Markus Fischer

> there is probably something simple I misunderstood.

I have read over your post several times, and I could find no fault with it. Cap's method above should work, naturally, but yours should as well. Specifically, you can refer to a control on the same form in your second combo's query, and the .Requery method in the “after update” event of the first should work. A simplified version:

SELECT PropertyAuditID, ValidDate
FROM tblPropertiesAudits
WHERE PropertyID = cboPropertySelect;

Exactly how doesn't it work? What happens? Do you see any error messages?

(°v°)

Si Ball

is the colon : in the command supposed to be there?

:Me.cboAuditYearSelect.Requery
or
Me.cboAuditYearSelect.Requery

i think i usually put the re-query in the on exit event, and set focus of the next control in the chain there too..

if the user tabs out of the 1st combo, the 2nd is refreshed and gets focus.

Phereklos

ASKER

Capricorn1 - thank you for the shorter code. That really makes my select statement more manageable.

harfang - Thank you for your comments. I had tried something similar to what Capricorn1 had suggested, and then tried another approach. I am not sure what I did wrong (so I will probably do it again), but I started from scratch and got it working.

Sudonim - the colon in my line ":Me.cboAuditYearSelect.Requery" was just a fat finger when I was posting the original question. The colon should not be there.