Form BeforeUpdate Query

[pedroghuk]

To all

Good afternoon.

I have introduced the following check to effectively make the on-call Physician field mandatory:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Speciality = "A&E" And Len(Me.AdmittingPhysician & vbNullString) = 0 Then
Cancel = True
MsgBox "You need to fill in a name of the on-call Physician if you have selected 'A&E'", vbExclamation, "Entry Error"
End If
End Sub

However, I need this condition to be relevant on other fields - DOB and Who Performance. See below.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Neurncology = "Yes" And (Len(Me.WHOPerformanceStatus & vbNullString) Or Len(Me.DOB & vbNullString)) = 0 Then
Cancel = True
MsgBox "You need to fill in either DOB and/or WHO Performance Status if you have selected Oncology 'Yes'", vbExclamation, "Entry Error"
End If
End Sub

Can the code be merged to one script or is it possible to have two BeforeUpdate events on a Form?

Any solutions or direction would be grateful received.

Thanks.

pedroghuk

 

[Kiwiman]

Howzit

Try

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Speciality = "A&E" And Len(Me.AdmittingPhysician & vbNullString) = 0 Then
Cancel = True
MsgBox "You need to fill in a name of the on-call Physician if you have selected 'A&E'", vbExclamation, "Entry Error"
Elseif 'Put your other criteria here...
End If
End Sub

 

[mdlueck]

Can the code be merged to one script?

At first glance I see no reason that both checks could not be combined into one event.

Both of your checks seem to be setting the Cancel. So either way (either order) unless both checks do NOT return cancel, then the commit would happen, else (either case) the commit would be canceled.

Or you could get fancy joining the checks together with the "And" logical operator and have both checks be required in the same If statement... as Kiwiman suggested while I was typing.

 

[vbaInet]

You need OR not AND.

 

[pedroghuk]

Thanks to all who replied.

One snag, yes the OR operator makes sense, but one Event relates to Me.Speciality = "A&E" and the other to Me.Neurncology = "Yes"; two conditions with two messages.

Would a bracketed OR work, i.e.
... (If Me.Speciality = "A&E" And Len(Me.AdmittingPhysician & vbNullString) = 0 Then
Cancel = True
MsgBox "You need to fill in a name of the on-call Physician if you have selected 'A&E'", vbExclamation, "Entry Error") OR ...

And what would happen if all fields are left blank, i.e. on-Call Physician relating to Specialty; DOB and WHO Performance Status relating to Oncology?

Regards.

 

[vbaInet]

else if

 

[pedroghuk]

Thanks to all.

One further issue ...

The following works:

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Speciality = "A&E" And Len(Me.AdmittingPhysician & vbNullString) = 0 Then
      Cancel = True
      MsgBox "You need to fill in a name of the on-call Physician if you have selected 'A&E'", vbExclamation, "Entry Error"
ElseIf Me.Neuro_Oncology = "Yes" And (Len(Me.WHOPerformanceStatus & vbNullString) Or Len(Me.DOB & vbNullString)) = 0 Then
      Cancel = True
      MsgBox "You need to fill in either DOB and/or WHO Performance Status if you have selected Oncology 'Yes'", vbExclamation, "Entry Error"
End If
End Sub

I have just noticed that if the on-call Physician field, the DOB field or the WHO Performance field are blank then the script kicks in and advises by message box that the User must complete the field(s). What I do not want happening is that when the User selects 'OK' to the message, the Form closes and the vast amount of other information is not saved.

How do I ensure that when the User selects 'OK' that they are returned to the Form and must complete the omitted information?

Regards.

pedroghuk

 

[spikepl]

How do you induce the form to close? http://allenbrowne.com/bug-01.html

 

[pedroghuk]

Spikepl

Thanks. Interesting link but I don't want the form to close (it does at present when the message box is confirmed OK).

I want the User to remain in the form until all three fields are complete.

pedroghuk

 

[spikepl]

The code you showed does not close the form. What does?

 

[mdlueck]

I want the User to remain in the form until all three fields are complete.

In that case I suggest you configure all of your validation code into the button which will save / commit the data to the database table.

If errors are found, simply skip to the end of that event and thus bypass the update of the database / close of the form.

In my Validation class design, I also turn the field's background red which are not valid fields. Remember to flip the field back to white if the value (next time) was found to be valid. Perhaps someone had two bad fields, they fix one, thus the second time the validation rules fire it is now a good value, so the red needs to be turned back off.

 

[vbaInet]

So you want to stop the form from closing if all the required fields? Use a Boolean variable and test against it in the UnLoad event of the form. Cancel the Unload event if the variable isn't set (or whichever way you choose).

But if you want to prevent the form from closing if a record is being editted, then test against the form's Dirty property. If it's Dirty then a record is being editted.