Field becoming mandatory on condition

[pedroghuk]

Third and final query relating to this pesky inherited Database!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
(This one is a problem I would have had if I had created the Database myself from scratch).<o></o>
<o></o>
In summary, I have a combo box, which when a particularly option is chosen a Message Box appears, advising the User to complete a further field x.<o></o>
<o></o>
My issue is that when this option if selected, I want field x to become mandatory. How do I do this? (Using validation in Table field properties or using VBA?).<o></o>
<o></o>
I have the following on-click procedure in place:<o></o>
<o></o>
Private Sub Speciality_AfterUpdate()
If Me.Speciality = "A&E" Then
MsgBox "Please determine the name of the on-call Physician who would admit the patient"
Me.Speciality.SetFocus
End If<o></o>
End Sub<o></o>
<o></o>
The Physician name would then be a mandatory requirement in field x.<o></o>
<o></o>
Any advice/guidance would be appreciated. Thanks.<o></o>
<o></o>
<o> </o>

 

[boblarson]

In the Before Update event of the form is where you would enforce it.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.Specialty = "A&E" And Len(Me.YourTextBoxNameHere & 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

Be sure to change YourTextBoxNameHere to whatever control name is being used for the extra field information.

 

[pedroghuk]

Perfect Bob
Works well and helps with data integrity.
Regards.
Gary