Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    Data validation on each row.

    Using this code for testing:



    Code:
    Private Sub Command50_Click()
    If Me.Quantity_to_inv > Me.remaining Then
       MsgBox "Value exceeds available quantity"
       Cancel = True
       Me.Inv_Quantity.SetFocus
       Else
        MsgBox "Created Invoice"
     End If
    End Sub
    to test:

    * I have two lines on the form. One passes this rule. One doesn't.
    * when this code is assigned to a button in the footer, then then we get the message "created invoice".
    * when assigned to a button on a line, the message is "Value exceeds available quantity" <- this is on both lines despite one of them being correct.

    So in both places this does not do as I want.

    The problem is, I don't want the ability to create an invoice that will exceed the order value. Each order has multiple lines so the have to all pass this validation rule.

    Anyone have a suggestion? thanks.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Is the form in Continuous Form or Data Sheet view? Where are you looking at the Cancel variable?

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Its a Continuous Form and I'm not sure what you mean with the second question. Okay I now realise you're referring to the code there. I don't think its needed. Ill take it out.

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Okay: Ive amended the code.

    Code:
    If Me.Quantity_to_inv > Me.remaining Then
       MsgBox "Value exceeds available quantity"
       Me.Quantity_to_inv.SetFocus
       Else
        MsgBox "Created Invoice"
     End If
    End Sub
    This now always comes up with "created invoice" no matter where it is. One of the remaining values on the form is now -600 so any value I try on that line should not come up with this message.

    Edit: searching validation on continuous forms. Might help haha.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would use the BeforeUpdate event of the Quantity_to_inv control. Cancel will then hold the cursor in that control.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe try the OnCurrent of the form:

    If Me.Quantity_to_inv > Me.remaining Then
    MsgBox "Value exceeds available quantity"
    Me.Inv_Quantity.SetFocus
    End
    Else
    MsgBox "Created Invoice"
    End If

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    That opens a whole new can of worms. Currently I have to update that field via another form. The recordset of the first form has a few calculations and is not updatable. I have tried to include on my second form the information for remaining quantity but this then also makes it not updatable. Having real issue with this validation. I'm sure it should be simple. but every route I go down seems to come to a dead end.

    Note: The remaining quantity is a calculation. It adds up all the quantities for that line, then it subtracts that value from the order quantity leaving us with the remaining quantity.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm having a little trouble understanding your structure. Any chance you can post the zipped up db? Removing any sensitive data of course.

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Database1 - Copy (2).zip

    I've deleted anything I'm not sure about data wise. the form I'm working with is called invoice generator. Any comments are appreciated tho.
    Attached Files Attached Files

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    That will help. I would suggest you Compile your project and fix all of the errors as well. While viewing code go to Debug>Compile

  11. #11
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    If you're referring to the audit code. I'm aware of it. I have the solution bookmarked its just in a long queue of things to do lol. This is my fourth database I have done for this company, if you seen my first one you wouldn't be helping me. the structure of it was terrible haha. it worked tho. surprisingly. Any advice relating to this issue would be greatly appreciated also.

    Even if this is the wrong solution I'm willing to start from scratch and get it right. I don't need the answer just a direction of where I'm headed.

  12. #12
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    With the debug > compile. I've just realised there are more than that one problem (once I've just turned the code into a quote it goes to the next error).

    I have code half working every where to be honest. I wouldn't worry too much about it haha. I'm working on it.

    Its just become a priority for the basic functionality of this database. I just want to create invoices that cant exceed an order value.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm starting to understand your project a bit now. I had to comment out the compile errors before I could get to work. I'll be back.

  14. #14
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    If there any questions, no matter how obscure feel free to ask them.

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I have a question: The Record Source of the Invoice Generator form is not update-able. How did you expect the user to interact with this form?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 01-07-2014, 04:20 PM
  2. Data Validation
    By Traci in forum Programming
    Replies: 1
    Last Post: 08-21-2012, 10:59 AM
  3. Data Validation using VBA
    By Cheshire101 in forum Programming
    Replies: 3
    Last Post: 05-10-2011, 08:43 AM
  4. Data Validation of another field
    By dssrun in forum Access
    Replies: 4
    Last Post: 03-22-2011, 01:09 PM
  5. Data Validation - Please help
    By larry S in forum Access
    Replies: 0
    Last Post: 11-16-2008, 10:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums