Changing Field Requirement

[ianacole]

Is it possible, with the selection of a entry in a drop list, to change the requirement of another field. What I'm trying to do is, in a "Probability of Close" combo list, if 100% is selected from the choices, to make the "Install Date" field required. Otherwise, this field isn't even visible (which I've already figured out with many thanks to this forum).

Thanks in advance,

Ian

 

[jwindon]

You will add code to the AfterUpdate of your combo to toggle the visiblity of the InstallDate field.

If Me.ComboBox = "100%" Then
Me.InstallDate.Visible = True
Exit Sub
Else

End If

I do not know of a way to actually CHANGE the field requirements to REQUIRED outside of table view, which would not work for your form, however, there are several fixes you can set so as not to allow the user to leave the record. Running code, toggling visibility, not showing close buttons or navigational buttons, etc.

If IsNull(Me.InstallDate) Then
Msgbox "You must enter an install date!"
Me.InstallDate.SetFocus
Exit Sub

Else

do your other actions here

End If

That should point you in the right direction.

 

[ianacole]

Thanks for the direction. But I'm not quite getting this to work. Here is the code:

Private Sub Combo58_Change()
If Me.[Sales Phase] = "5" Then
Me.[Install Date].Visible = True
Me.[Install Date].Enabled = True
Me.[Install Date].SetFocus
Else
If Me.[Sales Phase] <> "5" Then
Me.[Install Date].Visible = False
Me.[Install Date].Enabled = False
Else
Exit Sub
End If
End If

End Sub

Private Sub Install_Date_Exit(Cancel As Integer)
If IsNull(Me.Install_Date) Then
MsgBox ("You must enter an Install Date!")
Me.[Install Date].SetFocus
End If

End Sub

As you can see, the first sub enables and makes visible the [Install Date] when phase 5 is selected. The second sub produces the error box. I have two issues:

1. When moving from record to record, the visibility of the [Install Date] remains the same through the records based on the last selection. How can I make this dependant upon the current record?

2. When I exit [Install Date] with no date input, I get the error window. However, after clicking okay, the cursor doesn't return to the [Install Date], and I can move through and write the record with no date in the field. Any suggestions?

Thanks,

Ian

 

[jwindon]

Is 5 the value of 100%? If it is, drop the quotation marks.

Make sure the field names are EXACLTY liked they are named. It is good practice to NOT use spaces in field names.

Set the visibility of InstallationDate to Visible: No and Enabled: No in the form design.

Put this code on the OnCurrent Event of your form:

Me.Installation Date.Visible = False
Me.Installation Date.Enabled = False

That will return your form control to the way you want it as you move from record to record.

'checks value of Sales Phase

'Sales phase is 100%
If Me.Sales Phase = 5 Then
Me.Install Date.Visible = True
Me.Install Date.Enabled = True
Me.Install Date.SetFocus

Else
'Sales phase is NOT 100%
'therefore the installation date
'field should not be visible
'since the property of the control is
'already set to visible=no, there is no
'need to set anything here

End If

If IsNull(Me.InstallDate) Then
Msgbox "You must enter an installation date!"
Me.Installation Date.SetFocus
Exit Sub
Else
End If


You were leaving out the "Exit Sub" on your code to check the Installation Date for IsNULL. That threw off your code.

The single quote before a line(as above) can be included in your code. It should be green and will not affect any procedures taking place in the code. It is there for your benefit later.


[This message has been edited by jwindon (edited 11-27-2001).]

 

[ianacole]

Thanks for the help. The second if-then-else statement was to allow for the changing of the sales executives mind (or should they mistakenly select the wrong item). I apologize about the confusion with the 100% vs. the 5. Our scale on sales phase is 1-5, with one being the equivelent of 50% and 5 100%.

However, I am still not getting the SetFocus to return to the Install_Date field after exit. This is allowing for the writing of the record without this field complete. Here is the complete code:

Private Sub Form_Current()
If Me.[Sales Phase] = 5 Then
Me.[Install Date].Visible = True
Me.[Install Date].Enabled = True
Exit Sub
Else
Me.[Install Date].Visible = False
Me.[Install Date].Enabled = False
End If

End Sub

Private Sub Combo58_Change()
If Me.[Sales Phase] = 5 Then
Me.[Install Date].Visible = True
Me.[Install Date].Enabled = True
Me.[Install Date].SetFocus
Else
If Me.[Sales Phase] <> 5 Then
Me.[Install Date].Visible = False
Me.[Install Date].Enabled = False
Else
Exit Sub
End If
End If

End Sub

Private Sub Install_Date_Exit(Cancel As Integer)
If IsNull(Me.Install_Date) Then
MsgBox ("You must enter an Install Date!")
Me.[Install_Date].SetFocus
Exit Sub
Else

End If

End Sub

Would A do-loop until me.[install_date] not IsNull work?

Thanks again for your help.

Ian



[This message has been edited by ianacole (edited 11-27-2001).]

 

[Rich]

Use the before update event of the form,
If Me.Sales_Phase = "5" And IsNull(Me.Install_Date) Then
Cancel=True
MsgBox ("You must enter an Install Date!")
Install_Date.SetFocus
End If
Avoid spaces in your field names Access doesn't like them.

 

[ianacole]

Fantastic!! Thanks!!!


Ian