Combo box row source

[Alockhart]

Hi there, I am working on a db to track changes and who did them. Right now I have a form that is filled in by someone and there is a combo box where then put there name or the name of the person doing the change in and it does a lookup on there name in a table to ensure they are still employed with the company. But my problem is when that person is no longer employed with us there name no longer shows in that combo box.
Can someone help me out or suggest some ideas that I could try.

Thanks

 

[willir]

Alockhart

This is a data integrity issue...

In general, do NOT delete "master" records. Instead, have a flag (boolean) on the employee file for active / inactive, or a date field on when the employee left the company. Use the flag to ensure the employee is not included in assignments, but is still referenced on linked records.

(So when can you delete??? Depnds on your design, but you have to plan accordingly. If this is a history / transaction file, then you will eventually be able to delete them. Or re-assign the responsibility to another employee.)

Work-arounds...
- Instead of linking the employee's number, paste their name into a text field. Breaks a rule for normalization, but lets you delete the employee record.
- Create a dummy employee, "Past Employee", and asign the dummy to the linked record to replace the terminated employee. Looks messy, but it works. Variation of assign task to another employee. This work around is more for a transaction file where the detail is less important. If you are linking ownership or responsibilities, then you should probably reassign to another employee.

Richard

 

[Alockhart]

I am flagging the employee with the date that they leave the company. When I do my check on that table I check to see if the date is null or is > then today's date then I know they are still employed with us.

Also once the change is completed the row is flagged with a date that the changed was completed on as we want to keep all records so we can track what was changed in the past.
And that is when I run into troubles because if the changes is still ongoing it is re-assigned but once it is completed we don't want to touch that row.
So if that employee leaves the company and we go back and look at the history of what was changed by them that combo box is now empty because the row source looks for only currently employed employees.

Andrew

 

[KenReay]

Hi

You could change the rowsource of the combo box to select all employees (including those who have left) if the chnage is a completed change, and have it as now for an on going change, you would need to requery the combo on change of "Change Record" row.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[willir]

Actually Ken is correct...

Use On Current (or On Dirty, or on Before Update) record to toggle between selecting for all employees and active employees.

You will need to know if the current record is accessing an terminated employee, or using a new employee.

Syntax, is fairly simple...

If ... then
strSQL = ....
Else
strSQL = ....
End if

Me.YourFieldName.RowSource = strSQL
Me.YourFieldName.Requery

Richard

 

[Alockhart]

thanks for your help I will try that.

 

[Alockhart]

Thanks for your help that worked great.