Force input into form's fields

[ksgirl]

I thought I had this one solved but I need some advice! I have the following code placed in on my form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String, strTitle As String
strMsg = "Please enter a "
strTitle = " Missing Entry"

If IsNull(Me![Reason for Modification]) Then
MsgBox strMsg & "Reason for Modification", vbInformation + vbOKOnly, strTitle
Me![Reason for Modification].SetFocus
Cancel = True
End If

If IsNull(Me![txtEquipmentID]) Then
MsgBox strMsg & "Equipment ID", vbInformation + vbOKOnly, strTitle
Me![txtEquipmentID].SetFocus
Cancel = True
End If

If IsNull(Me![Position]) Then
MsgBox strMsg & "Position", vbInformation + vbOKOnly, strTitle
Me![Position].SetFocus
Cancel = True
End If
End Sub

I am trying to force the user to enter values into those three fields. My problem is that if a field is left blank and you press the exit button, the message box pops up perfectly directing the user that they have left it blank but the form closes, not allowing the user to go back and correct the empty field. I don't know if I'm missing something, or need something else on my code for the exit button (which I made with the wizard).....
Any ideas?

 

[[nateobot]]

I have something similar to that I needed to do. I have a change password form and if they have to change password (various reasons) then this form pops up. I have a global variable that is set to 1 in this instance. If this variable is 1 and the form is attempted to be closed then it prompts them to change the password and will not let them leave the form until that is done.

Here is the code that keeps them from closing.

Private Sub Form_Unload(Cancel As Integer)
    If gintMustChangePass = 1 Then
        MsgBox "You must change your password now.", vbCritical, "Change Password Failed"
        Cancel = True
    End If
End Sub

 

[ksgirl]

I know my code for the empty field works fine......except for the fact that it doesn't cancel the form 'closing'. Do I need to put this code in an Unload or OnExit event?....so that the user can go back and fill in the blank field before the form closes? Is there something I need to have in my OnClick of the exit button, other than what is generated when I do it through the wizard? Any other ideas?

 

[[nateobot]]

You could set the variable when the condition is true. Set it to false if the condition is false.

As you can see on the code below it is for the Unload event "Private Sub Form_Unload".

example:

If IsNull(Me![Reason for Modification]) Then 
   iLetFormClose = 0
   Me![Reason for Modification].SetFocus 
Else
   iLetFormClose = 1
End If

Hope that explains a little better

 

[ksgirl]

Ok I have this problem solved.... Nateobot Thanks!!! But, then I went to one of other forms, where I have basically the same code (with different field names) in the BeforeUpate event, and I can't seem to get it to do what I want. I want the user to have to fill in these fields too, before they can open up the next form. There is one button on this form that opens up another form, so I'm stumped to as why the BeforeUpdate event won't work!!! Does anyone have a suggestions for this?

 

[ksgirl]

Anyone? I just need help with the last post of mine!!
I'm up for any suggestions!

 

[Jeff Bailey]

I think maybe you are confusing two things here:

You are Canceling the Update event, so the data is not changed, but you are doing nothing about the Close event. That's why the form closes. You need to put code in a 'cancelable' event concerning form closure ...

 

[ksgirl]

Thanks for your reply Jeff, but I don't think you caught onto my question. I figured out how to tackle my first post, about canceling the closure of the form if a field is blank. Now I am trying to do a similar thing to another form, where I want the user to have to fill in certain fields before they can open up another form (with a button, where the first form stays open until the third form or so closes down all of the forms). I thought the BeforeUpdate would work in this case, but when you click the button, it opens up the next form not prompting the user if they have left a field blank! Does this make sense? I would have posted a new question regarding this one, but it was too similar! Any ideas?

 

[Jeff Bailey]

In the On Click event start off by checking the relevant field(s)

If isnull(forms!MyFormName!MyFieldName) then
msgbox "blah blah blah"
exit sub
end if

 

[Jeff Bailey]

In the On Click event for the button start by checking the relevant field(s) on the original form ...

If IsNull(Forms!MyFormName!MyControlName) then
msgbox "Blah blah blah"

 

[Jeff Bailey]

Sorry for the duplication - trouble with my keyboard

 

[ksgirl]

Ok, I've placed this in the OnClick of the first form (to open the second form), and I now have the Msgbox pop up and prompt the user that a field is blank but when you click ok on the Msgbox it still opens up the second form and not allowing the user to correct the mistake. How do I cancel the Open event of the form after all the IF statements to check the fields have been checked and one is null?

 

[Jeff Bailey]

If you have a button on the first form that opens the second form then the 'exit sub' in the code I posted earlier stops the code from going any further - it will never get to the part that opens the second form so there will be nothing to cancel.

 

[ksgirl]

But it doesn't!!!! I can't get this to work!!!!!!!!!!!!!!!!

Ok, here is what I have....

Private Sub work_orders_button_Click()
On Error GoTo Err_work_orders_button_Click

