Field Dependancy - Access

[UberWes]

I'm new to Access and I need a little help. How do you make 1 field dependant upon another? We use a database to handle our mailing list and I would like to have a check box that says Yes/No to receive our newsletter. However I would like a condition that makes the check box verify that and email address is present in another field.

Any help would be great.

[BradHodge]

Is the Email field also on that form? If so, you could enter this code in the BeforeUpdate event of your checkbox...

If IsNull (Me.[I]YourEmailField[/I] ) Then
MsgBox ("You must enter an Email address to receive a newsletter."), , "Forget something?..."
Me..[I]YourEmailField[/I].SetFocus
End If

See if that works for you.

Brad.

[BradHodge]

Code line 3 should just say...

Me.YourEmailField .SetFocus

Darn extra periods!

Brad

[NeoPa]

Nice idea Brad. A small enhancement would be to confirm that the operator had changed it to True. This could be expected, but defensive coding suggests not to rely too heavily on the existing state.

Private Sub chkMailingList_BeforeUpdate(Cancel As Integer)
  If chkMailingList And IsNull(Me.[I]YourEmailField[/I]) Then
    Call MsgBox("You must enter an Email address to receive a newsletter.")
    Me.[I]YourEmailField[/I].SetFocus
  End If
End Sub

[UberWes]

Thank you. It works Great.

One thing though, if you do not enter an email address you get a pop-up warning...perfe ct... However the check box stays checked, when you do not enter anything. How can I get it to force a negative or no check.

Thanks Again.

[JKing]

You can force a check box to be unchecked by setting it equal to false.
Example: Me.chkCheckBox = False

[burg226]

This is the code Wes and I are trying to work with...

Private Sub Constant_Contact_BeforeUpdate(Cancel As Integer)
If IsNull(Me.email) Then

Call MsgBox("You must enter an email address in order to activate this checkbox.")
Me.Constant_Contact = False
Me.email.SetFocus
End If
End Sub

Constant_Contac t is the checkbox and email is the field.

I keep getting an error message when i run it that states "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsft Office Access from saving the data in the field."

I need the Constant Contact checkbox to require text in the "email" field. If it doesnt have any text in the field i need a error message to popup and it to set the checkbox to an unchecked state and move the curser to the email field.

Any help would be greatly appreciated as we are getting pretty frustrated. Thanks everyone.

[BradHodge]

This worked for me...

Change from a Before Update Event to an On Enter Event for that check box.

That should solve the problem for you.

Brad.

[burg226]

It works pretty good now. Thanks everyone for your help.

One more question for you all. When i am tabbing through the fields it won't let me tab by the Constant Contact checkbox unless their is an email address in the email field. The MSGbox always popsup whether i check the checkbox or not but only if there isnt an email address. I need to be able to tab by it even if there is no email address. THis is the code i have on the Constant Contact check box...

Private Sub Constant_Contact_Exit(Cancel As Integer)
If IsNull(Me.email) Then

Call MsgBox("You must enter an email address in order to activate this checkbox.")
Me.Constant_Contact = False
Me.email.SetFocus
End If
End Sub

The only way around the checkbox without the Msgbox popping up is to click around it. I need to be able to tab by it when their is or isn't an email address in the field and with the checkbox checked or unchecked. Thanks again for all your help. I hope this makes sense to all of you.

[JKing]

Well the simplest solution would be to just remove the check box from the tab order.

Another option would be to change the event that controls your code.

[code=vb]
Private Sub Constant_Contac t_Click()
If Me.Constant_Con tact = True Then
If IsNull(Me.email ) Then
Call MsgBox("You must enter an email address in order to activate this checkbox.")
Me.Constant_Con tact = False
Me.email.SetFoc us
End If
End If
End Sub
[/code]

Only when the user clicks the check box the code will fire. Adding another if around the original block of code to see if checkbox is checked. If so verify the email and if thats null display the error message etc. If the checkbox is unchecked it does nothing.

This should eliminate the error message being called when you're only tabbing through the check box.

[NeoPa]

Thank you. It works Great.

One thing though, if you do not enter an email address you get a pop-up warning...perfe ct... However the check box stays checked, when you do not enter anything. How can I get it to force a negative or no check.

Thanks Again.

You need to set Cancel to TRUE :

Private Sub chkMailingList_BeforeUpdate(Cancel As Integer)
  If chkMailingList And IsNull(Me.YourEmailField) Then
    Call MsgBox("You must enter an Email address to receive a newsletter.")
    Me.YourEmailField.SetFocus
    Cancel = True
  End If
End Sub

[NeoPa]

You need to set Cancel to TRUE :

Private Sub chkMailingList_BeforeUpdate(Cancel As Integer)
  If chkMailingList And IsNull(Me.YourEmailField) Then
    Call MsgBox("You must enter an Email address to receive a newsletter.")
    Me.YourEmailField.SetFocus
    Cancel = True
  End If
End Sub

This will avoid the other problems you were getting (and is logically the proper way).

PS. You could equally change line #5 to :

    chkMailingList = False