Need to close form, but unload event causing problems!

[ksgirl]

This is in reference to some of my previous post, but different, so here I am posting something new! I have a form set up so that the user has to fill in fields before they can exit the form. I have this in the unload event and it worked great until something was brought to my attention. "What if the user accidently opens the form and doesn't want to fill anything in?".... So I decided to put a cancel button on the form:

Private Sub undo_record_Click()

Dim strMsg As String, strTitle As String
Dim intResponse As Integer

strMsg = "You Chose to Cancel This Entry." & vbNewLine & vbNewLine & _
" Are You Sure?"
strTitle = "Are You Sure?"
intResponse = MsgBox(strMsg, vbQuestion + vbYesNo, strTitle)

If intResponse = vbYes Then
Me.Undo
DoCmd.Close
End If
End Sub

Then if the user clicked on the "undo_record" button, the code from the unload event would prompt the user to fill in the blanks that were missing and wouldn't let you exit out of the form. And So then I manipulated the Unload event and added this at the beginning of my IF statements and changed the other IF's to ElseIF's:

If Me.undo_record.Enabled = True Then
Me.Undo
Exit Sub

I thought this did the trick! If you click the "undo_record" then it closes out of the form nicely, not saving any info. But if you go and start to fill in the form and think you have it filled and click the "exit" button, and one of your fields is blank, then the form closes without prompting the user to fill them in and saves the record. I'm confused on where to go now. I know there is something wrong with the Unload event, but have no ideas. Any suggestions?

 

[AlanS]

Try using the BeforeUpdate event to verify that the required fields are filled in, instead of the Unload event. That way, if the user opens the form and changes his/her mind before entering or changing any controls, when they close the form the BeforeUpdate event will not fire. If they do enter or change any controls, the BeforeUpdate event will fire and perform the verification you specify. If anything does not pass verification, simply set the Cancel argument to True to abort the update, and display an appropriate error message; you may also want to set the focus to the control where the problem was found.

 

[ksgirl]

I've had this code on the Before-Update previously (look at my previous posts around 8-1-02), which it works, but the form closes without the user being able to go back and fill in the field. Someone suggested to put in the Unload event which solved my problem, until now. With the "undo_record" button, the code I have close the form, which then the Unload event is triggered.... and there is where I'm lost. That is why I added the (If me.undo_record.enabled = true) statement, which I thought would do the trick. My next question is, if I use the Before-Update, how can I keep the form open if one of the fields is Null? Anyone have any other ideas?

 

[AlanS]

Assuming you are cancelling the BeforeUpdate event if required fields aren't filled in, when the user attempts to close the form (and the current record has missing field values), they will get a dialog box saying "You can't save the record at this time." and asking them whether they want to close anyway. That way, the user is presented with the only choices that make any sense at that point: click "Yes" to undo the record changes and close the form, or click "No" to abort the close and go back to correct the missing values. In no case will they be able to close the form while saving a new or changed record that does not satisfy the edit checks in the BeforeUpdate event procedure.

If this scheme doesn't work for you, then you might try putting your edit checks in a separate function, and call that function from both the BeforeUpdate and the Unload event procedures, with both events being cancelled if the function does not return the desired value.

 

[ksgirl]

This is the code I have for the missing field verification:

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

I have put this in both the On Unload, and On Update, and with both having the same code. I know the code works in certain cases for me. This is my problem:

On Update: Did not work properly because the user could not go back and update the field that null, even though the message box popped up telling the user which one to fill in, because the form would close. So I tried:

On Unload: worked great.... until I added the "undo_record" button. Now with this "undo_record" button, it worked fine (with the addition of that code I mentioned in the first post to the Unload event), but the "exit" button does not. It would just close out the form not prompting the user if there was a blank field.

So now I'm thinking neither of these events will work. This is where my major problem arises: in the "undo_record" button!!! Since I want to undo the record and close the form, the Unload event is activated, so if there is anyway I can bypass the rest of the code in the Unload event if the user has clicked "yes" on the "undo_record" button then that would solve my problem. Is there anyway to reference to that button in the Unload event if the "undo_record" button was selected? This way if "yes" was selected it would close the form with out going through the field verification code, but if the button is not selected the code still runs through the field verification code on the closing of the form. Am I making any sense?

 

[AlanS]

I see a couple of problems with this code. First, you are using a series of If...Then/End If blocks, rather than a single If...Then/ElseIf/ElseIf/End If block. If any condition is true, there's no point in checking the subsequent ones.

Also, the help screens (at least in Access 97) say that you must use the Msgbox function as part of an expression if you are specifying more than the first argument. I know that you can get away with specifying multiple arguments even when it is not used in an expression, but I wonder if doing so could somehow be causing your problem.

 

[ksgirl]

Opps, I copied the wrong code for you. I did have the statements set up as If...Then/ElseIf....End IF, I've been playing around with it so much that it is hard to post something with the correct info. Anyway, here is what I did have on the Unload event:

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


If Me.undo_record.Enabled = True Then
Me.Undo
Exit Sub

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

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

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

The part in red is where I was trying to "bypass" the verification code. Anyway, I'm not quite sure if I understand what this means. "the help screens (at least in Access 97) say that you must use the Msgbox function as part of an expression if you are specifying more than the first argument"....could you explain?

 

[AlanS]

The simple way to use the MsgBox function is to call it as you would call a Sub, i.e., simply specify it on a line by itself, along with any arguments, e.g.:

MsgBox "Some message."

However, if you want to specify more than the first argument (the message to be displayed in the message box), you must use the syntax applicable to functions in general, i.e., you must use the function as part of an expression, e.g.:

If (MsgBox("Are you sure?", vbYesNo, "Confirmation") = vbYes) Then ...

In this example, everything within the outer parentheses constitutes an expression, which will evaluate to either True or False.

To put it another way, the MsgBox function must be used syntactically just as any other function, EXCEPT when it is called with only the first argument, in which case it may also be used syntactically as if it were a Sub. With any other function, you must always use the function as part of an expression - for example, the following line would not compile:

CSng(X)

whereas this line would:

Y = CSng(X)

because only in the second line is the CSng function used as part of an expression. You may want to review the help screen for the MsgBox function. You may also want to put a break point in your form's load event, so you can step through it and see what is actually happening, and in what order.

 

[ksgirl]

Thank you for your reply. But it still doesn't help me any. I understand that you think the code should be put in the BeforeUpdate event, and yes that would solve my "bypass" problem, but I'm still back to some kind of error.... the verification code works but doesn't leave the form open for the user to fill in those fields. If I put a message box of some sort where they choose to either continue without saving or to go back and make the changes, how do you suggest I put this in on the BeforeUpdate event with the other code that I have? There has to be a way to be able to do this!!!!! HELP!

 

[ksgirl]

I've now tried this:

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

If Me.Dirty Then

If IsNull(Me![Reason for Modification]) Then
......Blah....Blah
End If
.....Blah.....Blah

End If
End Sub

I've added the part in red and this now allows the user to click on the "undo_record" button and exit out of the form without saving, but if you click the "exit" button the form just closes, not prompting the user if there are null fields....so my next question is why doesn't this work?

 

[ksgirl]

ANYONE?