Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    9

    Unanswered: Sub form input validation from existing value

    Hi All I have to input dates on a subform when gas boilers are serviced on a ten month cycle. On a few occasions the operator has put in the wrong date by accident. I have put validation rules in the table cant be greater then todays date etc. I have tried putting an event in the beforeupdate of the subform but it keeps validating against the date I have just input and not the original date still to be updated in the table. I want to use the original un-updated date in the table to give a validation warning (sometimes if its an empty house or a new boiler the date will be sooner than the 1o month date) that the newly entered date is before the 10 month due date [Last service date entered] < [Last service date in table + 10 months]. Help.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You could use a DMax to find the last service date in the table, and compare the newly entered date against that.
    Paul

  3. #3
    Join Date
    Aug 2009
    Posts
    9
    Thanks Paul
    Your reply gave me an idea and I worked it out using the code below - thanks again
    Private Sub Last_service_date_BeforeUpdate(Cancel As Integer)
    If DLookup("[Arranged date]", "Dates", "[PROP_CODE] = Form![PROP_CODE]") > Form![Last service date] Then
    If MsgBox("Date entered is before arranged date. Do you want to allow this?", vbYesNo + vbQuestion, "Warning") = vbNo Then Cancel = True
    End If
    End Sub

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, and welcome to the site by the way!
    Paul

Posting Permissions

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