Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2008
    Posts
    25

    Question Unanswered: Code For "if Null"/forced entry on a field?

    Hi Everyone

    I have a form on which I have three fields, Status and date Closed and resolution.

    I currently have users changing the status to Closed, but need a little "sense" check so that if the status field is "closed", the resolution and date closed fields need to be completed.

    Can anyone help here?
    Last edited by MIKEWB20012000; 01-24-09 at 09:22.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    some potential tests to experiment with:
    if isnull(me.someBox) then
    if lenb(me.someBox) = 0 then
    if not isdate(me.someBox) then
    if lenb(nz(me.somebox, vbnullstring)) = 0 then
    if lenb(trim$(nz(me.somebox, vbnullstring))) = 0 then

    can't suggest which event you would use on a bound form (unbound is easy - it goes before your 'manual' save code)

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    When "closed" is entered into the Status field, this code will check the Closed Date field and the Resolution field, and if either are blank, notify the user, move focus to the missing field and return the Status field to it's previous (if any) Value:
    Code:
    Private Sub Status_AfterUpdate()
    If Me.Status = "closed" Then
      
      If IsNull(Me.DateClosed) Then
        MsgBox "A Closed Date Must Be Entered Before Setting Status to 'Closed'"
        Me.Status = Me.Status.OldValue
        DateClosed.SetFocus
        Exit Sub
    
       End If
    
      If IsNull(Me.Resolution) Then
        MsgBox "A Resolution Must Be Documented Before Setting Status to 'Closed'"
        Me.Status = Me.Status.OldValue
        Resolution.SetFocus
        Exit Sub
      End If
     
     End If
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jan 2008
    Posts
    25
    Thanks Missinglinq - works a treat!

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad it works for you!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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