Question How to setup an Employee combo box properly

[Stoss]

Hello all,

I was just wondering how the best way to handle this situation....

I have a combo box that has employee information (i.e. Name) and this is used when creating a new Purchase Order, and shows their name when selecting previous records. The question that I have is what is the best way to do this? I have an "Active" field in the table for that employee but if I tell the combo box to search only "active" employees then all the PO's that they have created in the past will show up blank (employee combo box that is). It is a relational design so the employee is still linked to that PO.

In a nutshell, I want to show the employees name in the combo box when they have already entered in a PO in the past but when you pull down the combo box creating a new PO, I just want it to show "Active" employees.

Hope that makes sense!
-Stoss

 

[David Eagar]

Create a query of Active employees and use that to populate the combo box instead of the employees table

 

[Stoss]

Hmm, I must be missing something. I created a query but it still does the same thing. If I put an employee off of active, it doesn't show their name in older records.

-Stoss

 

[jzwp22]

You can alter the row source of the combo box depending on whether the current record is new or not. If the record is new, show only active customers; if it is an existing record, show all customers. You would use the on current event of the form and include some code similar to the following:

Private Sub Form_Current()
If Me.NewRecord = True Then
    Me.cboCustomer.RowSource = "SELECT [tblCustomers].[pkCustomerID], [tblCustomers].[txtCustomerName], [tblCustomers].[logActive] FROM [tblCustomers] [COLOR="Red"]WHERE logActive=-1[/COLOR] ORDER BY [txtCustomerName] "
Else
    Me.cboCustomer.RowSource = "SELECT [tblCustomers].[pkCustomerID], [tblCustomers].[txtCustomerName], [tblCustomers].[logActive] FROM [tblCustomers] ORDER BY [txtCustomerName]"
End If

End Sub

The first Me.cboCustomer.RowSource statement includes the filter to show only the active customer (WHERE logActive=-1). The second me.cboCustomer.RowSource does not have the filter.

 

[David Eagar]

Hmm, I must be missing something. I created a query but it still does the same thing. If I put an employee off of active, it doesn't show their name in older records.

-Stoss

Yeah, i jumped the gun a bit there and I see jzwp22 has given a good solution