Prevent form closing when not complete 3

[alr0]

I have a data entry form (Access02) with five pieces of data and they all need to be required. They cannot be required at the table level because other entries require a differnt set of values. I wrote the following code:

If Len([LaborDate]) > 0 And [JobID]) <> 0 And Len([EmployeeID]) > 0 And Len([ProdID]) > 0 And Len([Quantity]) > 0 Then
GoTo GoOn
Else
DoCmd.CancelEvent
MsgBox "Please complete all fields"
DoCmd.CancelEvent
End If

GoOn:

This works fine if the user tries to save the current record but if the user closes the form, the message box comes up but it still closes the form after clicking OK.

I thought the CancelEvent would cancel the close but it does not. The second CancelEvent was added because one did not work.

Any thoughts on how this should be done?

Thanks,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[PHV]

Put your validation code in the BeforeUpdate event procedure of the form and play with the Cancel parameter.
At worst the user looses the last data entry, at least no invalid data is created into the DB.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya alr0 . . .

In parallel with [blue]PHV[/blue] and adding a little code restructuring, the event should should look something like this (stay away from those GoTo statements):

[blue]Private Sub Form_BeforeUpdate([purple][b]Cancel[/b][/purple] As Integer)
   
   If Trim([LaborDate] & "") = "" Or Trim([EmployeeID] & "") = "" Or _
      Trim([ProdID] & "") = "" Or Trim([Quantity] & "") = "" Or _
      JobID = 0 Then
      
      MsgBox "Please complete all fields"
      [purple][b]Cancel[/b][/purple] = True
   End If

End Sub[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

How are ya alr0 . . .

In parallel with [blue]PHV[/blue] and adding a little code restructuring, the event should should look something like below (stay away from those GoTo statements):

[blue]Private Sub Form_BeforeUpdate([purple][b]Cancel[/b][/purple] As Integer)
   
   If Trim([LaborDate] & "") = "" Or Trim([EmployeeID] & "") = "" Or _
      Trim([ProdID] & "") = "" Or Trim([Quantity] & "") = "" Or _
      JobID = 0 Then
      
      MsgBox "Please complete all fields"
      [purple][b]Cancel[/b][/purple] = True
   End If

End Sub[/blue]

See Ya! . . . . . .

 

[MajP]

Or you can use this logic. Here is a form that there is no way of closing, without clicking the close button. You can not x out of the database or form. This is one way to ensure that no one closes out the database without going through the form.

Public blnOKtoClose As Boolean
Private Sub CmdClose_Click()
  blnOKtoClose = True
  DoCmd.Close
End Sub


Private Sub Form_Unload(Cancel As Integer)
  If Not blnOKtoClose Then
    DoCmd.CancelEvent
  End If
End Sub

basically you can set the "blnOKtoClose = True" in your code.

 

[alr0]

Hi All,

Thanks for your responses!

PHV-This is the point at which I arrived to prompt the question. I guess it is acceptable but hardly a polished app if the partial entry is lost. Is there really no way to do this?

TheAceMan1-I remember learning that GoTo should be avoided but I never understood why. What is so bad about it when something else is facilitated by using a GoTo?

MajP-If there is a way to do this, the unload event is likely needed. It's a neat trick even though the help file states:

"The Unload event can be canceled, but the Close event can't."

Well, it can cancel the close event!

Of course the same help also contains these two statements:

"The Unload event occurs after a form is closed but before it's removed from the screen."
"The Unload event occurs before the Close event."

So...

I tried quite a few combinations of these things and could not get the form to stay open and continue to display a record that needs completion.

Have better coders than I tried and failed to do this?

If I have no chance I'd rather not spend any more time on this. Any additional thoughts?

Thanks again,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.

 

[MajP]

I am confused about this line.

Have better coders than I tried and failed to do this?

I thought I clearly showed you how to keep anyone from closing a form (or database) unless you say it is "OK to Close". I use this technique often and guarantee there is no way you can close the form, if you are using my code or a variation. Maybe I am missing something.

 

[alr0]

Hi MajP,

You did and it works.

What I failed to do was keep the form open and prevent the record from being deleted.

Perhaps this is a little picky but this job has had a lot (too much) of focus on this kind of thing.

"You mean I have to start over, I can't just add the missing number? Boy, this is much harder than the other..."

Thanks again,

alr

_____________________________________
If a large part of intelligence is the ability to generalize, it is ironic that a large part of what we call wisdom is the ability not to generalize.