Want form to dis-allow entry dependent on value of another field

[Guest]

I basically am looking to modify a form so that particular fields are "greyed
out" and won't allow entry if a certain value is chosen in another field.
For example, the first field will be type of contact. If it is "incoming
call", I want certain fields to require a null value. If is is "email" I
want different fields to require a null value and for the email address field
to require entry. Thank you in advance for your help.

 

[UpRider]

Pipe, first problem is to make sure the users fill in 'Type of Contact'
first. The way to do that is to make that field the only one available for a
new record.
Look thru the below and see the comments on how to set up the form for each
type of contact

HTH, UpRider

Private Sub Form_Current()
If Me.NewRecord Then Call fcnDisableAll
me.TypeofContact.setfocus 'put the cursor there
End Sub

Function fcnDisableAll()
Me.txtThis.Enabled = False
Me.txtThat.enabled = False
'and so on for all the controls
End Function

Then, when the user fills in TypeofContact
Private sub TypeofContact_AfterUpdate()
select case TypeofContact
case "incoming call"
me.txtThis.enabled = True
me.txtThat.enabled = False
'and so on to set up the form for this type of contact
case "email"
me.txtEmail.enabled = True
me.txtSomething.enabled = False
'and so on to handle the email entry
' add additional cases for all other possibilities for type of contact
case "memo"
'set up form
case "drop in"
'set up form
case else
msgbox "Unknown type of contact"
end select
end sub

 

[missinglinq via AccessMonster.com]

OK Pipe, listen up! This may sound and look complicated at first, but it
really isn't, especially compared with the alternative, which is to deal with
the staus of each and every textbox separately. In order to do this you need
to use the Tag Property of your textboxes to tell Access which ones to make
enabled and which ones to "grey out."

To set the Tag property of a textbox:

Right Click on the textbox
Click on Properties
Click on Other

For textboxes you want enabled if TypeOfContact is "Email" in the Tag
Property box enter Email (no quotation marks!) Be sure and do this for Your
EmailAddress textbox!

For textboxes you want enabled if TypeOfContact is "Incoming Call" in the Tag
Property box enter ICCall (no quotation marks!)

Now what this does, basically, is check to see what value is entered in your
TypeOfContact textbox, loop thru your textboxes, and set them to Enabled =
True or Enabled = False, dependng on your wishes. If the user makes a mistake
and has to change the TypeOfContact, the textboxes will change accordingly.

'This sets the textboxes to Enabled or not Enabled immediately
'and if the TypeOfControl is "Email" also sets the focus to
'EmailAddress textbox

Private Sub TypeOfContact_BeforeUpdate(Cancel As Integer)
Dim ctrl As Control

Select Case TypeOfContact

Case "Email"
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If ctrl.Tag = "Email" Then
ctrl.Enabled = True
End If
If ctrl.Tag = "ICCall" Then
ctrl.Enabled = False
End If
End If
Next
EmailAddress.SetFocus

Case "Incoming call"
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If ctrl.Tag = "ICCall" Then
ctrl.Enabled = True
End If
If ctrl.Tag = "Email" Then
ctrl.Enabled = False
End If
End If
Next

Case Else
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If ctrl.Tag = "ICCall" Then
ctrl.Enabled = True
End If
If ctrl.Tag = "Email" Then
ctrl.Enabled = True
End If
End If
Next
End Select

End Sub

'This code does the same check as the previous code, but it does it each time
you move from one record to another, so that the Enabled status of each
textbox is set properly

Private Sub Form_Current()
Dim ctrl As Control

Select Case TypeOfContact

Case "Email"
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If ctrl.Tag = "Email" Then
ctrl.Enabled = True
End If
If ctrl.Tag = "ICCall" Then
ctrl.Enabled = False
End If
End If
Next

Case "Incoming call"
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If ctrl.Tag = "ICCall" Then
ctrl.Enabled = True
End If
If ctrl.Tag = "Email" Then
ctrl.Enabled = False
End If
End If
Next

Case Else
For Each ctrl In Me.Controls
If ctrl.ControlType = acTextBox Then
If ctrl.Tag = "ICCall" Then
ctrl.Enabled = True
End If
If ctrl.Tag = "Email" Then
ctrl.Enabled = True
End If
End If
Next
End Select
End Sub

'If the TypeOfContact is "Email" focus is immediately placed on the
'EmailAddress textbox. Thiscode insures that something is entered
'into this textbox. If the textbox is left empty, a messagebox pops
'up and focus returns to the box. In the line

'AnyEmailEnabledTextbox.SetFocus

'need to substitute the name of one of the textboxes that is "tagged" Email
'in place of

'AnyEmailEnabledTextbox

'Focus has to move to another control before being returned to the
EmailAddress box

Private Sub EmailAddress_LostFocus()
If IsNull(Me.EmailAddress) Then
MsgBox "You MUST Enter an Email Address!"
AnyEmailEnabledTextbox.SetFocus
EmailAddress.SetFocus
End If
End Sub

After getting the above code up and running, there's a couple of other things
you need to think about.

This code will only work if the value in TypeOfContact is either "Email" or
"Incoming Call." If something else is entered, or if either one of these is
misspelled, the code will fail and it will default to the *Case Else*
statement, which leaves all textboxes Enabled. You may want to do some
validation of the values in this field to be sure that a valid entry has been
made.

You may also want to modify Sub EmailAddress_LostFocus() to not only check
that it has been filled in, but that the data is a valid email address.

But get the above code implemented first, then worry about these issues.

Good Luck

Linq ;0)>

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com