Solved Help with Logic Check for values in fields

[mloucel]

Hello Gurus:

I have an issue with my LOGIC to create a routine where:

The Form is a bound form, that contains already some data.
So when I close the form I need to check 3 dates

If the user decides not to fill the dates is easy to check:

IsNull(MyFirstDate)

so I know the date(s) where left without a date or has a date.

problem I have is when the user comes back, and then decides to make a change to the date.

What type of code can I use to check both things in the same code?

Ie.. this has to happen for the 3 dates I need to check..

If date is null then just continue
if date old value <> Value on the table then
ask user if is ok the changes.
But
If date is not null then save the new data

I don't know how to check, since the form is bound to database.

Any Ideas will be appreciated.

 

[tvanstiphout]

Code would be in Form_BeforeUpdate, because that is a cancellable event (Cancel=True).
If Me.MyFirstDate.OldValue <> Me.MyFirstDate then ...
Cancel = (Msgbox("Yo! You OK with saving the new MyFirstDate?", vbQuestion or vbYesNo) = vbNo)
...

I would find it highly annoying if I had to confirm each save, but maybe it makes sense in your scenario.

 

[mloucel]

Code would be in Form_BeforeUpdate, because that is a cancellable event (Cancel=True).
If Me.MyFirstDate.OldValue <> Me.MyFirstDate then ...
Cancel = (Msgbox("Yo! You OK with saving the new MyFirstDate?", vbQuestion or vbYesNo) = vbNo)
...

I would find it highly annoying if I had to confirm each save, but maybe it makes sense in your scenario.

