Form Validation aka Am I cracking up (2 Viewers)

[ryetee]

I want to validate the fields on a form over and above the definitions that exist in the data definition (i.e. numeric, must be filled in etc).

I thought all I needed to do was put a bunch of ifs and elses in the beforeupdate part of the form with a setfocus a cancel = true possible a me.undo and maybe a message telling the user what the problem is.

I want to remain on the form so the error can be corrected. This works if trying to move to another record using the navigation bars. 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.

I also have a button on the form which closes the form. This is where the problem lies. If I introduce an error and then click the close button I get my error message, the field in error is restored but the form is then closed. I want it to remain open. Do I need my validation in both places (before update and also in the event for the button) or have I completely lost it.

 

[theDBguy]

Hi. No, you won't need to duplicate the validation code. You can simply set a form-level variable to set and check if it's okay to close the form after it passes validation.

 

[ryetee]

Hi. No, you won't need to duplicate the validation code. You can simply set a form-level variable to set and check if it's okay to close the form after it passes validation.

D'oh!!
Thanks.

 

[theDBguy]

You're welcome. Good luck with your project.

 

[ryetee]

You're welcome. Good luck with your project.

Actually I'm still missing something here.
When the button is clicked to close the form the beforeupdate hasn't taken place so at this stage I don't know if there are any errors as the variable won't have been set.
I told you I'm cracking up

 

[MajP]

Here is an example I did in a previous thread that can be called from multiple locations. In this case it provides a message for which fields are not filled in. You could expand the idea and check for a specific field within the loop and add more rigorous checks. It returns true or false if all checks are met.

https://www.access-programmers.co.uk/forums/showpost.php?p=1611095&postcount=22

 

[theDBguy]

Hi. Does your close button code look something similar to this?

If Me.Dirty Then Me.Dirty = False
If varOK2Close Then DoCmd.Close acForm, Me.Name

 

[ryetee]

Hi. Does your close button code look something similar to this?

If Me.Dirty Then Me.Dirty = False
If varOK2Close Then DoCmd.Close acForm, Me.Name

Looks like

If CountriesFormPass = "pass" Then
DoCmd.Close acForm, "Countries"
End If

 

[ryetee]

Here is an example I did in a previous thread that can be called from multiple locations. In this case it provides a message for which fields are not filled in. You could expand the idea and check for a specific field within the loop and add more rigorous checks. It returns true or false if all checks are met.

https://www.access-programmers.co.uk/forums/showpost.php?p=1611095&postcount=22

Thanks I'll take a look

 

[theDBguy]

Looks like

If CountriesFormPass = "pass" Then
DoCmd.Close acForm, "Countries"
End If

Try to force a save first to make sure the BeforeUpdate event fires before you check for the value in the variable.

 

[ryetee]

Try to force a save first to make sure the BeforeUpdate event fires before you check for the value in the variable.

OK looks like the If Me.Dirty Then Me.Dirty = False forces the beforeupdate
Thanks again

 

[theDBguy]

OK looks like the If Me.Dirty Then Me.Dirty = False forces the beforeupdate
Thanks again

That's correct. You're welcome. Glad to hear you got it sorted out. Good luck with your project.

 

[ryetee]

That's correct. You're welcome. Glad to hear you got it sorted out. Good luck with your project.

OK just when you think it's working it's not!

I have a catch 22 situation here.
I said at the beginning "This works if trying to move to another record using the navigation bars." which was true. When capturing the error i had Cancel = True in the code. This I believe prevented selection of the next record. However when testing the button that closes the form the cancel = true is giving me problems on the
If Me.Dirty Then
Me.Dirty = False
End If
code. I get a run time error 2101. The setting you entered isn't vali for this property. This appears on the line Me.Dirty = False. If I get rid of cancel = true it works but this screws up the navigation by not remaining on current record. I think the cancel = true is causing the me.dirty to be returned to TRUE which is potentially going to put this all in an infinite loop.

 

[theDBguy]

Hi. Are you able to post a demo?

 

[ryetee]

Hi. Are you able to post a demo?

OK I'll cut it down and send something

 

[ryetee]

Hi. Are you able to post a demo?

OK so this should open a tax category form. If you blank out the description, a field that is required, and then click the close button (door in top left hand of form) then you get an error and the form is not closed.
Doing the same thing and clicking the navigation buttons at the bottom of the form causes the same error but the next record (or prev record) is displayed and the original record will now have a blank description.
If you uncomment out the cancel = true then the navigation button works ok but the close throws up an error message

 

[theDBguy]

Hi. I think the main reason for this issue is because you're using a VBA variable but then use Embedded macros in your navigation buttons, which cannot see VBA variables. I changed the whole approach, so take a look and let me know what you think.

 

[ryetee]

Hi. I think the main reason for this issue is because you're using a VBA variable but then use Embedded macros in your navigation buttons, which cannot see VBA variables. I changed the whole approach, so take a look and let me know what you think.

Thanks for this. I didn't actually mean the navigation buttons at the top which I'm probably going to get rid of. I meant the ones at the bottom where it says 1 of 5 etc etc. However your changes work for those as well so appreciate it!

One thing though the Me.txtDescription.Undo doesn't appear to work as I thought it might. It's leaving the description blank rather than returning to previous value. This isn't important in this case but I'm introducing validation into all forms and it will for a lot of them

 

[theDBguy]

Thanks for this. I didn't actually mean the navigation buttons at the top which I'm probably going to get rid of. I meant the ones at the bottom where it says 1 of 5 etc etc. However your changes work for those as well so appreciate it!

One thing though the Me.txtDescription.Undo doesn't appear to work as I thought it might. It's leaving the description blank rather than returning to previous value. This isn't important in this case but I'm introducing validation into all forms and it will for a lot of them

Oh, sorry, I guess I misunderstood what you were saying. With regards to Me.txtDescription.Undo, that would probably only work if you were using the control's BeforeUpdate event rather than the form's.

 

[ryetee]

Oh, sorry, I guess I misunderstood what you were saying. With regards to Me.txtDescription.Undo, that would probably only work if you were using the control's BeforeUpdate event rather than the form's.

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