Results 1 to 5 of 5
  1. #1
    mick3911 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2013
    Location
    Wirral, UK
    Posts
    66

    Issue with data required

    I have a continuous form with, amongst others, has 3 fields, Date field, Name field and Time field. If one of these 3 fields has had data entered, then to other 2 fields require data. I achieved this by using the following code on the forms’ Before Update property which works fine;

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not IsNull([Date]) And IsNull([Name]) Then
    [Name].SetFocus
    MsgBox "The name must be entered in the Name’ field."
    Cancel = True
    Else
    If Not IsNull([Date]) And IsNull([Time]) Then
    [Time].SetFocus
    MsgBox "The time must be entered in the 'Time' field."
    Cancel = True
    Else
    If Not IsNull([Name]) And IsNull([Date]) Then
    [Date].SetFocus
    MsgBox "The date must be entered in the 'Date' field."
    Cancel = True
    Else
    If Not IsNull([Name]) And IsNull([Time]) Then
    [Time].SetFocus
    MsgBox "The time must be entered in the 'Time' field."
    Cancel = True
    Else
    If Not IsNull([Time]) And IsNull([Date]) Then
    [Date].SetFocus
    MsgBox "The date must be entered in the 'Date' field."
    Cancel = True
    Else


    If Not IsNull([Time]) And IsNull([Name]) Then
    [Name].SetFocus
    MsgBox " The name must be entered in the Name’ field."
    Cancel = True
    End If
    End If
    End If
    End If
    End If
    End If
    End Sub

    To prevent a date in the future being entered in the Date field, I have used the following code in the fields’ Exit property

    Private Sub Date_Exit(Cancel As Integer)
    If Date.Value > Date Then
    MsgBox "You cannot enter a date in the future!", vbExclamation
    Cancel = True
    End If
    End Sub

    The issue I have is that if a user enters a date in the Date field then realises that he/she has entered data in the wrong record and deletes the date entered then tries to moves the cursor to another field the message "You cannot enter a date in the future!" keeps appearing.

    How do I work around this?

    Many thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You shouldn't use 2 fields for date/time.
    a date/time field holds both,and you want both in it, not separated.
    i hope your 2 textboxes reference a single field but with 2 formats to clarify the entry.

    I would avoid BEFORE update event,it causes too many problems. (At least for me)
    ive never had a problem with AFTERupdate.

    all field checking should be done in the afterupdate or when user tries to exit the form.

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Before Update is definitely the place to put form validation if you don't want a user to be able to save incomplete records, as it lets you cancel the event.

    Secondly Date , Name and Time are all reserved words and you have to remember to always put [ ] around them. You will cause yourself issues with those as field names somewhere down the line.
    They are also very non-descriptive. EventDate or PaidDate etc, will be much more descriptive when used in queries etc.

    As Ranman suggested DateTime fields always have a time part, so why not store then as one field? You could display the time separately if you needed to.

    With regard to your issue, you could add a cmd Button that allowed a date to be changed, by setting a flag that you used to bypass the validation?
    Or pop up a input box to allow a new date to be entered and use VBA to change the date with the input box value.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Does this work (you should really follow the advice and change the names of these fields to non-reserved words):
    Code:
    Private Sub Date_Exit(Cancel As Integer)
    If IsNull(Me.[Date]) Then 
         Exit Sub
    Else
        If Me.[Date] > Date Then
             MsgBox "You cannot enter a date in the future!", vbExclamation
             Cancel = True
        End If
    End iF
    End Sub
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    DateTime fields always have a time part
    OP needs to realize that to store a valid time component you have to use Now() or some other cobbled approach to getting the time part into the date value. If you use Date() the time defaults to 12:00:00 AM
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Required to count Data
    By Mubashar in forum Queries
    Replies: 2
    Last Post: 03-08-2016, 04:50 AM
  2. Replies: 5
    Last Post: 05-15-2015, 10:49 AM
  3. Sample Data Base Required
    By mirfanmalik in forum Access
    Replies: 5
    Last Post: 04-17-2015, 11:38 AM
  4. Possible outline structure for this data is required
    By Resorte in forum Database Design
    Replies: 3
    Last Post: 08-03-2012, 12:21 PM
  5. Automated Data Collection - Alert Required
    By MarkHenderson in forum Access
    Replies: 1
    Last Post: 11-25-2010, 06:21 AM

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