Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data validation (2003 (SP2))

    Just a quicky, can the data validaion (within table / field properties) use SQL?

    What I'm thinking of, is a new record cannot be be created with a date/time that is less than an existing record date/time + duration. duration being a completed field within the same record.

    E.g. 8/9/06 12:00 + 8:00 (duration) = 8/9/06 20:00, means that a new record cannot be entered, until after 8pm today.

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

    Re: Data validation (2003 (SP2))

    No, you'd have to use the Before Update event of the form used to enter data. You can use DMax to find the most recent date/time in the table. If it is too soon, set the Cancel argument of the event procedure to True.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data validation (2003 (SP2))

    Oh well, 'fraid that's me knackered; I'm only good for two lines of vb <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

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

    Re: Data validation (2003 (SP2))

    If you provide some more details, we can probably help. You could also post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data validation (2003 (SP2))

    Thank you Hans, you're a good man.

    It's actually the same one as on the other thread. But here's an un-corrupted version.

    Oh, FYI I know I mentioned this in the other thread, but the composite primary key for 'TblMain' ;
    DateTime & Furnace

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

    Re: Data validation (2003 (SP2))

    You can use this code:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim t
    ' Get out if this is not a new record
    If Not Me.NewRecord Then Exit Sub
    ' Get out if duration is blank
    If IsNull(Me.duration) Then Exit Sub
    ' Most recent DateTime in table
    t = DMax("DateTime", "tblMain")
    ' Get out if there is no DateTime yet
    If IsNull(t) Then Exit Sub
    ' Check elapsed time
    If Now < CDate(t) + Me.duration Then
    ' Inform user
    MsgBox "Can't create new record yet.", vbInformation
    ' Cancel update
    Cancel = True
    ' Optional: undo record
    Me.Undo
    End If
    End Sub

    See attached version.

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data validation (2003 (SP2))

    That's great Hans, does everything I asked for. <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    The problem now is; me <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22> I didn't explain that there are 5 different furnaces, so the date/time calculation needs to be done for each/relevant furnace. ie if furnace 1 date/time + duration = 11/9 20:00, then (as you've done already) a new record for furnace 1 cannot be created. However it needs to be possible to add a record for any other furnace, if the date/time + duration (for furnace 2) has expired.

    Sorry for messing you about.

    PS I'm hoping to be on a VBA course shortly <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

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

    Re: Data validation (2003 (SP2))

    Only a small modification is needed: change
    <code>
    t = DMax("DateTime", "tblMain")
    </code>
    to
    <code>
    t = DMax("DateTime", "tblMain", "Furnace = " & Me.Furnace)
    </code>
    With an extra check, the code becomes:
    <code>
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim t
    ' Get out if this is not a new record
    If Not Me.NewRecord Then Exit Sub
    ' Get out if duration is blank
    If IsNull(Me.duration) Then Exit Sub
    ' Get out if furnace is blank
    If IsNull(Me.Furnace) Then Exit Sub
    ' Most recent DateTime in table for selected furnace
    t = DMax("DateTime", "tblMain", "Furnace = " & Me.Furnace)
    ' Get out if there is no DateTime yet
    If IsNull(t) Then Exit Sub
    ' Check elapsed time
    If Now < CDate(t) + Me.duration Then
    ' Inform user
    MsgBox "Can't create new record for this furnace.", vbInformation
    ' Cancel update
    Cancel = True
    ' Optional: undo record
    Me.Undo
    End If
    End Sub</code>

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data validation (2003 (SP2))

    Spot on!

    Cheers <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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