Form: before update HELP

[scukaf]

Hi guys, i have a little problem regarding the code that i wish to use on my form:
[FONT=&quot]
[/FONT]
[FONT=&quot]Private Sub Form_BeforeUpdate(Cancel As Integer)[/FONT]
[FONT=&quot]If Me.Status = "Installed" Then[/FONT]
[FONT=&quot] If IsNull(Me.[Install Date]) Then[/FONT]
[FONT=&quot] MsgBox "If status is Installed, you have to enter Date value", vbOKOnly[/FONT]
[FONT=&quot] Cancel = True[/FONT]
[FONT=&quot] Me.[Install Date].SetFocus[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot]End If[/FONT]
[FONT=&quot]End Sub[/FONT]

If I enter the data, code works well. Also if I don't enter the data upon clicking "save & exit" button on my form, message box appears and that's also fine, but! After message box appears and clicking OK onto it, also an error 2169 appears (You can't save the record at this time...) which I wouldn't like to be seen.

Can you make any suggestions what to do?

Thanks in advance

 

[missinglinq]

You validation is correct, and would work just fine if you were simply trying to Cancel Saving the Record until the problem was corrected. With this code in place, for instance, if you tried moving to another Record/new Record, it would act as you want it to. But you're also trying to Close the Form at the same time, which is what prompts the 'You can't Save the Record..." message. With the above code in the Form_BeforeUpdate event, you'd have the same problem if you merely clicked on the Close Button (big X) to Close the Form.

To do this and Close the Form, when the user is ready, you have to do the validation in your custom Close & SaveButton, and not attempt closing the Form until the missing date is entered.

You can do this with code like this:

Private Sub CloseAndSave_Click()

 If Me.Status = "Installed" Then
    If IsNull(Me.[Install Date]) Then
       MsgBox "If status is Installed, you have to enter Date value", vbOKOnly
       Me.[Install Date].SetFocus
       Exit Sub
    End If
  End If
    
DoCmd.RunCommand acCmdClose

End Sub

But this comes with two caveats!

• You must insure that the user can only Close the Form using your Save & Close Button (clicking on the big X will bring up the unwanted message)
  
• You must prevent the user from navigating to another Record, instead of hitting your Save & Close Button.

The first can be done by setting the Form's Close Button Property to No. (In Form Design View, go to Properties - Format - Close Button.)

The second can be doe by doing the same for the Form's Navigation Buttons Property.

If you want your user to be able to enter multiple Records per session, you'll need to use your original code, from Post #1, in the Form_BeforeUpdate event.

Linq ;0)>

 

[sneuberg]

The first can be done by setting the Form's Close Button Property to No. (In Form Design View, go to Properties - Format - Close Button.)

This gets rid of the close button on the form but unless you hid the Access application the user can still click the close button on it and no validation is performed. You can do the validation in the form unload event as follows and keep the close buttons.

Private Sub Form_Unload(Cancel As Integer)

If Me.Status = "Installed" Then
    If IsNull(Me.[Install Date]) Then
       MsgBox "If status is Installed, you have to enter Date value", vbOKOnly
       Me.[Install Date].SetFocus
       Cancel = True
    End If
  End If

End Sub

But if you keep the your own close button with this it will produce a cancellation error which you can trap as shown below.

Private Sub CloseAndSave_Click()
On Error GoTo CloseAndSave_Click_Err

DoCmd.Close


CloseAndSave_Click_Exit:
    Exit Sub

CloseAndSave_Click_Err:
 [COLOR="Blue"]   If Err.Number <> 2501 Then
          MsgBox Error$
    End If[/COLOR]
    Resume CloseAndSave_Click_Exit

End Sub

Putting the validation in the form unload creates another irritant. When you switch the form from form view to design view this event fires forcing you to comply with your constraints. To avoid this just remember to open the form in design view from the nav pane.

If you want to leave the navigation in I suspected you could add the validation to the form current event although I've haven't test this. But all of these workarounds seem a bit nutty and I think it a nasty flaw in Access that we have no way of handling this "You can't save the record at this time.." error.

 

[scukaf]

Guys, this works great!

Thanks for helping me, I can only set this thread as Solved