Results 1 to 10 of 10
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field Validation (Access 2003/SP3)

    Does

    Is Null Or Between 0 And [ReceivedPerIANumber]

    do what you want?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Field Validation (Access 2003/SP3)

    Scenario:
    Changing the Inventory Action Number updates the Currently On Hand amount.
    The Number Transferred can not be negative or exceed the amount currently on hand.
    I've set the validation rule as you can see in the attached graphic. The problem is that if there aren't enough items on hand and a new inventory action number is selected, the rule still triggers even if the new on hand amount is sufficient.

    Am I missing a reset option or is this better done in code?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Field Validation (Access 2003/SP3)

    Nope!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Field Validation (Access 2003/SP3)

    Hah! Changing the problem after a reply has been posted!

    I don't understand why this occurs, but perhaps it's better to use the Before Update event of the form to check the value.

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Field Validation (Access 2003/SP3)

    sorry....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Field Validation (Access 2003/SP3)

    Can you try to prevent the problem, rather than catch the error.

    In this case can you change the row source of the combo box that provides the values for Inventory Action Number?

    Set it so that it only offers values up to the Number Currently ON Hand, and set the limit to list as True.

    It would need to be a query that uses Numberon Hand as a parameter, and you would need to requery whenever the Item changed.
    Regards
    John



  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Field Validation (Access 2003/SP3)

    I'll mull that one over.
    In the meantime, my testing has revealed a further dimension of the problem.
    The validation only works when the first item from the Inventory Action Number field is selected. If I start by selecting another item from the list (even though the Currently On Hand number updates properly on-screen (note: it is written by VBA code to the control), the validation fails.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Field Validation (Access 2003/SP3)

    So if there were 100 on hand the drop-down would be populated with the values 1 - 100?

    It is also failing when I try to check the amount using VBA.

    This bugs me...
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Field Validation (Access 2003/SP3)

    That is what I would try to do.

    I would have thought that you could have a query that tells you the number on hand, and use this value as an upper limit.

    You would need to requery for each item.
    Regards
    John



  10. #10
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Field Validation (Access 2003/SP3)

    Solved it.
    The value of the Currently On Hand control was set as follows in VBA
    Me.ReceivedPerIANumber.Value = Nz(Me.IANumberPick.Column(2, intListItems), "0")
    For whatever reason, the value in the control was not seen properly by the validation code or my first attempts in VBA.
    So I created a public variable whose value was set whenever the Inventory Action Number changed.
    Now my VBA code referencing that variable, works just fine.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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