Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2010
    Posts
    12

    Unanswered: Conditionally Required Fields?

    I have a form with the fields "DateShipped" and "Location" on it. Location will (soon) be a list box, so that only one item can be in each location. I would like to change the requirements so that Location is required only if there is nothing in the DateShipped field. AKA, if something has already left the warehouse it doesn't need a location anymore, that location is free for something else to come in. But I need to make sure that items always have a location on them if they're in the warehouse.

    Is there a way to make Location conditionally required? And is there a better way of making sure that each location is used only by one item at a time rather than just making a list box and indexing the field?

    Thank you!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    At the table level, I don't think so. I'd use the before update event of your form:

    Before update
    Paul

  3. #3
    Join Date
    Aug 2010
    Posts
    12
    OK, so would my code look like:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Len(Me.DateShipped Is Null And Me.Location Is Null) Then
    MsgBox "You need to fill out Location"
    Cancel = True
    Me.Location.SetFocus
    End If
    End Sub

    I'm a newbie! Thanks!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Does it work? I'm thinking more like:

    If IsDate(Me.DateShipped) And Len(Me.Location & vbNullString) = 0 Then
    Paul

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This rule can (and should) be applied at the table level (EDIT - not contradicting Paul - I think if he had known it could be done there he would have recommended it so).
    1) Table Design view -> Properties
    2) Paste the below in "Validation Rule":
    [DateShipped] Is Null And [Location] Is Not Null Or [DateShipped] Is Not Null

    This means that Location can be NULL or a value if there is a DateShipped. If there is no dateshipped then it must not be NULL.

    Quote Originally Posted by mwhcrew View Post
    And is there a better way of making sure that each location is used only by one item at a time rather than just making a list box and indexing the field?
    The Listbox won't ensure the location is only used once, but it will help the UI. The best way to ensure that a column value is unique is to use a unique index (index with No Duplicates in Access terminology).
    Last edited by pootle flump; 08-10-10 at 05:39.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's a helpful tip for you kids at home: don't post after drinking wine.

    Thanks for the correction.
    Paul

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    But I'd only have about 200 posts to my name
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Then you're clearly better with wine in you than I am. I salute you!
    Paul

  9. #9
    Join Date
    Aug 2010
    Posts
    12
    This is great, thanks so much! One more question - is it possible to delete the value in the location field when the date shipped field is updated? I tried putting this code in the AfterUpdate property:

    Me.Location = Null

    But don't think I have the right wording or syntax. Thanks!

Posting Permissions

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