Validate (1 Viewer)

[mike60smart]

Hi Everyone
I have a Continuous Form where I need to validate just 1 Control named "AchievementLevel"
I have tried adding the following Code to the On Click Event for Closing the Form.
When I try to run the Code Access Locks up?

Is there a better way to achieve this?
Any help appreciated.

Private Sub cmdClose_Click()

10        On Error GoTo cmdClose_Click_Error

          Dim rs As DAO.Recordset

20        Set rs = Me.RecordsetClone
30        rs.MoveFirst

40        Do While Not rs.EOF
50            If IsNull(rs!AchievementLevel.Value) Then
60                MsgBox ("AchievementLevel cannot be blank")
70                Me.Bookmark = rs.Bookmark
80                Me.AchievementLevel.SetFocus
90                Exit Sub
100           End If
110       Loop

120       Set rs = Nothing

140       DoCmd.Close
          
150       On Error GoTo 0
160       Exit Sub

cmdClose_Click_Error:

170       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdClose_Click, line " & Erl & "."

End Sub

 

[MajP]

Validation gets done in the form before update event. Period.
Been covered extensively in multiple posts on this thread. No one explains this in more detail than @Pat Hartman

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Your approach seems illogical to me. Why would you wait until multiple invalid records are added then go back and verify by checking all records? Seems you want to validate before a record is created.
Maybe this makes sense if the data is imported from an external source.

 

[mike60smart]

Validation gets done in the form before update event. Period.
Been covered extensively in multiple posts on this thread. No one explains this in more detail than @Pat Hartman

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Your approach seems illogical to me. Why would you wait until multiple invalid records are added then go back and verify by checking all records? Seems you want to validate before a record is created.
Maybe this makes sense if the data is imported from an external source.

Hi MajP

The Form is a PopUp, Continuous Form View which when opened displays 4 specific records which are created using a Macro that runs an append query.
The user then only has to fill in the Achievment Level for Each of the 4 Records.

I have tried using the following without success also.
Any help appreciated.

Private Sub Form_BeforeUpdate(Cancel As Integer)

10        On Error GoTo Form_BeforeUpdate_Error
      Dim ctl As Object

20    For Each ctl In Me.Controls

30        If ctl.Tag = "Reqd" Then

40            If Nz(ctl.Value, "") = "" Then

50                Cancel = True

60                MsgBox "All Achievement Levels need to be completed.", vbCritical, "Incomplete Entry"

70                Exit Sub

80            End If

90       End If

100   Next ctl


          
110       On Error GoTo 0
120       Exit Sub

Form_BeforeUpdate_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate, line " & Erl & "."

End Sub

 

[MajP]

Do While Not rs.EOF
50            If IsNull(rs!AchievementLevel.Value) Then
60                MsgBox ("AchievementLevel cannot be blank")
70                Me.Bookmark = rs.Bookmark
80                Me.AchievementLevel.SetFocus
90                Exit Sub
100           End If
110       Loop

There is no rs.movenext so this code runs forever if the first record is not null.

 

[mike60smart]

Hi MajP

How would I modify the section you highlighted so that if any records left with the value "0.0"
would be highlighted as needing a value other than "0.0"?

Do While Not rs.EOF
50            If IsNull(rs!AchievementLevel.Value) Then
60                MsgBox ("AchievementLevel cannot be blank")
70                Me.Bookmark = rs.Bookmark
80                Me.AchievementLevel.SetFocus
90                Exit Sub
100           End If
110       Loop

 

[mike60smart]

Do While Not rs.EOF
50            If IsNull(rs!AchievementLevel.Value) Then
60                MsgBox ("AchievementLevel cannot be blank")
70                Me.Bookmark = rs.Bookmark
80                Me.AchievementLevel.SetFocus
90                Exit Sub
100           End If
110       Loop

There is no rs.movenext so this code runs forever if the first record is not null.

Hi MajP
Until I find a better solution I am currently using the following.

When the Form opens the Focus is set to the Control "AchievmentLevel"
Then on the LostFocus Event I have the Code below.
This does not allow the user to close the Form until all Records have been completed.
Thanks for the help.

Private Sub AchievementLevel_LostFocus()

10        On Error GoTo AchievementLevel_LostFocus_Error
       Dim ctl As Object

20    For Each ctl In Me.Controls

30        If ctl.Tag = "Reqd" Then

40            If Nz(ctl.Value, "") = "" Then

50                MsgBox "Not all required controls are filled in.", vbCritical, "Incomplete Entry"

60                Exit Sub

70            End If

80       End If

90    Next ctl


          
100       On Error GoTo 0
110       Exit Sub

AchievementLevel_LostFocus_Error:

120       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure AchievementLevel_LostFocus, line " & Erl & "."

End Sub

 

[Pat Hartman]

