Re: Validating ComboBox Entries

Disagree if you wish, but you obviously don't undertand what you are doing.
Your analogy doesn't even hold up. In fact, it makes my point. Because the
label says "Beans" doesn't mean there are beans on the shelf, it only means
the store sells beans. If you try to buy beans and see the shelf is empty,
then maybe you will have potatoes instead.

If you have found a way to not include the item in the combo, but still
allow the user to select it and view it but not edit it, I would very much
like to see that solution. In 28 years of application development, I have
never seen it done.

"Duncs" wrote:

I've spent the weekend mulling over your comments and, unfortunately, I
still disagree with you.

If you go to a shop to buy a tin of beans and they are there on the
shelf, you can buy them. However, if for some reason the shopkeeper
wants to prevent you from buying them, he simply removes them from his
shelf. When you go into the shop, you can see that the shopkeeper has,
as some point, sold beans, as the sticker with their name and price is
still on the edge of the shelf. Once you've left the shop, he can then
put the beans back on the shelf.

It's the same as what I want to do, and in no way appear contradictory
to me.

I want people to be able to record work against a project, when the
project code is valid and has a budget. However, once the budget has
expired, the project should no longer appear in the list. Whilst the
project can no longer be selected, I should still be able to see what
employees have worked on it, from a historical perspective.

If there were a way to prevent the items appearing--which I believe I
have found--this prevents the need for an unecessary Msg Box being
displayed, which holds the user up.


Klatuu wrote:
Okay, so how do you handle the ability to display the data in the form and
not allow the user to enter data but not allow the user to select the data?

To be able to view the data, it has to be in the combo's rowsource or you
can't select it.

To prevent a user from entering any data, it can't be in the rowsource.

The two statements above are contridictory, but you seem to want it to be
both ways. Can't happen.

Now, if you don't want to present a warning message, that is okay, but the
message would give users information.

I did not say to lock the controls just until the user responds to the
message box. That would be useless because you can't do anything else until
you respond to a message box.

What I am suggesting is to leave all projects in the combo. Test to see if
the project has expired. If the project is active, allow the user to enter
data. If the project has expired, lock the controls so they can only view
the data. Whether you can understand this concept or not, it gives you
exactly what you are trying to accomplish. This does not require a message
box, but for user friendliness, it does let them know they have selected an
inactive project and can't enter data. That prevents them from thinking
something is wrong with the application when they try to enter data and can't.

"Duncs" wrote:

You're right.

If there is no more money in the budget for a particular project, I
don't want the project to be displayed in the Combo Control. This will
prevent employees from selecting it and causing havoc at the end of the
month for both myself and the accountants.

Also, whilst I don't want it shown and selectable if it has expired, I
still need to see it in the employees historical entries.

Totally illogical?

What I think is totally illogical is:

1. Displaying a message box and locking the forms controls, until the
employee clicks on the OK button.
2. Creating a separate form, that will be identical to the original
one, for viewing historical data.

Why should the user have to view data on a separate form?

Why should the user have to press a button before continuing, when the
data can simply be prevented from being shown?

Surely making data entry as easy as possible--by not showing expired
projects--is more "User Friendly".

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

Quantcast