Combobox with active employees

[Urco]

Hi

Been looking on the internet and especially on this forum, but haven't found a satisfactory answer yet to the following issue.

I have a combobox on a form that displays employees. Now several people in that list don't work here anymore. I added a column to the table called active, with the option true and false and changed the query. The combobox now displays only the active employees. But as you know all the records that were dealt with by employees not working here anymore show blanks for the combobox. I need those records still to display their name as this is necessary.

So how can I get their names to be out of the combobox but still displayed when a record handled by them is opened on the form?

Thanks in advance.

 

[tarcona]

I just dealt with this for Active/Inactive companies. What I did was when I wanted to delete a company, I would press a Delete button, but the company would only be inactive. It would not be deleted. It my table I had an Active/Inactive column. So when deleted, that field would be changed to an "I" instead of an "A". Then I had a query that only showed Inactive companies. And if the user wanted to Reactivate the company, they would just change the "I" to an "A" in the query.

Maybe do what I did.

Maybe create a combo box for active employees and then create a combo box for inactive employees?

I hope that I helped you in the right direction.

 

[dkinley]

You work with Alistair J?

Here is a proposed method to get around this issue, let's assume the row source feeding your combo box has at least 3 columns: UserID, UserName, Status
And you have criteria in the third column: "Active"
(Not, the UserName column is / is not a concantenation of first and last names - the point is the first column is the UserID)

1. Make an UNBOUND text box (txtUserID) that is the same size as your combo box with the same label and place this directly on top of the combo box (cboUserID).

2. Use the following code in the forms OnCurrent() property.

If IsNull(Me!cboUserID.Column(2)) Then
    Me!cboUserID.Visible = False
    Me!txtUserID.Visible = True
    Me!txtUserID = DLookup("[FirstName]", "tblUser", _
        "[UserID]= " & Me!cboUserID) & " " & DLookup("[LastName]", "tblUser", _
        "[UserID]= " & Me!cboUserID)
Else
    Me!cboUserID.Visible = True
    Me!txtUserID.Visible = False
End If

This snippet looks for something in the second column of the combo box. On the inactive names, this column is null because of the criteria placed in the row source. It will hide the combo box and populate the text box from the DLookups.

If there is data in the second column then it knows it is looking at an active user and hides the text box but displays the combo box.

-dK

 

[Urco]

Thanks for replying.

tarcona, i did something like you did. But it still does not deal with the issue that it shows blanks for records that were dealt with by inactive employees.

dkinley, thanks for the help. I'm going to try your suggestion, I will let you know how it works out.

 

[Urco]

Great, it appears to be working.

The only thing is that when I add a new record in the form it does not show the combobox to make a selection of active employee. It directly shows the textbox as the value of the combobox is a blank. Any ideas to solve this?

 

[DCrake]

Simple Software Solutions

For historical purposes when you are editing a record you need to base the recordsource of the combobox on the historic table. In that table you will hold a code relating to the employee that set up the record. Create a query and bring in the employee table and create a join on the PK in the employee table and related field in the historic table. Group by PK this will then show you only those employees who set up the original records.

When adding a new record change the combobox recordsource to active employees only. This will ensure that only valid employees can be selected.

The only issue you may have is that if you re use PK's in your employee table the employee name will change and you will not be able to detect who the creator was, as you would only be storing the PK as a FK in the main table.

CodeMaster:

 

[dkinley]

Urco ... you can create a nested if-then (outside of the if-then posted) that checks if the record is new or not to show which box.

DCrake ... i think i understand what you are getting at. Change the recordsource of the combobox depending on the state of the record?

I made assumptions that Urco was storing the fk of the UserID in the bound control. So .. for example, if the record is historical, then the criteria of the recordsource is 'Inactive' and if new or current, then the criteria of the recordsource is 'Active'?

If that is right ... it does sound simpler than my proposed work-around. I am just not that familiar with using conditional combo boxes in that fashion to dynamically set the recordsource.

-dK

 

[dkinley]

Forgot to paste it, Urco. Here again is the work around ...

If Me.NewRecord Then
    Me!cboUserID.Visible = True
    Me!txtUserID.Visible = False
Else
    If IsNull(Me!cboUserID.Column(2)) Then
        Me!cboUserID.Visible = False
        Me!txtUserID.Visible = True
        Me!txtUserID = DLookup("[FirstName]", "tblUser", _
            "[UserID]= " & Me!cboUserID) & " " & DLookup("[LastName]", "tblUser", _
            "[UserID]= " & Me!cboUserID)
    Else
        Me!cboUserID.Visible = True
        Me!txtUserID.Visible = False
    End If
End If

-dK