VBA Code to verify data

[DJRhino]

I have 2 fields I need to check data for:
Field 1) is yes/no box called "Closed"
Field 2) is a combo box named "QualityClosedBy"

I need code that says and executes on closing the form:
if Closed = yes then "QualityClosedby" must not be empty and if it is empty to set focus to that field to be filled in.
This way it will force a record into a log that is controlled by code already in place. I need this as someone has been closing things out without it triggering the code for the log.

[Seth Schrock]

You actually almost have it. In the form's On_Unload event, put in the following code:

If Me.Closed = True Then
    If Me.QualityClosedBy & "" = "" Then
        Cancel = True
        MsgBox "You must enter a value in QualityClosedBy."
        Me.QualityClosedBy.SetFocus
    End If
End If

[NeoPa]

I would suggest that the proper place to do that, and what it's designed for, is the table properties.

When in Design View of a table hit Alt-Enter (or press the properties button) and look at the Validation Rule.

[DJRhino]

This worked Good. The only issue was when I compiled it, I needed to remove the extra end if.

[DJRhino]

NeoPa

Your right that the Validation rule would be good, but I needed it on the form as someone was closing issues out with out entering data to back it up, so I put in a log file to see who was doing it and it happened again but there was nothing in the log file. So this should hopefully stop this from happening again.

[NeoPa]

It depends on what you want to do. If you want to name & shame someone then you'll need to log it with some code.

OTOH, designing the table so as to disallow it in the first place ensures it never happens, either directly or via a form.

Remember, as the designer, most of the shame would come back on you anyway as it's your responsibility to design it to work correctly. That's part of the job. I'd give that some thought.

[DJRhino]

Seth,

The code works good, but when:
"Closed" = True and "QualityClosedBy" = True
it still pops up the message. I dont want the message when both are true.

Neo,

I agree with what your saying, but I only require the Closed by part when you close it "Officially" and someone has been going in and just clicking on close. I'm just trying to prevent that.

[Seth Schrock]

You must have entered something incorrectly, because my code doesn't produce the error message unless the combo box QualityClosedBy doesn't contain anything.

[DJRhino]

Here is what I have

Private Sub Form_Unload(CANCEL As Integer)
'Help stop unauthorized closure.
If Me.Closed = True Then
    CANCEL = True
    MsgBox "You must enter your name in Quality Closed By, If your name is not in the list, then leave this form as you are not authorized to make changes. To leave uncheck close and click the close button. Goto the read only version."
    Me.QualityClosedBy.SetFocus
    End If
End Sub

[Seth Schrock]

That would explain your comment about the extra End If. You missed line 2 completely. You need to add it and the End If to go with it.

[DJRhino]

Good catch, As soon as I put that in all problems gone.

Thanks for all your help.

[Seth Schrock]

No problem. Just as a side note, it would probably be a good idea to also put in the field validation in your table as well to block the attempts that might go around your form. As NeoPa says, correct data is always better than catching fixing it later. I personally like doing my validation in code because it allows me to make nicer error messages, but there are ways around that, so doing both gives you the best of both worlds. I have never regretted following NeoPa's advice. A quick Google search for Conditional Field Validation should help you create the validation rule that will only allow the proper combination of values.

[PhilOfWalton]

Sorry to come in so late in the discussion, but I would have thought (please correct me if I'm wrong) that you equally have an error condition if Closed = No and there is a valid QualityClosedBy name.
I believe one of the best ways of handling this situation is to remove the Close button on the form and instead add a command button to close the form. That way, on pressing the Close Command button, you can run whatever checks you like before actually closing the form.
On all of my database, I go a bit further. Once the main menu is loaded, it is impossible to close the database on the normal Access close button. It has to be closed using the Close Command button on the main menu.
This allows various "housekeeping" activities like checking no other forms or reports are open and checking when the last backup was done before allowing the database to close.

Phil

[DJRhino]

I use the closed - Yes/No box for sorting.

Also the other reason I did not do the validation in the table, is we use a form to create a data entry and then once that issue is dealt with, they use another form for closing the issue out.