base one combo box off of another

[hockey8837]

I have 2 combo boxes on a form:
[cboARRAdate] & [cboARRAzip]

I want to base [cboARRAzip] off of [cboARRAdate] (user selects a date, and only records with that date's zip codes are available from the cbo)

It seems to be working fine, I can select a date, and I can see that the zip codes change in [cboARRAzip], but I cannot select any of them but the first in the list.

What did I miss? Any suggestions? -detailed coding below.


the Row Source of the [cboARRAdate] is:

SELECT DISTINCT qryLocationOfTreeWork.TargetPlantingDate FROM qryLocationOfTreeWork GROUP BY qryLocationOfTreeWork.TargetPlantingDate;

[cboARRAdate] has an after update event of:

Private Sub cboARRAdate_AfterUpdate()

Me.cboARRAzip = Null
Me.cboARRAzip.Requery
Me.cboARRAzip = Me.cboARRAzip.ItemData(0)
End Sub

Private Sub Form_Current()
Me.cboARRAzip.Requery
End Sub

Private Sub Form_Load()
Me.cboARRAdate = Me.cboARRAdate.ItemData(0)
Call cboARRAdate_AfterUpdate
End Sub

the row source of [cboARRAzip] is:

SELECT DISTINCT qryLocationOfTreeWork.TargetPlantingDate, qryLocationOfTreeWork.LocationZip FROM qryLocationOfTreeWork WHERE (((qryLocationOfTreeWork.TargetPlantingDate)=[Forms]![frmOrderNewTrees]![cboARRAdate])) ORDER BY qryLocationOfTreeWork.LocationZip;

 

[SpentGeezer]

This is another work around for the same problem from the Spent Geezers Association.

 

[hockey8837]

Thanks, this method should work, too. I'll play around with it. My DB has both the date and the zip in the same table, rather than 2, but I think I should be able to figure it out. I'll let you know if I have any questions.

Thanks, happy holidays!

 

[hockey8837]

One issue I've noticed that's keeping me from an easy conversion here is that my "date" is not always an actual date, it is a target date or period, so some records may have "December 30, 2010" and others may have "Fall 2010". This column does not have a 'date picker' but, rather, it is an item list.

Basically, we're tracking target planting dates/tree work (each record w/ a date and zip is a location) with corresponding funding sources.

The way that we notate whether it's, say, 'Funding Source A' or 'Funding Source B' is 'Funding Source A' = seasons (i.e. Fall 2010, Spring 2011...), whereas 'Funding Source B' is notated with more an arbitrary due date (June 30, 2010, December 30, 2010...) This may not be the best method, but it helps keep it straight just by looking at the Target Date.

I want to be able to run a report based off of funding source, Target Date, and zip code (one specific from that Target Date or all w/in that Target Date). So, I need combo boxes dependent on one-another so you don't get all possible records as an option.