Re: Validating ComboBox Entries

Effectively, you are filtering out expired contracts.
The list of projects in the combo cannot be viewed until the combo has the
focus. When it gets the focus, you filter out expired contracts so they will
not bee seen. Then the removing the filter in the Lost Focust puts them back,
but again they will never be seen. So the effect is, you are filtering yout
expired contracts.

Now I am confused as to why you think you need them to show in the combo
list if they should never be selected. I just don't understand your intent.

"Duncs" wrote:

Klatuu,

I may have solved it. It may not be the best way of doing it, but it
seems to work.

The Combo Control defualts to a RowSource of:

RowSource SELECT tblProjects.ProjectDescription,
tblProjects.fldProjectID,
tblProjects.fldValidUntilDate
FROM tblProjects
ORDER BY tblProjects.fldProjectID;

This ensures that ALL entries, expired or not, are displayed in the
control.

In the "GotFocus" event of the control, I alter the RowSource to:

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

and then requery the control. This ensures that the control only
displays those projects that have not expired.

Finally, in the "LostFocus" event of the control, I reset the RowSource
back to:

RowSource SELECT tblProjects.ProjectDescription,
tblProjects.fldProjectID,
tblProjects.fldValidUntilDate
FROM tblProjects
ORDER BY tblProjects.fldProjectID;

Which will show all project information in the field.

As I say, I don't know whether it is advisable to do this or not, but
it works and suits my purposes.

Duncs



Klatuu wrote:
There is one other thing I thought of that may help if you want expired
project to be viewed by prevent any time from being entered into them would
be rather than presenting a message box in the After Update event would be to
lock the controls on the forms so nothing could be entered.

"Duncs" wrote:

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