make combobox field showing value not part of the drop down list

[benmaheu]

Hello,

I am doing an Access 2007 database for our human ressources department.

I have created a table and a linked form to enter the absences of the employees.

In the "absences" form, I run a query to filter only the "active" employees so the person that will fill the form will select the employee number from a drop down list showing only the active employees.

This works fine except that if I have a look to a previous record pertaining to an employee no longer active its name will not show up in the "employee number" field (even though I don't activate the dropdown list) since he is not active.



The employee number is the first field to be filled in the form so I got around with code showing either a dropdown list if [there is a date entered (so it is a new record) or if the employee is active] or a another field showing the name

Private Sub Ctl_absence_AfterUpdate()
If IsNull([date_absence]) Then
Me!Employee.Visible = True
Me![name].Visible = False
Else
Me!Employee.Visible = Me![active]
Me![name].Visible = Not (Me![active])
End If
End Sub

I was wondering if there would be anything easier to manage this kind of situation?

(in short I would like the field associated with a drop down list to show a value not listed in the current drop down list)

Thank you very much for your help!

(ps. sorry if english is not perfect since this is not my first language )

Benoit

[davegri]

Here's a possible solution. It involves having 2 queries for the recordsource. One for active employees and one for not active. Add a checkbox or radiobutton on the form to switch between active and non active employees. Change the recordsource for the form depending on the option selected by the user. The form load event would load whichever you want as default.

[Micron]

davegri's solution is a decent one, but I think what was not mentioned is that when the form opens to the mode you decide, the checkbox or option button will have to be set accordingly, otherwise it will be Null. I think its state should match the default. Then when switching between Active/Inactive, you will have to swap the combo box row source in code, and requery the combo box. If there are subforms on this form that rely on any of this, I'd probably do this in the form Open event, not the Load event.

Another method would be to open the form in either Active or Inactive Employee mode and assign the row source to the combo box in the Open event. The downside is that you would have to close the form and reopen to switch modes. Which method you'd employ probably depends on your business needs and current project design.

A third method would be to have another combo labelled for inactive employees. Its row source would be a query that selects inactive employees.