Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is Not Null (Access 2000)

    I am trying to create a control on my form that will give a pop up message if one field is filled in but not another. For example, if a user populates an accepted/declined field (combo box), I want them to have to populate the date field associated with the accepted/declined field. If they try to exit the form, I want them to have a pop up that says they must complete the field. I have heard of using validation rules in tables but using them in forms with an event procedure from another field... I am not sure. Help. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is Not Null (Access 2000)

    You can write code for the BeforeUpdate event of the form. This has a Cancel argument that you can set to True to prevent the record from being updated.

    Open the form in design view.
    Activate the Properties window.
    Activate the Events tab of this window.
    Click in Before Update.
    Select Event Procedure from the dropdown list.
    Click the builder button (the three dots ... to the right.)
    Make the code look like this, with the appropriate names substituted for the combo box (cbxAcceptDecline in my example) and text box (txtDate):

    Private Sub Form_BeforeUpdate()
    If Not IsNull([cbxAcceptDecline]) And IsNull([txtDate]) Then
    MsgBox "You must enter a date!", vbExclamation
    [txtDate].SetFocus
    Cancel = True
    End If
    End Sub

    Switch back to Access, and save the form. Open it in form view and try to leave the date empty while the combo box is not empty.

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is Not Null (Access 2000)

    I got that to work, however, if I add an exit button to the form and try to get out, it does not give the focus back to the date field. It just closes the form without saving. If I use the close button of the window, it works great. Can I add an event that will cancel my exit button if the field is null? And still get the pop up message? Thanks Hans.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is Not Null (Access 2000)

    You can put similar code in the On Unload event of the form. To avoid duplication, you can create a function to be called from Form_BeforeUpdate and Form_Unload:

    Private Function TestDate() As Integer
    If Not IsNull([cbxAcceptDecline]) And IsNull([txtDate]) Then
    MsgBox "You must enter a date!", vbExclamation
    [txtDate].SetFocus
    TestDate = True
    End If
    End Function

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = TestDate
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    Cancel = TestDate
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •