ComboBox Value for Inactive Records

[dgunner71]

In a Clients table ("tblClients "), I have a ComboBox "Employee" to select from a list of Employees contained in tblEmployees. I only to display / allow employees who are "Active" in the system, however, if any employee was already selected, but is now "Inactive", I would still like them to show up / display.

Scenario:
Tom is the salesperson on client record ABC. Tom then leaves the company and is marked inactive. By marking Tom in active, he is removed from the combobox list so that he is no longer selectable. However, Client Record ABC should still display Tom but does not because he is marked inactive. I have written vba to make this work - just wondering if I can make it work in a table.

Thank you in advanced for any assistance.

Gunner

[NeoPa]

Hi Gunner.

You talk about a ComboBox in your table. Technically, though you can have fields in your table set up to behave as if they are a ComboBox, a ComboBox is a Control - not a Field. Controls are found on Forms & Reports. I'll assume you're actually talking about a ComboBox Control on a Form.

So, where do we start. With the setup you already have of your ComboBox Control. Unfortunately not shared yet. That will delay matters. For now I can only say generally that your RowSource is likely to be something like :

SELECT *
FROM   [tblEmployees]
WHERE  ([Active])
   OR  ([EmployeeID]=Forms!YourForm!txtEmpID)

Does that start to make sense?

[dgunner71]

As always, thank you, NeoPa - I actually was referring to the table (if possible.)

For the field in question, on the lookup tab in table design:
I have the Display Control type set to: ComboBox
The Row Source Type set to: Table/Query
The Row Source set to:

SELECT tblEmployees.AID
     , tblEmployees.txtLastName
     , tblEmployees.txtStatus
FROM   tblEmployees
WHERE  (((tblEmployees.txtStatus)="Active"))

The question comes in where the employee was Active when selected but no longer active at a future point in time.

This may not even need to be done in the table as I can use code in the actual form.

Thank you again - sorry if this is a wild goose chase question; just wondering if it could be done...

Gunner

[NeoPa]

DGunner:
Thank you again - sorry if this is a wild goose chase question; just wondering if it could be done...

Actually, it's an increasingly common one. I was hoping that wasn't where you were going but it is so let's deal with it.

This feature is something that many of us more experienced users would love never to have been introduced. The Access team has good reason for introducing it as it helps novices to better understand the sorts of things that can be done, but while it's an easy introduction, it is also something that can lead to complications & frustrations further down the line. My strongly held belief is that you are a lot better off without it. Once you start to know what you're doing you leave the table design as just that - table design. User interaction with the data is what Forms are for. However, I've already covered that approach in the earlier reply.

In the table design itself you don't have the same capabilities that are available to you in a Form. There are no Event Procedures that allow you to make changes on the fly when you move from one record to another. That's not a surprise. Table designs were never created to support user interaction beyond the basics.

So, what can you do?

In your position I would accept that direct configuration of the SQL is not possible so I would include all records in your SQL, but I would also include an extra column that shows [txtStatus] as well. Idiot users should never have access to the raw data anyway. If you plan to give access to anyone that can't be trusted to use it properly then more fool you. For users that can be trusted this should be perfectly adequate. Never select an item that has a value other than 'Active' in that column.

This approach should not be considered to be adequate in the standard user interface. In a Form do it as shown earlier. However, you should be safe to use it solely with those responsible enough to have direct access to the raw table data.

PS. Now I've reformatted your post I can see this is pretty much what you already have so I just have to say I don't believe there's a better way than what you're already using. Not in the table design.

[dgunner71]

Answered my question perfectly (i.e. it's poor design to design a table this way.) Thank you!