filter combo box, no text descriptions

[Lifeseeker]

Hello,

I have 3 individual tables that feed into one junction table in the middle.

On the data entry form, there are three dropdown combo boxes. The 1st combo box is client area, which can be 3 areas. The 2nd combo box is category, and the 3rd initiative.

I have the recordsource of the first combo box set to the actual client area table. (so it's a query that selects all program areas)

The rowsource of both the 2nd and the 3rd combo box are set to be on the master record table. So for example.....the sql for the 2nd combo box is this:

SELECT Issue_Master.Category, Issue_Master.Client_Area
FROM Issue_Master
WHERE (((Issue_Master.Client_Area)=[FORMS]![Data Entry]![CLIENT_AREA]))
ORDER BY Issue_Master.Category;


there is no error when I run this above query. The problem is when I go back to the main form, and after the I've made a selection from the 1st dropdown, the dropdown content from the 2nd combo box is showing all repeating numbers. (i.e. when I choose an area from the 1st dropdown, I get 2 13s from the category dropdown, and when I choose another program area, I get like 10 3s from the category dropdown.)

I have made sure that the column bound is set to 1 in all of those controls, but it's not giving me the actual text description.


Could anybody help me on this?

thanks

 

[Access_guy49]

Change to SELECT DISTINCT for starters. that will fix your repeat values error...
Do you have your second and third combo boxes requery'ing through code on the change of a value of the box preceeding it?

Also, Which field holds the text description your seeking?

 

[Lifeseeker]

Hi, thanks for posting back.

The requery part works.

The dropdown selection problem persists. It seems that it's referring to the ID instead of the text description, and the same numbers repeat still even when I'm using SELECT DISTINCT. (by text description I mean the actual text description of the category and the initiative).

however, the column bounds for those combo controls are set to 1 in the table design, so I'm not sure what the source of origin is.

I suspect that the problem is I have 3 combo boxes, and 2nd one depends on the 1st and the 3rd one depends on the 2nd.....so simply specifying the rowsource in the query is not sufficient to do the tricks....but I'm not sure...

Change to SELECT DISTINCT for starters. that will fix your repeat values error...
Do you have your second and third combo boxes requery'ing through code on the change of a value of the box preceeding it?

Also, Which field holds the text description your seeking?

 

[Access_guy49]

These are bound combo boxes?? In your table design you have set them as combobox and set the row source there????

Also, do you use the requery function at all on your form?????

 

[Lifeseeker]

These are bound combo boxes?? In your table design you have set them as combobox and set the row source there????

Also, do you use the requery function at all on your form?????

Hi,

yes they are all bound combo boxes and yes they are combo boxes by design.

The requery as I just mentioned works now, but the 2nd and the 3rd combo box are still giving me info like this based on the 1st combo box selection

1
1
1
1
1
1
1
1
1
1

or

3
3
3

 

[Access_guy49]

It's because it's a bound combobox. I have read documents in the past in which people advise against Comboboxes on the design of the table, and furthermore advise against setting the row source on the table design.

If you create unbound comboboxes on your form, and set up their row sources the same as you did your bound comboboxes, then in your code, on the onchange even of each combobox, set a text box to that value, and let the text box be bound to the column..
That's how I do it, and it works like a charm.