The user then only has to fill in the Achievment Level for Each of the 4 Records.

It is really bad practice to save records you KNOW are invalid. Add the records one at a time, all the user needs to do is to is to pick a value for Achievement, your code can populate the rest of the fields since you must have the values if you are appending the rows using a macro.

Then, if the business rule is that all four must be added, Check that in the click event and refuse to close the form. However, the user can always overcome this by using Task Manager to close Access. That leaves you between a rock and a hard place. Either you allow bad data to be added immediately or risk having missing records. I solve the missing records problem by adding a "complete" flag to the parent record. In the AfterUpdate event of each child record, I count the number of child records. If they are all present, I update the "complete" flag on the parent and close the popup. Then queries always have to consider the "complete" flag when deciding whether or not to use the record. You can also run a query on start up looking for partial transactions if this could be a problem.

 

[MajP]

See if this demo works for you.
You cannot cancel the Close event. You can try this in the unload event because it has a cancel. But this can cause big problems where you cannot even get into design view. I would may a close button and no way to X out.

 

[mike60smart]

Hi MajP
That is very slick will study and incorporate and let you know how I get on
Many thanks

 

[mike60smart]

See if this demo works for you.
You cannot cancel the Close event. You can try this in the unload event because it has a cancel. But this can cause big problems where you cannot even get into design view. I would may a close button and no way to X out.

Hi MajP
Well your method is ideal. Just implemented and works a charm.
Many thanks yet again

 

[Pat Hartman]

The code still leaves you with bad data. You just get to confirm you want the bad data.

 

[mike60smart]

The code still leaves you with bad data. You just get to confirm you want the bad data.

Hi Pat

I am not understanding the bad data comment as when this particular form opens it
displays 4 specific records that need data to be entered into 1 Control only.

Using the code supplied by MajP if a record does not have a value entered by the User for the "AchievementLevel"
then the msg prompt displays.

If all records are completed correctly then the Form Closes as expected.

Where is the resulting bad data?

 

[MajP]

FYI. I left the prompt to allow you to close with bad data for testing purposes. You probably want to take that out.

 

[Pat Hartman]

I am not understanding the bad data comment as when this particular form opens it
displays 4 specific records that need data to be entered into 1 Control only.

If AchievementLevel is required and you are saving records with no value, then the data is bad. "Only 1 bad field" is still bad. Using the "complete" flag at least warns about the problem.

Remember, no matter whether you attempt to prevent the form from closing or not, YOU don't have the ultimate say and that is why I use a "complete" flag in this instance. It allows you to enter bad or incomplete data in this awkward circumstance but not make bad business decisions because of the bad data.

 

[mike60smart]

FYI. I left the prompt to allow you to close with bad data for testing purposes. You probably want to take that out.

Hi MajP

I tried the same code on a different Form and I get the following error?

 

[mike60smart]

FYI. I left the prompt to allow you to close with bad data for testing purposes. You probably want to take that out.

Hi MajP

How would I modify the following lines of Code so that if the user does not update any records
it just displays the Msg Prompt and returns the user to the Form and not Close?

       If IsNull(rs!AchievementLevel) Or rs!AchievementLevel = 0 Then
                If MsgBox("AchievementLevel cannot be blank or 0. Complete all Achievement Levels", vbYesNo, "Exit") = vbYes Then
                  Validate = True
                  Exit Function
                End If

 

[MajP]

 tried the same code on a different Form and I get the following error?

Need to check the recordset before looping
If not rs.Eof then ...

How would I modify the following lines of Code so that if the user does not update any records
it just displays the Msg Prompt and returns the user to the Form and not Close?

Put it back to the way you had it. Add the msgbox without the return value and delete what is inside the if...then

 

[mike60smart]

 tried the same code on a different Form and I get the following error?

Need to check the recordset before looping
If not rs.Eof then ...


Put it back to the way you had it. Add the msgbox without the return value and delete what is inside the if...then

Hi MajP

My apologies but my VB Skills are not very good.

When you say "Put it back to the way you had it." ??
I have the lines exactly as posted from your original example?

And have no idea how to achieve the following:-
"Add the msgbox without the return value and delete what is inside the if...then"

 

[MajP]

The way you had it was just a straight message box. I added the extra if check.

'If MsgBox("AchievementLevel cannot be blank or 0. Do you want to exit anyways?", vbYesNo, "Exit") = vbYes Then
      '  Validate = True
      '  Exit Function
 'End If
 
 MsgBox "AchievementLevel cannot be blank or 0.", vbInformation, "Validate Data."

Remove what I had and put the message box back the way you had it.

Also see the check to ensure you have records in the recordset before looping.

 

[mike60smart]

Hi MajP
Well I am completely lost now.
Many thanks for the help but I will revert to the version I had that was working.
Thanks again