Prevent data entry based on another field's value?

[caringsharingbristolbilly]

Hi to all my charitable friends in MrExcel.comland...

I've got a form-based database which members of staff are updating to show when a work-item has been completed. On each record, they can select from a 'status' field to show how they're getting on with it... There are three main choices for this 'status' field -

(a) Haven't started it yet
(b) I've started it, but it's not finished
(c) All done.

There is also a 'date completed' field, which they fill in once they have finished the work.

The problem is, people keep filling in this 'date completed' field when they're still at stage (b), which is playing havoc with my stats.

How can I stop people entering a date in the 'date completed' field, when they haven't actually completed the work?

Any help would, as always, be very much appreciated.

 

[mdmilner]

There's two ways to setup code to execute in a database. The basic method is to put them into a new Module (check the tab). VBA modules can also be attached directly to Forms -- copy the form to a new database and the code goes with it.

My personal preference is to only put database/functionality specific functions in the form, and anything more universal gets moved over to a general purpose VBA module. The main difference is how you reference other objects within the mdb.

For your purposes, the simplest way is just to put it behind the form.
Go back to Norie's suggestion and my first post. Open your form up in design mode.

Events: Events are actions that a user performs that triggers some code.
The after_update event, for example, triggers after a user types in a new value to a field but before they exit it (such as by tabbing out or selecting something else). It's useful because it captures the current value after it's been changed by a user...and happens each and every time they change it.

Check the built-in VBA help for other event names. Also, look at the list on the properties page you're about to work thru for some names you can research.

In whichever object you will use (textbox, listbox, combobox) on the form, right-click it and hit properties to open it up. Go to the Events tab, and in the After_Update event click into it. Use the down arrow on the right to change it to [Event_Procedure] then click the three dots just to the right.

This 1) Creates the attached VBA module for the form. 2) Creates an after_update event for the control.

Add the below code. Be certain to substitute the correct control names.
As a hint, just type Me. (Me and a period) and then look at the drop down box that shows up. As you start to type, it attempts to match the word you're typing.

Last piece is to go into the control you're modifying (the object you're going to turn to true or unlocked) and set it by default to the opposite (hidden or locked) in design mode. (you will need to close what you were doing, and open the other object up in design mode.

Mike

 

[caringsharingbristolbilly]

Many many many thanks. It works like a dream!

Really appreciated this, you've been clear with your instructions, and incredibly patient, so a massive thank you!

I am truly not worthy. ray:

This is what the board's all about. Well done that man.

Cheers,
CSBBB.

 

[mdmilner]

You're most certainly welcome.
Now if only I could get a job explaining this kind of stuff!

There's a lot of people who give really good and specific answers on this board. In my opinion, it's easily the most responsive I've seen yet on VBA questions - none of the arrogant attitude you usually see elsewhere. That in particular is why I post at all here.

Personally, I still think I'm an amateur because on any given day it's a toss-up on whether somebody will post a specific technique that I've never seen before or not.

Mike