Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi all! I am trying to create kind like a validation vba code for a datasheet form. I have a date field which I want to confirm its value within a already established date range before updating or adding the data. I have two date fields with the startdate and enddate info where the comparison or validation needs to happen..

    This is what im working on so far..

    Private Sub ActualStartDate_BeforeUpdate(NewData As String, Cancel As Integer)

    If NewData <> Between Me.CycleStartDate And Me.CycleEndDate Then
    msgBox "Please enter a date within the prescribe range"
    Me.ActualStartDate.Setfocus
    End If

    I notice "Between..And" operator doesnt work on vba unless being used by the HAVING clause in a SELECT statement. what other command can i use to perform the above task in vba code or as an expression. Any help is super welcome!!!

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Lastcall' post='799985' date='26-Oct-2009 19:08']Hi all! I am trying to create kind like a validation vba code for a datasheet form. I have a date field which I want to confirm its value within a already established date range before updating or adding the data. I have two date fields with the startdate and enddate info where the comparison or validation needs to happen..

    This is what im working on so far..

    Private Sub ActualStartDate_BeforeUpdate(NewData As String, Cancel As Integer)

    If NewData <> Between Me.CycleStartDate And Me.CycleEndDate Then
    msgBox "Please enter a date within the prescribe range"
    Me.ActualStartDate.Setfocus
    End If

    I notice "Between..And" operator doesnt work on vba unless being used by the HAVING clause in a SELECT statement. what other command can i use to perform the above task in vba code or as an expression. Any help is super welcome!!![/quote]


    Not (NewData >= Me.CycleStartDate And NewData <= Me.CycleEndDate)

    Ought to do iy
    Andrew

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='799987' date='26-Oct-2009 20:14']Not (NewData >= Me.CycleStartDate And NewData <= Me.CycleEndDate)

    Ought to do iy[/quote]


    Thanks Andrew for your help. I tried your recommendation; is not evaluating the ActualStartdate only withing the cycle start and end date range..so it keeps executing the msgbox and saving the changes. here it is:

    Private Sub ActualStartDate_BeforeUpdate(Cancel As Integer)

    If Not (Me.ActualStartDate >= Me.CycleStartDate And Me.ActualStartDate <= Me.CycleEndDate) Then
    Undo
    MsgBox "Please enter date within range"
    End If

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of using Undo, tell Access to cancel the update:

    Cancel = True

  5. #5
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799996' date='26-Oct-2009 20:42']Instead of using Undo, tell Access to cancel the update:

    Cancel = True[/quote]

    Sorry Guys..:-( THere is this Prompt that comes up "Property not found" and i noticed it relates to changing the view of the form from continuous to datasheet. The prompt just need to be click "ok" and everything still good. How can I supress this prompt? I tried using DoCmd.SetWarnings False but it doesnt do the trick. any suggestions.

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Lastcall' post='800158' date='27-Oct-2009 18:14']Thank you guys!! it works like a champ...:-) Whoo hooo!!

    Sorry Guys..:-( THere is this Prompt that comes up "Property not found" and i noticed it relates to changing the view of the form from continuous to datasheet. The prompt just need to be click "ok" and everything still good. How can I supress this prompt? I tried using DoCmd.SetWarnings False but it doesnt do the trick. any suggestions.[/quote]


    I think it might be the syntax Me.Field etc

    Try changing it to Me!Field

    e.g.

    Code:
    If Not (Me!ActualStartDate >= Me!CycleStartDate And Me!ActualStartDate <= Me!CycleEndDate) Then
    	  MsgBox "Please enter date within range Or Press ESC to Clear"
    	  Cancel=True
    End If
    Andrew

  7. #7
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='800173' date='27-Oct-2009 20:25']I think it might be the syntax Me.Field etc

    Try changing it to Me!Field

    e.g.

    Code:
    If Not (Me!ActualStartDate >= Me!CycleStartDate And Me!ActualStartDate <= Me!CycleEndDate) Then
    	  MsgBox "Please enter date within range Or Press ESC to Clear"
    	  Cancel=True
    End If
    [/quote]

    Andrew I changed the syntax as you recommended but the "property not found" message still pops up.

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Lastcall' post='800190' date='27-Oct-2009 20:57']Andrew I changed the syntax as you recommended but the "property not found" message still pops up.[/quote]


    I cannot create the error.

    Could be a missing library file.
    Look in tools references for anything ticked with missing against it.

    If not would need a sanitized copy of database to look at.
    Doesn't need any real data in it.
    Andrew

  9. #9
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='800192' date='27-Oct-2009 22:02']I cannot create the error.

    Could be a missing library file.
    Look in tools references for anything ticked with missing against it.

    If not would need a sanitized copy of database to look at.
    Doesn't need any real data in it.[/quote]

    Andrew, thanks again...Im working on the copy of the database. I checked the library and no missing items.

Posting Permissions

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