Filtered Combobox in Continuous form (1 Viewer)

[jonnie_c]

I would like to filter a combobox in a continuous form according to the selection of another combobox in that record (e.g. 'Department' combobox selection determines available options in 'Employees' combobox). I would like to see the 'Employee' value in all records even though that value may not be in the current recordeset for that combobox. Impossible? If so, what is the best workaround? The best one I can think of is placing the 'Employee' combobox in the parent form or header of the continuous form with a locked textbox that displays the appropriate field within the continuous form. ugly, non? Thanks in advance.

 

[AndyS48UK]

Jonnie...someone will come up with a more straightforward idea but thi smay help a little(!!)...use the value of the Department box as the criteria for your query...so that, as the value of the Dept combo changes so the Employee combo will only list the Employees relating to that record. So, in the Department Criteria line in the query you have something like;
[Forms]![frmNameOfContinuousForm]![cmbDepartmentChoice]

I'm sure you can also use VB in the AfterUpdate event of the Department Combo Box...I don't reel it off too well so I won't try and confuse but maybe someone else may be a little more fluent?
I think maybe you'd say that the RowSource for the Employee Combo would now be the same source but using the Department Combo value as the criteria...which is he same thing but done in a different way!

Not sure about your other question. Don't really understand what you're trying to do but if you're just trying to fill gaps in text boxes with the employee's name (from the combo) then, in each, you could just use an expression like;
=iif([QueryFieldName]=null,[cmbEmployeeName],[QueryFieldName]
...this fills in the employee name into the field if the Query field is null.

Hope I haven't go the wrong end of the stick...if this is confusing switch off and wait for a more learned answer!

A

 

[godofhell]

Johny, what you need is a Cascading combobox, search for Threads I have posted. I have attached databases with this very feature in it. Here is one that uses check boxes to change the recordsource, the concept can be applied to comboboxes.

http://www.access-programmers.co.uk/forums/showthread.php?t=87559