Thanks Tom, yes it is annoying but each date is highly important, the user must be set accountable [per my boss] and since the record [each record] saves a log of who was the last person that changed the record, someone will be accountable anyway.
Logs will be deleted monthly by an admin or I will run a delete routine [I'm working on that] to perform that task.

Thanks for the code, I worked on it for a bit and it worked the way it is supposed to be, just some minor changes, since I need to annoy the user as soon as they finish with the date, so form before update didn't cut it, I used the more annoying LostFocus and it worked..

 

[The_Doc_Man]

As to "how to check" you can use Tom's suggestions. As to "when to check" that is up to you, but a change doesn't become permanent until the form updates. So the best time is to do a check is in the Form_BeforeUpdate event, which has a Cancel option.

You do your tests in the BeforeUpdate event. THEN if you want to disallow the changes, you can Cancel the update (Set the Cancel parameter to -1.) The best place to check is there. (Not the only place - but the best place.) This gives you the option do either Undo all updates (Me.Undo) or give your user a chance to make another change.

Note that if you have ANY part of the form still "dirty" i.e. updated with respect to each control's .OldValue, that form WILL have to eventually have a Me.Undo or a Save operation. Not only the dates, but ANY bound control would have this issue. Since you say the form is bound, you don't necessarily have to create a Save button. You could do it by navigation, or with one of the default controls that Access puts on a form. But dirty forms must be reset & cleaned or saved in some manner, one or the other.

 

[tvanstiphout]

Thanks Tom, yes it is annoying but each date is highly important, the user must be set accountable [per my boss] and since the record [each record] saves a log of who was the last person that changed the record, someone will be accountable anyway.
Logs will be deleted monthly by an admin or I will run a delete routine [I'm working on that] to perform that task.

Thanks for the code, I worked on it for a bit and it worked the way it is supposed to be, just some minor changes, since I need to annoy the user as soon as they finish with the date, so form before update didn't cut it, I used the more annoying LostFocus and it worked..

LostFocus might work, but <control>_BeforeUpdate would be more appropriate if you need instant check, again because this is a cancellable event.

 

[mloucel]

As to "how to check" you can use Tom's suggestions. As to "when to check" that is up to you, but a change doesn't become permanent until the form updates. So the best time is to do a check is in the Form_BeforeUpdate event, which has a Cancel option.

You do your tests in the BeforeUpdate event. THEN if you want to disallow the changes, you can Cancel the update (Set the Cancel parameter to -1.) The best place to check is there. (Not the only place - but the best place.) This gives you the option do either Undo all updates (Me.Undo) or give your user a chance to make another change.

Note that if you have ANY part of the form still "dirty" i.e. updated with respect to each control's .OldValue, that form WILL have to eventually have a Me.Undo or a Save operation. Not only the dates, but ANY bound control would have this issue. Since you say the form is bound, you don't necessarily have to create a Save button. You could do it by navigation, or with one of the default controls that Access puts on a form. But dirty forms must be reset & cleaned or saved in some manner, one or the other.

You now what @The_Doc_Man you and @tvanstiphout have way more experience than me, and thou I am big hard head, I have to get rid of my old FoxBase/Cobol habits and follow the leaders.

So I am gonna follow your advise and re-do it tomorrow as soon as get to the office.

Thanks.

Maurice.

 

[Pat Hartman]

Thanks for the code, I worked on it for a bit and it worked the way it is supposed to be, just some minor changes, since I need to annoy the user as soon as they finish with the date, so form before update didn't cut it, I used the more annoying LostFocus and it worked..

No, it didn't. You just think it did. I guarantee that I can get bad data to save using your current method. The correct method is the form's BeforeUpdate event as was suggested if you actually want to prevent the saving of bad or incomplete data.

When multiple fields are involved in a validation check, you cannot actually check the values in the individual control events because, how do you know that the user has finished with the data entry. For example if dt1 must be <= dt2, how can you validate dt1 if dt2 has not been entered? If you change dt2 after dt1 has been validated, you must also validate dt1 again. The logic is far simpler if you do it in the form's BeforeUpdate event since once that event runs, the user has made his intent clear. He is done. Therefore all fields should have valid values. If you want to do it one field at a time, you can use a sub that is called from all three control's BeforeUpdate events. BUT, the code must allow the user to contrinue because, the validation cannot actually be performed correctly until ALL controls have been populated. Once you leave these lose ends, you need to duplicate the validation but with no lose ends in the form's BeforeUpdate event so you end up with slightly different code to do the same thing but called from different events. Bad programming practice.

Please rethink your process and if you carefully test what you have, you will see that I am correct and that your code is not actually preventing bad data from being saved. Just popping up error messages doesn't do anything. The part of the code that does something is the Cancel = True that tells Access to not save the dirty record.

 

[mloucel]

No, it didn't. You just think it did. I guarantee that I can get bad data to save using your current method. The correct method is the form's BeforeUpdate event as was suggested if you actually want to prevent the saving of bad or incomplete data.

When multiple fields are involved in a validation check, you cannot actually check the values in the individual control events because, how do you know that the user has finished with the data entry. For example if dt1 must be <= dt2, how can you validate dt1 if dt2 has not been entered? If you change dt2 after dt1 has been validated, you must also validate dt1 again. The logic is far simpler if you do it in the form's BeforeUpdate event since once that event runs, the user has made his intent clear. He is done. Therefore all fields should have valid values. If you want to do it one field at a time, you can use a sub that is called from all three control's BeforeUpdate events. BUT, the code must allow the user to contrinue because, the validation cannot actually be performed correctly until ALL controls have been populated. Once you leave these lose ends, you need to duplicate the validation but with no lose ends in the form's BeforeUpdate event so you end up with slightly different code to do the same thing but called from different events. Bad programming practice.

Please rethink your process and if you carefully test what you have, you will see that I am correct and that your code is not actually preventing bad data from being saved. Just popping up error messages doesn't do anything. The part of the code that does something is the Cancel = True that tells Access to not save the dirty record.

Good point Pat, is just that my boss is HARD HEADED.. my only choice is to modify as suggested and never show that part again until the whole project is finished, by that time I hope she doesn't even remember her name..

 

[Pat Hartman]

You can put the phoney tests in to placate the manager but there is nothing stopping you from doing it right to actually prevent the bad data from being saved. I always feel that I have a fiduciary responsibility to keep the client's data safe and as clean as possible so I am a stickler for real rather than phoney validation.