combo populating combo in continuous form

[jim68]

I have a continuous (sub)form with one combobox populating another. The problem is that when I select the first of the two boxes it populates the second (fine), but then for the next records when I select the first combo all the second combos on the form are also repopulated.

I think people have asked similar questions before but I did not understand the answers! It should be easy to make the current record only to be affected.

Thanks

 

[scriverb]

This is a problem with SubForm controls and their properties. You see when you select the value for the first ComboBox on the subform and then trigger the requery of the second combobox using the value of the first all rows in the subform will be requeried with that value being criteria. Thus they will all be the same. Remember you have one form only multiple records being displayed.

If you change the Row Source you change it for all instances of that control. Same goes for the backcolor, forecolor, etc. Change the text to Bold and the entire column will change.

I don't know if there is a workaround for this problem. I haven't seen one. If you don't requery and just pick a value the value for that record will be saved properly. Bob Scriver

 

[jim68]

Thanks for replying. Then, the only solution if I were to keep my two comboboxes would be to redesign and have a single form? That way the requery would only apply to the current form which would only be showing one record.

Hope I have it right.

Thanks agaian,

Jim

 

[scriverb]

That is correct. When you use a continuous form you really are only using one form but showing multiple records. The same rules apply in both instances. Bob Scriver

 

[jim68]

Thanks for your help on this. Let me then ask how to get around the problem best, if you don't mind the time.

I am entering occurrences of words in literature. So I have

tblBkCategory containing data of types of literature (e.g. Greek lit., Latin lit., inscriptions)

I wish to select one from this category and then for it to populate the list of books from tblBooks (this is to prevent one having to select from a list of say 1000, but maybe of only 50 according to category).

Both boxes should be bound so that it records the data (i.e. in which books a word occurs). Will I need 5 forms, one for each of 5 Bkcategories, or is there a better way of doing this?

Hope that is not too opaque?

Jim

 

[scriverb]

Jim, why don't you try setting the second Combo Box property limit to list to false. This would allow the value that is entered to be something other than what is queried.

Now the value will always be the same and when you click into an already filled combo then the ComboBox of that particular record should be the value that is used to requery all of the comboboxes and you should see you selection as one of those available in the dropdown.

Give that a try. Bob Scriver

 

[jim68]

Thanks, Bob, that worked well - good idea. One further anomaly, however, is that when I select limit to list as false, Access says that the column widths of the combo must be the same. This means the ID no. is now visible - any way of getting around this?

Thanks for your help on this; it has allowed me to think about more complex programming issues in the meantime....

Jim

 

[scriverb]

Sort of. In the row source you probably have a query that is populating the combobox. The order of the columns change be changed so that the value that you want to be visible is the first one and the ID no. can be moved to the last one. Now if you are using ID no. as the bound column and that used to be 1 then you will have to change that to whichever column it now resides. If this works for you then the only other small problem is that there is a horizontal scroll bar at the bottom of the combobox selections. This is just an annoyance rather than a function problem. It will allow the user to scroll to the right and see the ID no. column but this shouldn't really have a huge negative effect.

That's my best shot at a solution. Does it work for you? Bob Scriver

 

[jim68]

The simplest solutions are the best. Yes, that of couse worked fine (shoudl have thought of it). And I can live with the scroll bar for now.

Thanks for all the help,

Jim