I have a problem with a Combo box which I cannot seem to insert a value into using the selection based on a combo box.

First box picks a week number, using a query as the row source, so that only valid week numbers in the underlying table are picked.
I read that you can't use Group By in a combo box query as it wont let you insert data using Item.Data(0), so I used Select
Distinct as the Data.

I can use the Form on open event and the Item Data property to insert the first week number, 1119 into the combo box, and I also
populate a text box "weektxtbox" with the value.

The second combo box then needs to pick up the week value to use as a criteria as I then want to use that week to determine
which Team Leaders relate to that week. I use the first combo box value 1119 as a filter which I do by putting it into the
criteria of the Row Source using weektextbox in the criteria field. Again this uses SELECT DISTINCT to only bring the name up
once.

This filters it quite happily but I cannot automatically populate, using TLCombo.Item.Data(0) the second combo box with the first
team leader in the list. The control works fine, and all reports are generated correctly using the combination of week number and
team leader name, but I cannot automatically insert the first value into the second box, although I can quite happily in the first
weeknumber box.

Is this something to do with SELECT DISTINCT not allowing update of the field. I have googled the subject, and apart from advice
to use DISTINCT and not grouping within the query, it implies there should be no problem doing this. I have several other examples
where I have achieved this, but the only difference is that the ones that work OK use a fixed table and do not need to use SELECT
DISINCT. Is it not possible to update a combo based on another combo when using DISTINCT??


Thanks J


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

Well distinct queries are not updateable.

However, that should not cause the problem you are seeing. How are you trying
to populate the combobox? Where do you have TLCombo.Item.Data(0)?

Have you tried TLCombo.ItemData(0)? I believe that is the correct syntax.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 5/10/2011 4:27 PM, bezz wrote:

J

I hope I am reading your post correctly, if so...!

I have always based any subsequent filtered cbo's on a query based on the
ControlSourceField attached to the previous Mycbo which I have on the form
in a non visible state..

MyCbo2 RowSource would then point to the query grid where the criteria would
be:

=Forms!Myform!Mycbo'sControlSourceField

then in your Mycbo1 AfterUpdate() you would have:

Me.MyCbo2.requery

which would then refilter it with any matches found....

HTH
Mick