Re: Validating ComboBox Entries

Sorry, Duncs, but we are right back where we started.
You can't have it both ways. Either the project is going to show in the
combo box or not. What you are asking seems totally illogical. You don't
want someone to enter time against a project with no budget, so they should
not see the project in the combo, but if you are looking at historical data
you want it in the combo.

I guess the questions now are how do you select historical data and should a
time entry form also be used for looking at history?

So, I see two ways to accomplish your needs. First, as I suggested earlier,
When a user selects a project, present a message box warning that this
project can have no charges against it and lock the controls so they can't
enter anything. You would still be able to see it when looking at history.
The other way would be to restrict this form to time entry only and present
only active projects and create a different form for view history.

"Duncs" wrote:

If I filter out all expired projects from the combo, viewing historical
records--which go back to the start of the financial year--does not
show the projects that have expired.

For example, "Project Alpha" has been worked on every day during the
month of May by two employees and it has a "Valid Until Date" of 1st
June, 2006. On the 31st May, when I view all records within the
database, the project name is displayed in the Combo control.

However, on the 1st June, when I view the combo, "Project Alpha" isn't
listed. However, when I view all records for the month of May, where
the employees have worked on "Project Alpha", the Combo control is
blank. This is using the RowSource value of:

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

What I need to happen on the 1st June, is:

1. The project to no longer appear in the list of available
projects, to avoid an
employee accidentally selecting a workstream that no longer
has a
budget
2. The entries in May to show "Project Alpha" in the Combo
control, for the two
employees who worked on it.

With the RowSource above, part 1 of my needs is fulfilled. However,
part 2 does not happen. The Combo Control is blank.

In your final comment, you say "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 guess I don't need them to show in the combo control if
they are expired, but I do need them to show for all entries where they
have been selected, prior to the Valid Until Date.

Duncs

Klatuu wrote:
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








.