trouble viewing entered data in a combo box drop down

Question

When I first created my database, I didn't know much about Access, I've taught myself as I went. I still have a lot to learn. Anyway, I have a database with an employee table and a project table. Instead of using the employee table's ID field, I used the employees names to populate the project table. Now I see that my project table could be considerably smaller had I used the IDs instead. So far I've created a new field in the project table and have scripted copying the ID's to the new field. So far so good. I've modified my form to use the ID's, but show the employee's names, you see Fred, but it writes 5. For historical purposes I've left all the employees names in the name table even though they've left the group or company and have added a ViewPreferences (VP) field to the name table, either Yes or No.  So 1 is Bob and the VP is set to No, 2 is John VP is Yes.  THen in the form I have the Row Source set to "Select tblEmployees.ID, tblEmployees.Usernames, tblEmployees.Role FROM tblEmployees Where tblEmployees.Role = "TPM" AND tblEmployees.ViewPreference = yes".  This filters the drop down to show only current employees (that are TPM's in this example).  So far everything works as I want.  However, when I go to an old record where the employee is marked with VP = No, that control won't show his name, even though it's written to the record.  So is there a way to view names that are written to existing records but remove their name from the drop down choices, and still use the tblEmployees ID numbers?

Answer 1

I would change VP to IsActive, and a Yes/No field, just to make more clear what this field is for.

Usernames I would rename to Username. Each employee has a single username. The field should be set to Required, and all existing records need to be fixed up so there are none without a name.

Username should be removed from Projects table, or at least the field name prefixed with "z" so you know it's on its way out and should not be used anymore.

Then a query like:
select ID, UserName from tblEmployees where Role="TPM" and IsActive=False

will return inactive users in that role. If an expected record is not being returned, there can be only a few reasons:
1. Not in the table at all
2. Not in the TPM role
3. IsActive is not set to False

In future you may want to normalize the Role field as well: make it RoleID and add a Roles table (RoleID, RoleName) with TPM and other roles.

Don't forget to use the Relationships window to setup relations AND ENFORCE THEM.

---

-Tom. Microsoft Access MVP

Answer 2

I'll take your suggestions under advisement.  They make sense.  However, even with those changes, the issue would still be there.  In the combo box, when I open an old project where the TPM is no longer with the company, his/her name doesn't appear (because it's not in the drop down menu?). 

For old projects I want to see who was assigned, regardless of whether they are still with the company (VP = either, yes or no).  And for new projects I only want to see employees that are still with the company (VP = Yes).  And I want to use the same form for both. 

Answer 3

I think I figured it out.  In the project table I have a checkbox for marking the project complete.  Using VBA and the onLoad function I can say:

if Project_Complete = no, then cboTPM.rowsource = "Select tblEmployees.ID, tblEmployees.Usernames, tblEmployees.Role FROM tblEmployees Where tblEmployees.Role = "TPM" AND tblEmployees.ViewPreference = yes". 

or

if Project_Complete = yes, then cboTPM.rowsource = "Select tblEmployees.ID, tblEmployees.Usernames, tblEmployees.Role FROM tblEmployees Where tblEmployees.Role = "TPM".  Leaving out the AND tblEmployees.ViewPreference = yes" part

Answer 4

One other way to approach this is to always have all employees in the dropdown, and sort the active ones to the top, and for new records have a line of code that checks if this is an active one:

(in the dropdown's BeforeUpdate)
if me.myEmployeeDropdown.column(2) = False and Me.NewRecord then
  msgbox "Yo! Choose an active employee"
  Cancel=True

The rowsource for this combo would be 3 columns:
select ID, UserName, IsActive from tblEmployees where Role="TPM" order by IsActive Desc, UserName

---

-Tom. Microsoft Access MVP

Answer 5

I had considered figuring out some way to show all and sort so the old employees were at the bottom but ruled that out.  Thanks for the tip though.

Answer 6

Assigning a string expression to the RowSource property of a combo box works fine in a form in single form view, but in continuous forms inactive or former employees will still be hidden if a row for an active employee or an empty new row is selected, in which case inactive or former employees will be removed from the list.  As, in a continuous form, rows with inactive or former employees will still be visible the employee control in those rows will appear empty.

This can be avoided using a hybrid control, in which a text box is superimposed on a combo box to give the appearance of a single combo box control.  The employee's name is shown in this text box so, while the underlying combo box will be empty, the name will still show.  You'll find an example as ActiveEmployees.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the projects parent form contains a continuous subform of employees assigned to the project.  The subform contains a hybrid control set up as described above.

---

Ken Sheridan, Stafford, England

Answer 7

That sounds like a brilliant idea.  I'll give it a try but I'm sure that will take care of it, without scripting.  Thanks