Make a Field Required

[goaksmith]

I have a question about conditional formatting. I am using Access 97 and I am trying to make a field required or not depending on a certain list box (whose values are yes/no). So basically I have [List Box A] whose values can only be yes/no. And when the value is yes I want [Text Box B] to be a required field. And when the value is no I want [Test Box B] not to be required. Is this possible with Access 97? Any help would be appreciated. Thanks!

 

[Tay]

If you just want data entered into textbox B if listbox A = yes, then try (1). If you don't want text box B to appear at all if listbox A = no then try (2). and if you want both of these to occur, just merge the code.

(1)

Private Sub textboxA_AfterUpdate()

If me.listboxA = "yes" then

MsgBox "you must enter enter data into text box B"

End if

End Sub

(2)

Private Sub textboxA_AfterUpdate()

If me.listboxA = "no" then

me.textboxB.visible = false

End if

End Sub

HTH

 

[Fornatian]

In BeforeUpdate event

If Me.ListBoxA = "Yes" Then
If IsNull(Me.TextBoxB) or Me.TextBoxB = "" Then
Cancel = True
Msgbox "You need to enter a value in TextBoxB", vbCritical,"Entry Required"
End if

 

[goaksmith]

Before update event of what. [List Box A], [Text Box B], or for the form?

 

[goaksmith]

Thanks for the help with the coding. I have added a little bit of both of your code and it is working fine. The only thing is that the [Text Box B] field is not technically required. You can go to the next record without having to complete it. Is there any way to do this or is the only way to have warning boxes making it look like it is required come up?

 

[Fornatian]

You may be able to scroll through previous records without it quoting to have the field completed but the beforeupdate event code should prevent any records being created without an entry in the field being validated.

The before update event fires before a control or record is updated with changed data. It event occurs when the control or record loses the focus, or you click Save Record on the Records menu. This event occurs for new and existing records.

 

[WayneRyan]

You can use the OnCurrent event to trigger the
tests when each record comes up.

You could also make a query/report to list the
existing records that don't comply.

Wayne

 

[Pat Hartman]

Whenever you have edits that are dependent on another control, you must use the BeforeUpdate event of the FORM. That is the place where everything comes together. It is the last event fired before a record is actually saved. It has a cancel argument that you MUST set to true if you find an error. That is what prevents the record from being saved.

WayneRyan, using the Current event won't do you a bit of good because nothing has been changed at that time.

 

[WayneRyan]

I thought that the objective was to trap for
records that were made obsolete by the addition
of a new data field. Hence the OnCurrent and
reports/queries suggestion.

Having reread the post, Pat is absolutely
right (big surprise, eh?).

Wayne