Dim strMsg As String, strTitle As String, stDocName As String

strMsg = "Please enter a "
strTitle = " Missing Entry"
stDocName = "Enter a New Work Order Form"


If IsNull(Me![Clearance Description]) Then
MsgBox strMsg & "Clearance Description", vbInformation + vbOKOnly, strTitle
Me![Clearance Description].SetFocus
Cancel = True
End If

If IsNull(Me![Employee ID]) Then
MsgBox strMsg & "Employee ID", vbInformation + vbOKOnly, strTitle
Me![Employee ID].SetFocus
Cancel = True
End If

If IsNull(Me![Status]) Then
MsgBox strMsg & "Status", vbInformation + vbOKOnly, strTitle
Me![Status].SetFocus
Cancel = True
End If

If IsNull(Me![Equipment ID]) Then
MsgBox strMsg & "Equipment ID", vbInformation + vbOKOnly, strTitle
Me![Equipment ID].SetFocus
Cancel = True
End If

DoCmd.OpenForm stDocName

Exit_work_orders_button_Click:
Exit Sub

Err_work_orders_button_Click:
MsgBox Err.Description
Resume Exit_work_orders_button_Click

End Sub

I know that the if there is an error than the code should cancel and the form shouldn't open, but it still does!!! The msgbox pops up indicating which ones are blank, but you click ok and the second form still pops up!!! HELP!!!!!!!!!!!!!

 

[Rich]

Use this in conjunction with the tag property of each control you want to check
Public Function fnValidateForm(frmA As Form) As Boolean
Dim ctl As Control
Dim Msg, Style, Title, Response, MyString
fnValidateForm = True
For Each ctl In frmA.Controls
'value in the control is required
If InStr(1, ctl.Tag, "Required") > 0 Then
' no value entered or value is null
' or zero for numeric fields
If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then
ctl.SetFocus
MsgBox "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not! "
fnValidateForm = False

Exit For
End If

If InStr(1, ctl.Tag, "NumberRequired") > 0 Then
If ctl.Value = 0 Then
ctl.SetFocus
MsgBox "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not! "
fnValidateForm = False

Exit For
End If
End If
End If

Next
End Function

 

[ksgirl]

The problem with your (sorry, Rich) code, is that on my form I only want the certain fields specified as above to make the user input data into them. I have several other fields on this form that don't need to have info in them for the user to progress to the next form. Therefore if I only have one statement that says "You have not entered all the required fields return to the record and correct this! The record will not be saved if you do not!".....the user won't know what fields to go back to fill in info. I really like how I have the If statements are set up, and they work great for my original problem.....that is why I thought they would work for this new problem!!!! I'll keep this idea on the back burner, if all else fails!! Any other ideas???

 

[Rich]

If you try the function you'll see that it sets focus to the relevant control, you need only attach the Tag property to the fields you wish to verify, you can change the message to suit. It's far more efficient than the multiple If's you have.
On the tag property line for the controls you wish to validate just type
AlphaRequired Or NumberRequired
HTH

 

[ksgirl]

Whew! I went back to my original code.....and the problem arose in the fact that in the OnClick event to open up the second form, if an error occured (in the if statements) it wouldn't recognize that I needed the Do.Cmd Open Form to cancel too! I put this statement in after the If statements:

If Cancel = True then
GoTo Err_work_orders_button_Click
End If

And it solved my problem! I thought I would post an answer for everyone's curiosity! Thanks for everyones help!!!

 

[Jeff Bailey]

Your code ...

Private Sub work_orders_button_Click()
On Error GoTo Err_work_orders_button_Click

Dim strMsg As String, strTitle As String, stDocName As String

strMsg = "Please enter a "
strTitle = " Missing Entry"
stDocName = "Enter a New Work Order Form"


If IsNull(Me![Clearance Description]) Then
MsgBox strMsg & "Clearance Description", vbInformation + vbOKOnly, strTitle
Me![Clearance Description].SetFocus
Cancel = True
End If

If IsNull(Me![Employee ID]) Then
MsgBox strMsg & "Employee ID", vbInformation + vbOKOnly, strTitle
Me![Employee ID].SetFocus
Cancel = True
End If

If IsNull(Me![Status]) Then
MsgBox strMsg & "Status", vbInformation + vbOKOnly, strTitle
Me![Status].SetFocus
Cancel = True
End If

If IsNull(Me![Equipment ID]) Then
MsgBox strMsg & "Equipment ID", vbInformation + vbOKOnly, strTitle
Me![Equipment ID].SetFocus
Cancel = True
End If

DoCmd.OpenForm stDocName

Exit_work_orders_button_Click:
Exit Sub

Err_work_orders_button_Click:
MsgBox Err.Description
Resume Exit_work_orders_button_Click

End Sub

Your problem is the Cancel = True lines. Replace each with Exit Sub and it will work.

You cannot 'Cancel' a button click event so Cancel = True has no effect and the code just continues down until the Openform line is executed.

HTH