Validating ComboBox Entries

[Duncs]

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 tblTimeSheet.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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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".

 

[Guest]

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]

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.

 

[Guest]

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.

 

[Douglas J Steele]

"I'd like to buy some beans, please"

"Yes sir, that'll be $2.50 a can."

"$2.50 a can! They only charge 75 cents across the street!"

"Then why don't you buy them across the street, sir?"

"They're out."

"Oh, when we're out, we only charge 50 cents"

 

[Duncs]

I understand perfectly well what I want to do and indeed, have
expressed my wish / need / desire in every post I have made.

To use your interpretation of my analogy, an employee would work on
"Project Beans" today but find that the budget expired for "Project
Alpha" on the 1st June, 2006. They would then simply add their time to
"Project Potatoes" instead, resulting in "Project Potatoes" being
over-budget.

If I, and I'm sure many others, went into a store and saw a section of
a shelf, with the label "Beans" and the price of a tin of Beans, I
would see "Beans" on the shelf, not potatoes. The whole point is, if
there are no beans on the shelf, I cannot buy them. Linked to my
database, if a particular project is not shown in the drop-down list,
then it cannot be selected.

You made the statement "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."

This confuses me. I have never at any time suggested that I would want
the information to be editable. To clarify my request:

"Suppose you are my employee, and you work on "Project Beans" every day
for the month of February. On the 28th February, the project expires
as the work has been completed and there is no more money in the budget
to support work on it. On the 1st March, when you access the database,
you should not be able to see "Project Beans" in the list of
work-streams. However, if you or I want to look at the work you
completed in February, the form should show "Project Beans" in the
drop-down control field."

At no time in the above have I mentioned that I want to "allow the user
to select and view it but not edit" the expired work-stream.

I would refer you to thread 5, dated Wed, Jun 7 2006 3:10 pm. Here you
will see that I have managed to facilitate a way of achieving what I
want the control to do.

Duncs

 

[Guest]

Are you sure you want to get involved in this insanity?
Douglas, I would appreciate your input. Am I missing something here or is
Dunc just not understanding or refusing to accept the point?

 

[Guest]

I never said anything about being able to edit an expired project. In fact,
what I offered is a way to be able to view it without editing it.
You have made it very clear what you want to do.
I offered a suggestion on how you might do what you want.
You keep saying you want the user to be able to view an expired project, but
you don't want it in the combo list. So, my question to you is, how do you
expect to do that?

 

[Douglas J Steele]

To some extent, this sounds similar to the issue of having cascading combo
boxes on a continuous form. There, since there's on a single combo box for
all of the rows, what's in the combo box isn't necessarily appropriate for
each of rows.

One approach in that case is to have a text box that sits on top of the
combo box. You put the value you want in the text box, so it looks as though
it's in the combo box. Sandra Daigle has an example of this (Synch Combo
Continuous) at http://www.daiglenet.com/MSAccess.htm

 

[Duncs]

Yes, if a project has been worked on, I want to be able to view that
information in any historical records. My first attempt at a solution,
which was setting the Combo's RowSource to:

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

This meant that unexpired projects were not displayed in the Combo but
expired ones were exluded. However, as I viewed historical data, the
Combo Control would be blank for all those projects that had expired.

After experimenting with several options--including the one provided by
Doug--I came up with the |ntrol 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
to:

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

Which will show all project information in the field.

Whilst I say the control with "SHOW" invalid project or not as
appropriate, the user will never actually see the entries that have
expired, since the GotFocus event will remove them from view. But,
they will still be there for viewing in any historical data.

Duncs

 

[cranberryconsult]

Duncs,

I perfectly understand what you're trying to do and I'm looking for
that same answer myself. If you find a way to do it other than via the
'get focus' 'lose focus' please let me know.

To the person who's not understanding, what we're trying to do is not
allow further entry of a particular attribute. However, in a datasheet
form, we don't want to prevent the viewing of that same entry in
records where choosing that entry was valid at the time.

For example, a record might show 5/5/1628 'Buggy Whip Maker' as a valid
occupation for a particular person at that time. When I'm looking at
all occupations for all people, I should be able to see that record in
my datasheet. However, if I have a new row entry on my datasheet, and
I'm entering a record today, I do not want a user to be able to enter
'Buggy Whip Maker' as a valid occupation for 6/22/2006.

Thanks,
Cran

 

[cranberryconsult]

Duncs,

I perfectly understand what you're trying to do and I'm looking for
that same answer myself. If you find a way to do it other than via the
'get focus' 'lose focus' please let me know.

To the person who's not understanding, what we're trying to do is not
allow further entry of a particular attribute. However, in a datasheet
form, we don't want to prevent the viewing of that same entry in
records where choosing that entry was valid at the time.

For example, a record might show 5/5/1628 'Buggy Whip Maker' as a valid
occupation for a particular person at that time. When I'm looking at
all occupations for all people, I should be able to see that record in
my datasheet. However, if I have a new row entry on my datasheet, and
I'm entering a record today, I do not want a user to be able to enter
'Buggy Whip Maker' as a valid occupation for 6/22/2006.

Thanks,
Cran