Form Validation aka Am I cracking up (1 Viewer)

[theDBguy]

I suppose I could use validate form or duplicate validation code which I don't want to do

Hi. I know you're just trying to be nice, but if the user changed something they're not supposed to, it should be their responsibility to put it back the way it was. In any case, try the attached modified version, which uses the OldValue property.

 

[Micron]

Maybe I missed what the error message is when using the close button after an edited record isn't saved. Is it "The unload event was cancelled" or something like that? If so, I've encountered this before and the solution is to trap it in the close event.

 

[ryetee]

Maybe I missed what the error message is when using the close button after an edited record isn't saved. Is it "The unload event was cancelled" or something like that? If so, I've encountered this before and the solution is to trap it in the close event.

No it's not it's I get a run time error 2101. The setting you entered isn't vali for this property.
I think it's because access knows I'm about to go into an infinite loop!

I have if me.dirty=true the me.dirty=false end if
This forces the code into the beforeupdate which I want to put a cancel = true. But that cancel = true (I think) cancels the me.dirty=false as well as doing what I want it to do so stepping through the code it wants to go back into beforeupdate after setting me.ditry back to false which then.....infinite loop!

 

[Micron]

I see now that I did miss that. Does the sample db from post 21 solve the issue?

 

[ryetee]

I see now that I did miss that. Does the sample db from post 21 solve the issue?

yes it does!!

 

[theDBguy]

yes it does!!

Hi. Glad to hear you got it to work. Good luck with your project.

 

[ryetee]

Hi. Glad to hear you got it to work. Good luck with your project.

I spoke to soon. The form I gave you is quite simple! In my first post I mentioned "I do have a slight problem as I have a combo box which allows me to jump to a specific record but I'll post about that later.". The combo box I refer to is in the form header. If you blank out the description and try to use the combo box you get an error. This is down to the if me.dirty = true etc causing an infinite loop. I replaced this with if validateform and for this form it works great. However other forms using other tables I have the 2 following lines in basically the beforeupdate for the form
me.updatedby = "name"
me.updated on = now()

I now get Update or CancelUpdatewithout AddNew or Edit if I try to jump to a record from a record that has no validation problems. This has got something to do with Me.Bookmark = rs.Bookmark. I think this sends the code back into the beforeupdate again and second time through this code causes the problem.
Hope this makes sense. I've made changes to the simple form to reflect this and have attached here (It won't actually upload my file - I'll try on another post)

 

[ryetee]

Finally managed to load. Hope you're still around theDBGuy!!

 

[theDBguy]

Hi. I just downloaded your file but I am not getting any errors. Can you provide a step-by-step instruction on how to duplicate the problem? Thanks.


PS. Never mind. I found it. Check out the attached modified version. Hope it helps...

 

[ryetee]

Hi. I just downloaded your file but I am not getting any errors. Can you provide a step-by-step instruction on how to duplicate the problem? Thanks.


PS. Never mind. I found it. Check out the attached modified version. Hope it helps...

LOL I thought I was going mad there as when I first read your unedited post in the email you didn't have the PS in and then when I downloaded your file I didn't even look at the text so didn't even realise you'd edited it and didn't see the PS again! I couldn't work out why the DB was different from my post 28 but it was strangely familiar. Then I remembered I'd actually had the same solution at one time but discounted it (see below) and because I had problems attaching the file the other day I wondered if at one stage I'd managed to upload an old version and then overwrite it (told you I was cracking up) which wasw hy the 2 DBs were different!

Anyway this version isn't correct as those 2 fields are only updated when you use the combo box to move to anther record. Also I only want them updating if the record has changed. They need to be (I believe) in the beforeupdate (and not where I ave them in the validateform). Either way I get the error suggested in post 28. I've tried adding all sorts to cboStartTaxCategory_Click but to no avail. It either falls over on the me.updatedby or me.bookmark = rs.boomark.
I somehow have to finish with the current record completely before going into cboStartTaxCategory_Click otherwise I'll trigger beforeupdate wich causes the problem
Hope this makes sense

 

[theDBguy]

Ah, okay, makes more sense. Try this one then...

 

[ryetee]

