Help, Code not working to prevent saving incomplete records.

[Jramirez]

I have a situation where users add one field and then close the form leaving imcomplete records in the table. I tried similar coding in the Before_update and got a similar result. I may be way off on this but here is what I have, (at this point I get an error and the form closes anyway):

Private Sub Form_Close()
On Error GoTo ErrorHandler
' Stop User from leaving empty fields.
If Len(Me.Name & vbNullString) > 0 Then
' Go back to form to finish filling it out.
If MsgBox("If you close without saving, all data will be lost. Do you want to close anyway?", vbYesNo, "Data Missing...") = vbNo Then
Cancel = True
Me.SetFocus
' Clear Fields and close the form
Else
Cancel = False
Me.Undo
Resume ExitProcedure
End If
End If
ExitProcedure:
Exit Sub

ErrorHandler:
If Err.Number > 0 Then
MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
End If

Resume ExitProcedure
End Sub

Thanks for any help you can give.

 

[boblarson]

1. You can't use the Close event.

2. It is the Before Update Event you need.

3. Name is an Access Reserved Word and actually one of the worst to use as almost everything in Access has a .Name property. So you need to add square brackets around it. Me.[Name]

 

[Jramirez]

Still getting the "Runtime Error" Any ideas?

 

[boblarson]

Still getting the "Runtime Error" Any ideas?

What's the EXACT error message?

 

[Jramirez]

If "No" is selected MS Access comes back with "You can't save this record at this time. Microsoft Office Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database any way? Yes/No" If "No" is clicked here an "Action Failed" dialogue box comes up with Macro Name: "FormName/ Close : OnClick : Embedded Macro/ Condition: True/ Action Name: Close/ Arguements ,,Prompt/ Error number 2950. If "Yes" is selected a dialogue box comes up with "Error #20" in the title line/ a warning symbol and "Resume without error/ Ok. When "Ok" is clicked the whole thing closes. No records are saved as a result of either condition.

 

[boblarson]

1. Under ActionName: Close and the Arguments should not have PROMPT it should say NO. That prompt has nothing to do with saving records. It has to do with saving DESIGN CHANGES to the form.

2. So you seem to have a close button which is running the embedded macro, or the embedded macro is in the Close event of the form. This should not be the case. There should be no embedded macro in the close event of the form. If there is, delete it from that event.

3. And, last post the exact code you now have in the close button's event.

 

[Jramirez]

I have to admit I feel a little foolish, I do have a macro running the close door button. I also get the same errors when I click the Orange X. So I can't post any code. Only the code from the BeforeUpdate, which you've already seen, but I will post it here again:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
' Stop User from leaving empty fields.
If Len(Me.[Name] & vbNullString) > 0 Then
Cancel = True
' Go back to form to finish filling it out.
If MsgBox("If you close without saving, all data will be lost. Do you want to close anyway?", vbYesNo, "Data Missing...") = vbNo Then
Me.SetFocus
' Undo changes and close the form
Else

Me.Undo
Resume ExitProcedure
End If
End If
ExitProcedure:
Exit Sub

ErrorHandler:
If Err.Number <> 2100 Then
MsgBox Err.Description, vbExclamation, "Error #: " & Err.Number
End If

Resume ExitProcedure
End Sub

 

[boblarson]

Looks to me that you have it all there EXCEPT that you have one little tiny thing wrong. Change this line:

If Len(Me.[Name] & vbNullString) > 0 Then

to this

If Len(Me.[Name] & vbNullString) [B][COLOR=red]=[/COLOR][/B] 0 Then

 

[Jramirez]

First I changed the close code to DoCmd.Close. Both the "close door" button and the orange X completely close the form without asking for any information.

Second, I put in the = 0 and it seems to completely bypass the Form_BeforeUpdate procedure and close the form. If there is nothing in any of the fields it doesn't matter because nothing has been saved anyway. So, that part is fine. But when users enter 1, 2, 3 etc. fields and then close/saving with parts missing it causes a problem for our inventory reporting and calculations.

 

[boblarson]

First I changed the close code to DoCmd.Close. Both the "close door" button and the orange X completely close the form without asking for any information.

Second, I put in the = 0 and it seems to completely bypass the Form_BeforeUpdate procedure and close the form. If there is nothing in any of the fields it doesn't matter because nothing has been saved anyway. So, that part is fine. But when users enter 1, 2, 3 etc. fields and then close/saving with parts missing it causes a problem for our inventory reporting and calculations.

Well, you have to also check the rest of them in that form's Before Update event. It isn't just one control. You have to check them ALL. Just like this:

Dim blnError As Boolean
Dim strMessage
 
If Len(Me.ControlName & vbNullString) = 0 Then
   blnError = True
   strMessage = "Field1NameHereInQuotes"
End IF
 
If Len(Me.Control2Name & vbNullString) = 0 Then
   blnError = True
   strMessage = strMessage & "Field2NameHereInQuotes" & vbCrLf
End If
 
If Len(Me.Control3Name & vbNullString) = 0 Then
   blnError = True
   strMessage = strMessage & "Field3NameHereInQuotes" & vbCrLf
End If
 
 
If Len(Me.Control4Name & vbNullString) = 0 Then
   blnError = True
   strMessage = strMessage & "Field4NameHereInQuotes"
End If
 
If blnError Then
   Cancel = True
   Msgbox "Before you can go on you need to fill out: " & vbCrLf & strMessage
End If

 

[Jramirez]

Thank you, I had a feeling that might be part of the problem. I will try it tomorrow when I get in and post my results. Thank you.