Hierarchical combo boxes in continuous form subform (1 Viewer)

[ChristopherM]

I have a problem with a subform which is a continuous from used to create lines on a delivery note. The first combo box selects the supplier, and then performs a requery on the second (stock) combo box to limit the stock selection to that of the supplier. This works fine on the first line but when the requery is performed on the next line it clears the stock description from the line above - although it does not affect the (correctly written) record. However it is a bit disconcerting for the user.

Both the combo boxes are bound, and I've tried putting the requery in both the lost focus event of the first (supplier) combo box and in the on Got Focus in the second (which caters for the user going back to a previous record's stock box. If the same supplier is used on every line the stock descriptions remain visible but they disappear on previous lines if the supplier is different - even if he supplies the same stock.

Any help or suggestions on this would be much appreciated.

 

[MarkK]

- It's a bit of an illusion that controls on separate rows of a subform are separate controls. If you look at your subform in design view you'll see there is only one row, and only one control. If you change a property of that control then it is immediately changed across all instances.
- Microsoft would say this behaviour is by design.
- In fact, the real magic is that controls on separate rows can possibly show different data.

 

[ChristopherM]

Thanks, Lagbolt, but I'm not sure that I'm really changing a property of a control - what in effect I am changing are the criteria in an underlying query which, in qualitative terms, I would have thought is no different from displaying different records?

It has occurred to me to put the data entry part in the header of the subform and use a different (unchanging) query as source for the continuous records in the detail secion of the subform (with no combo boxes, and not editable) but before I try this I would be interested to hear if any one else has gone down this route, or found another solution.

 

[suepowell]

Hi Chris

I have had the same problem.

I solved it in the end by making the record source for my second combo box show only one column.

That is:
My second combo box gave department after fist choosing a person in the first combo box. Of course if the next record chose a different contact person, the departments they deal with would be different so unless the 2 people both had a department iin common the value for the first disappeared which I agree is disconcerting.

As is my normal (I hope good) practise my department table had a column with departmentid and one with department name. I bound the combo box to departmentid ansd just showed the name in the combobox.

I found that if I deleted the id field and just used the department name in the table (fine in my case it's just a pick list of about 6 unique names) the chose didn't disappear even if the next person didn't appear in that department.

I hope this helps, maybe if you can't loose the id field you can play with the field order in your combo record source, but I seem to remember you can get problems with this, I think to do with wether you allow not in list.

Good luck.

Sue

 

[MarkK]

Chris:
When you modify the query driving the second combo you affect a change to the rowsource property of that control, and all instances of that control.

It has occurred to me to put the data entry part in the header of the subform and use a different (unchanging) query as source for the continuous records in the detail secion of the subform (with no combo boxes, and not editable) but before I try this I would be interested to hear if any one else has gone down this route, or found another solution.

This is exactly what I would do.

 

[ChristopherM]

Thanks everyone for your help - putting the data entry and combo boxes in the header and using a different query for the detail worked.

However, I did come across what seems to me to be a bizarre feature of Access (possibly just 2007?). When tidying up the data entry by enabling/disabling various controls on the subform, I discovered that the last control on the subform still retains the focus even when a control on the parent form has the focus (either by clicking on it or in code) and you cannot disable it (the subform control) from an event on a parent control. The only way I could find round this was to move the focus to another (permanently enabled) control on the subform in the subform's on exit event.

Can a control in the parent form and one in the subform both have the focus simultaneously? I wondered if anyone knows of any rules governing when controls actually lose/do not lose the focus?