Results 1 to 11 of 11
  1. #1
    WILTSBOY is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    7

    Check Boxes linked to Date Fields on a Form

    Hi,
    I am new to this forum but hope someone will be able to help.
    I have built a new form for non Access users to add data but want to ensure that if they check a check box that certain date fields are completed and cannot be left blank.
    So [checkbox1] should be linked to [date1]


    [checkbox2] should be linked to [date2]
    So [checkbox3] should be linked to [date3]
    The three elements are not linked in any way. Just each specific check box to each specific date field.
    I have heard that code should be added somewhere to the form properties but am unsure what and where.
    Also do I have to add code to each checkbox property?
    Appreciate any help that can be given.
    Many thanks,
    WiltsBoy

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Check boxes cannot link to a Date.
    check boxes link to Boolean fields.
    Date fields link to textboxes.

    You can use a checkbox in a query to show if a date is IN the field. But user cannot change the check....
    IsDate([dateField])

  3. #3
    WILTSBOY is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    7
    I am sure I have seen code whereby if a user ticks a checkbox (yes/no field) on a form then the record cannot be saved unless a corresponding date field is populated as a mandatory field. Maybe I didn't explain properly in my initial thread.
    Is there some code that is added to the AfterUpdate property of the Form?
    Thanks for any help.
    Wiltsboy.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I am sure I have seen code whereby if a user ticks a checkbox (yes/no field) on a form then the record cannot be saved unless a corresponding date field is populated as a mandatory field.
    This is absolutely not correct. There are no fields that link to other fields on one table unless you write code to force the issue. it is only your relationships between tables where it becomes mandatory that something exists prior to saving a record.

    So, do you want these dates or are you putting them there just because it says you have to? To populate the date fields, in the OnClick event of the checkbox you will set the date field on the form to a particular value.

  5. #5
    WILTSBOY is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    7
    I think I am confusing matters here in not explaining things clearly. I have 3 checkbox fields named for example CB1, CB2 and CB3. I have 3 text box date fields named for example Date1, Date2 and Date3. I would like the data entry user to have to populate the relevant date fields on a form if they have checked any of the three check boxes.
    ie if CB1 checked then a date must be input into Date1 etc etc. I think this requires code of some sort in the properties of the form. It's what and where this code goes that is my requirement. I don't want the data entry user to be able to skip date fields if their corresponding check box has been ticked. The date field becomes mandatory if the corresponding check box has been ticked.
    Hope this makes it clearer.
    Many thanks,
    Wiltsboy

  6. #6
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Use the form BeforeUpdate event to check when checkbox(n) is true, then check if datevalue(n) is populated. If any isn't, then present message box and set Cancel = True. But I have to ask, what is the purpose of the check boxes? Can users not just enter the date value?

  7. #7
    WILTSBOY is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    7
    The checkboxes differentiate certain elements of data. User could check just one or two or all three. Different permutations on different records.
    could I ask you to help out on what code would go in the BeforeUpdate event of the form? Using my previously mentioned data labels for checkboxes and date fields.
    Thank you very much. Would be greatly appreciated.

  8. #8
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Perhaps:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Dim strMessage As String
    
        If Me.checkbox1 Then
            If Not(IsDate(Me.date1)) then
                strMessage = ";CheckBox1 Checked, Date1 not Entered."
            End If
        End If
    
        If Me.checkbox2 Then
            If Not(IsDate(Me.date2)) then
                strMessage = strMessage & ";CheckBox2 Checked, Date2 not Entered."
            End If
        End If
    
        If Me.checkbox3 Then
            If Not(IsDate(Me.date3)) then
                strMessage = strMessage & ";CheckBox3 Checked, Date3 not Entered."
            End If
        End If
    
        If strMessage & vbNullString <> vbNullString Then
            MsgBox "Required input is missing:" & Replace(strMessage,";",vbCrLf)
            Cancel = True
        End If
    End Sub
    Air code, so you may have to make corrections.

  9. #9
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    As a side note, if your controls are really named checkbox(n) and date(n), then I recommend you change them to meaningful names so code maintenance now and in the future will be easier.

    By the way,

  10. #10
    WILTSBOY is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    7
    Thank you for your assistance. My Controls are named something more relevant. I used those names purely for simplicity in gaining forum help. Don't think I explained myself properly in first instance.

  11. #11
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Okay, but for the future when you are describing any issue you are dealing with, please use real object names - it makes it harder for us if you don't. Cheers.

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

Similar Threads

  1. Check Boxes linked to table?
    By natonstan in forum Forms
    Replies: 5
    Last Post: 11-03-2014, 03:46 PM
  2. Filtering data with date and check boxes
    By raffyT in forum Access
    Replies: 9
    Last Post: 08-26-2014, 11:46 AM
  3. Replies: 6
    Last Post: 05-10-2012, 10:32 AM
  4. yes/no fields as check boxes
    By mejia.j88 in forum Reports
    Replies: 6
    Last Post: 01-30-2012, 10:01 AM
  5. How to Query fields with check boxes?
    By JynxRD in forum Queries
    Replies: 2
    Last Post: 09-10-2010, 08:35 PM

Tags for this Thread

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