Re: Validating ComboBox Entries

Klatuu,

Many thanks for your reply.

The main reason I want to prevent employees selecting a project that
has expired, is one of budgets. Previously, employees have
accidentally sleected an invalid project code, then at the end of the
month when we pass the details to our accountants for re-charging, we
find that several hours have been charged to a now invalid project. As
the one employee can work on several projects at the same time, it
becomes impossible for them to tell what project the time _SHOULD_ have
been logged against.

So, the idea of the "Valid Until Date" or "Project Expiry Date" was
born. The employee can work on the project up to and including the
expiry date, but after that date there is no more money in the budget
for work, so no more time can be spent on it.

Given your scenario listed below, if I were to use the "AfterUpdate"
event, I would have to display a Message Box or something similar to
the user, advising them of the invalid selection. The project,
although expired, would still appear in the list and would still be
selctable by an employee. What I wanted to do was remove the project
from the list display, since it has passed its expiry date. This would
prevent the user from selecting the project, either accidentally or
intentionally, and would subsequently prevent me from displaying a
message box if they have selected an invalid project code.

From the four scenarios you have listed below, only 1 & 2 are valid.
Whether or not a project has work completed on it within the last month
or not, is really of no relevance to anything.

Many thanks for your reply, and would welcome any other ideas you may
have.

Duncs


Klatuu wrote:
You really can't have it both ways. Either it is included in the combo row
source or it is not; however, if what you are after is to restrict processing
based on the Valid Thru Date, then rather than filtering your row source on
the date, you may want to consider filtering in on whether or not someone has
worked on the project in prior months. It appears you want four situations.
1. The project is Active = ValidThruDate <= Date
2. The project is InActive = ValidThruDate > Date
3. The project is InActve, but had work in prior month
4. The project ins InActive, but had no work in prior month

I don't know if the rules for 3 and 4 or correct or what you want to do with
time, but, I can suggest that rather than filter on the date, use the After
Update event of the combo box to determine what to do:

If Me.MyCombo.Column(2) > Date Then
'Do Whatever you need to deal with inactive projects.
End If

"Duncs" wrote:

I have a form that contains a ComboBox control, which displays the
following information:

Project Description
Project Code
Valid Until Date

The table definition is as follows:

tblProjects.fldProjectDescription
tblProjects.fldProjectID
tblProjects.fldValidUntilDate

What I want to happen, is when a project reaches its ValidUntil date,
it should no longer appear in the drop-down list. However, where an
employee has created a valid entry in the past, the project description
should still be displayed.

I have created my Combo Control as follows:

ControlSource tblTime***.fldProjectID
RowSource SELECT tblProjects.ProjectDescription,
tblProjects.fldProjectID,
tblProjects.fldValidUntilDate
FROM tblProjects
WHERE tblProjects.fldValidUntilDate > Date()
ORDER BY tblProjects.fldProjectID;

BoundColumn 2

With this setup, the drop-down does indeed restrict the entries to
those with a ValidUntil date in the future. However, it does not
display an entry in the ComboBox control for those projects that have
passed their ValidUntil date. For example, if "Project Alpha" has a
Valid Until date of 1st June, 2006, "Project Alpha" would not appear in
the drop-down. However, if any employee has worked on "Project Alpha"
in the month of May, the project details will not be displayed.

Can anyone suggest a way of doing what I want the control to do?

Many thanks & regards

Duncs



.

Loading