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.

 

[miccysoft]

you could protect the cells then only allow them to edit them through macro forms and input boxes.

alternatively you can have your stats filter them out. if its a pivot table for example make a column that checks if there is a date closed and the status is not done and then you can filter by that column

 

[Norie]

Billy

Have you looked at the On Exit/On Change/On Enter events of a textbox on a form?

Depending on how exactly you have the form set up theses could be used to prevent the date completed field being entered unless the staus field was 'All done'.

 

[caringsharingbristolbilly]

Norie; I'll have a look at the stuff you suggested. I've not used that functionality before (I'm a relative newbie), so I might be back with a subsequent question!! Thanks for the pointers, though...

Miccysoft; Thanks, all sounds a little complicated for the type of data I'm using. I think I'll go with Norie's idea for now, but thanks for the suggestions.

Many thanks to you both...

 

[GBlack]

I think an easy way to do this would be to take out the 'date completed' field on the form.

Then set it so that Access updates the 'date completed' field (in the query or Table your form is linked to) automatically, any time (C) All Done is clicked.

This way your users have one less thing to worry about and your table/query is updated only when they are finished.

Later
Gary

 

[caringsharingbristolbilly]

Hi again guys.

I'm starting this thread again, because when I originally posted it, I eventually deemed it unnecessary. Now, however, it appears that I've underestimated the muppetry of the people using my database.

The way Norie suggested doing things looks like the best. Could someone give me a brief example of how to do this? I'm still very much an Access newbie, so some help would be fantastic.

The Access help files also suggested that ValidationRule & ValidationText might be the way to go. Any ideas?

Any help would be hugely appreciated.

CSBBB.

 

[mdmilner]

Build a form based on a table as it's recordsource.
You'll need to change the properties of the field you wish to protect to either Locked (no edits) or hide it (Visible=False)

In the after_update event of the listbox (or combobox) put something like.

If Me.lboName.Value = "valid value" Then
Me.lboDate.Visible = True
' or Me.lboDate.Locked = False
End If

On the other hand, going back to a prior post by GBlack in this thread.
His is a much simpler method. It would be rather easy to adapt the above to insert the current date/time into the same date field.

When you base a form on a table, you gain the ability to reference the field values on the current active record directly using syntax like:

Me.fieldname.Value

In the above if statement, put something like.

Me.fieldname.Value = Date & " " & Time

Which just concatenates the current system default Date/Time together into the field as soon as you up date with the current value.

You might wish to make your logic a bit more complex though. Not sure if you want the code to update the completed date/time each time a user clicks into the prior listbox - or if you even want them to be able to change it ever.

Mike

 

[caringsharingbristolbilly]

Hi again guys, specifically mdmilner (Mike).

I've been looking at what you suggested.

In the first section of your post, you wrote:

If Me.lboName.Value = "valid value" Then
Me.lboDate.Visible = True
' or Me.lboDate.Locked = False
End If

Could you tell me how I'd alter the first line to be an "or" function too, as there are a couple of values that would allow it?

Also, where do I put all that? Do I go to the code builder from the properties section, then add it in where it says:


Private Sub Date_Completed_AfterUpdate()

End Sub

I'm utterly baffled by VB code at the moment!

Many thanks, I know a lot of you are probably smacking your foreheads in frustration at me... I just don't know enough to get started yet!!

Cheers,
CSBBB

 

[mdmilner]

Using the above example, the or written into it was setup as a comment with sample code.

If Me.lboName.Value = "valid value" Then 
Me.lboDate.Visible = True 
' or Me.lboDate.Locked = False 
End If

Anytime you see an apostrophe ' it means the line is commented out.
In this case remove the apostrophe and the word or and use the rest o the line and it'll execute as good syntax.

If you'd like to use multiple conditions in your test IF statement, it looks like this:

If test_variable = "valid value" OR test_variable2 = "a valid value" Then

actions to perform

End If

You can also use the ELSE clause to handle if the IF statement evaluates to false.

If test_variable = "valid value" OR test_variable2 = "a valid value" Then

actions to perform

ELSE

other actions to perform if the test is false

End If

Mike

 

[caringsharingbristolbilly]

Hi Mike.

Many, many, many thanks for your patient reply. It's massively appreciated. ray: I think I'm beginning to get it.

Could you clarify where I need to type that lot in? I've not used code before, but if you could point me in the right direction, you'll have set me well on my way to becoming a VB guru no doubt!

Thanks again,
CSBBB.