The closest I've ot is by moving the me.updatedby into the beforeupdate and having a boolean that doesn't do the update if set to false so I have for 2 subs

Private Sub cboStartTaxCategory_Click()
Dim rs As DAO.Recordset
updateme = True
If Not IsNull(Me.cboStartTaxCategory) Then
'Save before move. Took out if me.dirty etc
If ValidateForm Then 'think this saves it
'Search in the clone set.
updateme = False
Set rs = Me.RecordsetClone
rs.FindFirst "[ID] = " & Me.cboStartTaxCategory.Column(0) & ""
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
Else
MsgBox "Cannot jump to record until form passes validation.", vbInformation, "Required!"

End If
End If
updateme = True
End Sub

and

If updateme Then
If ValidateForm = True Then
Me.txtUpdatedBy = "me"
Me.txtUpdatedOn = Now()

Else
Cancel = True

End If
End If

This gives me the right error messages and doesn't fall over, however it doesn't update me.updatedby or me.updatedon!

 

[theDBguy]

Hi. Does this mean the lats one I sent you didn't work? Could you please give me a step-by-step instruction to try with a description of what is happening and what you expect to happen instead? Thanks.

 

[theDBguy]

Hi. I gave it another try. Please see attached...

 

[ryetee]

Hi. Does this mean the lats one I sent you didn't work? Could you please give me a step-by-step instruction to try with a description of what is happening and what you expect to happen instead? Thanks.

Yes. In a nutshell all the validation works but those 2 fields are only updated when the combo box is used to select another record. And they are always updated. They should only be updated if the record has been changed. I'll take a lok at the attachment in your next post

 

[ryetee]

Hi. I gave it another try. Please see attached...

still not quite there. i'll add explanation tomorrow as i'm ready to drop.

 

[theDBguy]

still not quite there. i'll add explanation tomorrow as i'm ready to drop.

Okay, when you get a chance, a step-by-step instruction to duplicate the issue would be nice, so I can have something to go by when trying to fix it.

 

[ryetee]

Okay, when you get a chance, a step-by-step instruction to duplicate the issue would be nice, so I can have something to go by when trying to fix it.

OK this almost works but not quite.

What do I want? This is a simple form but I have more complex ones with validation on more than 1 field. The user can make changes to none or more of the the controls on each form. He can then move off the current record by using one of the navigation buttons, the close button or by selecting another record from the drop down box in the form header. When he does that the form is validated and if there are any errors on any control then that control gets the highlighted, a suitable error message is displayed and the old value restored. The user can then either keep the restored value or change to a valid one and then move from the current record as per above where the form will get validated again. If all the form is correct then updatedby and updatedon will also get updated with the user name (in this case = "me" ) and the date and time.

The problem with this is currently if another field other than description gets amended as well as the description and description has an error then updatedby and updatedon are "undone". If the user then decides not to change the description then updatedby and updatedon are left inchanged even if other controls have been changed. Commenting them out means that they are never reset when they should be ie in the case that description only is changedto an invalid value and then left as the old value.

Hope this is clear....

 

[theDBguy]

Hi. Not sure if I understood all the nuances and was able to address them all here but take a look at the attached updated copy. Hope it helps... If not, please let me know what I missed. Thanks.

 

[ryetee]

Hi. Not sure if I understood all the nuances and was able to address them all here but take a look at the attached updated copy. Hope it helps... If not, please let me know what I missed. Thanks.

Yeah I think you got them but this still doesn't quite work.
This scenario is unlikely to happen on this form but it could on others.
So user goes in and blanks the description out only. He then tries using the combo box to skip to another record. The logic tells him there is an error and sets description back to old value. All good so far. Now this could happen if the user is ham fisted and hits the space bar when the description is highlighted. So what is the problem? If the user then tries to go to skip to another record now using the combo box he hits the code "If Me.Dirty Then Me.Dirty = False" wich takes him into the code beforeupdate which updates the updateby and updateon fields when it shouldn't . In this sceenario I Think the beforeupdate event is triggered as well if the user uses the close or navigation buttons.

So why, in this case, is the form dirty if the field has been reset to the old value. Is there way of 'undirtying' the form at this stage?