Inactive names dont show on old records

[oxicottin]

Hello, I have a form that I use for data entry and on this form there is a combo box that has a list of employees names that are "Active". The problem im having is When an employee is "inactive" thier name is hidden from the list so it only shows active employees. What is happening is if a set an employee as inactive then scroll back a few records to where I had used that employees name it shows a blank spot there now and if I select the combo box it shows the list of active employees. How can I make it show both inactive and active employees on records that are alreaty created but on new records only show active employees.

 

[John Big Booty]

Use the following in your Form's On Current event;

Dim strComboRowSource As String
    
    If Me.NewRecord Then
        strComboRowSource = "SELECT tblEmployees.EmployeeID, [EmpLast] + ', ' + [EmpFirst] AS EmployeeName, tblEmployees.Status " & _
                            "FROM tblEmployees " & _
                            "WHERE (((tblEmployees.Status)=False));"


    Else
        strComboRowSource = "SELECT tblEmployees.EmployeeID, [EmpLast] + ', ' + [EmpFirst] AS EmployeeName, tblEmployees.Status " & _
                            "FROM tblEmployees; "
    End If

    Me.cboEmployeeName.RowSource = strComboRowSource
    Me.cboEmployeeName.Requery

 

[bikermo]

I borrowed this code and it works GREAT! THANK YOU!

Now just one more question. We have a form that will have one combo box for Associates (like Employee in the OPs DB) which I've got set to show only active Associates for new entry per your tips above, but we'll also have a combo box for Developer which I need to function the same way. I don't know how to add that part to the On Current event, can you help?

 

[John Big Booty]

Sorry just noticed your question

Simply copy the code you currently have. Select the form events, you can tell you have this as there will be a small black square in the junction between the vertical and horizontal rulers at the top left hand corner of the design window;

Select Event Procedure from the dropdown list;

Now click the ellipsis to the right of the row, past your code into the code window where highlighted;