Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2011
    Location
    USA
    Posts
    13

    Red face Unanswered: Creating If and Then events

    I am having a hard time with manipulating one of my fields that is a text field that holds a Small Date Format (00/00/9999). I want it to be dependant on the previous field that ask for DispositionDestination. Basically if [DispositionDestionation]="Long Island" then [DateToDisposition]="N/A"

    Otherwise I want it to be able to contain the date that I want to input manually. This is what I have right now and it is not working:

    Private Sub DispositionDestination_Afterupdate ()
    If Me.DispositionDestination = "LongIsland" Then
    Me.DateToDisposition = "N/A"
    Else
    Me.DateToDisposition = " "
    End If
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The Field that DateToDisposition is Bound to has to be defined as Text Datatype, rather than as DateTime, in order for "N/A" to be valid data. Assuming that is how you have it set up, the problem I see is that your code says

    If Me.DispositionDestination = "LongIsland" Then

    and I'm guessing that you're testing by entering

    "Long Island"

    with the space between Long and Island.

    Correct your code to include the space in your If...Then Statement.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2011
    Location
    USA
    Posts
    13

    Question

    Is there a way to keep the integrity of the Input Mask if I want the user to insert a date if it is other than Long Island?

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    No way that I know of! It has to be defined as a Text Field in order to accept the 'N/A' if the other Field is Long Island, and this precludes the use of a DateTime Input Mask, in my testing in v2003.

    My only suggestion would be to have the underlying Field defined as Date/Time, and if the entry for DispositionDestination is "Long Island," have a date such as 1/1/2099 assigned, with the If...Then construct, with users understanding that this date actually means N/A.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Don't put N/As in fields. Bad news.

    That is what NULLs are for.

    If the person enters Long Island, on that field's AfterUpdate, disable/lock the date entry field so that no entry can be made.

    This accomplishes exactly what you want without the N/A.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Or, if this is a Single View Form, you could actually make the DateToDisposition Textbox non-visible if Long Island is entered in the DispositionDestination Field.

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Oct 2011
    Location
    USA
    Posts
    13
    Quote Originally Posted by PracticalProgram View Post
    Don't put N/As in fields. Bad news.

    That is what NULLs are for.

    If the person enters Long Island, on that field's AfterUpdate, disable/lock the date entry field so that no entry can be made.

    This accomplishes exactly what you want without the N/A.
    Is there a Code that I would need for that or would I do it in Expression?

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Code on the AfterUpdate event of the DispositionDestination field.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Oct 2011
    Location
    USA
    Posts
    13
    Quote Originally Posted by PracticalProgram View Post
    Code on the AfterUpdate event of the DispositionDestination field.
    Ok, Thanks for all your help. Code confuses me pretty easy.

  10. #10
    Join Date
    Oct 2011
    Location
    USA
    Posts
    13
    Private Sub DispositionDestination_Afterupdate ()
    If Me.DispositionDestination = "LongIsland" Then
    Me.DateToDisposition = Null
    Else
    Me.DateToDisposition = " "
    End If
    End Sub

    This is not working, I can still input information in the DateToDisposition Field.

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by GallantDon View Post
    ...This is not working, I can still input information in the DateToDisposition Field.
    That's because you haven't Locked the Field! And you still have LongIsland as one word with no space in between the two words! Is that really how you expect your users to enter Long Island? Adjust it in thte code, if necessary, but I'll assume it's suppsed to have the space:
    Code:
    DispositionDestination_Afterupdate ()
      If Me.DispositionDestination = "Long Island" Then
      Me.DateToDisposition.Locked = True
    Else
      Me.DateToDisposition.Locked = False 
    End If
    End Sub
    You'll also need to place the same code in the Form_Current event.

    Linq ;0)>
    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
  •