Results 1 to 6 of 6
  1. #1
    JeRz is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    33

    Updating 2 fields in a form based on a checkbox


    Hi everyone,

    I am struggling with what I thought would be easy to accomplish and was hoping for some assistance.

    I have a form with a field called FirstAttempt which is a checkbox. I also have a date and time field (was asked to separate these). I can get the date to populate once the box is checked but, I cannot get the time to populate in the time field. I was trying to use a VBA If/then statement using date() and time(). Can you not use an AND in the If/then?

    The code I tried was based on an OnClick event:
    If me.FirstAttempt=true then
    Me.attempdt=date AND me.attempttm=time()
    End if

    Is there a better solution other than this to accomplish what I need? Thank you for any help!

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    No, not as you have done. Just put them as separate statements.

    What is supposed to happen if you clear the checkbox?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Maintaining FirstAttempt field seems to have little purpose. Are subsequent attempts allowed? And as already asked, what should happen if field is unchecked?

    A normal command button could serve instead of checkbox. Perhaps only populate fields if they are Null unless you want to allow change.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    JeRz is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    33
    Quote Originally Posted by Welshgasman View Post
    No, not as you have done. Just put them as separate statements.

    What is supposed to happen if you clear the checkbox?
    Thank you. I would need the fields to clear if the box would be unchecked.

  5. #5
    JeRz is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    33
    Quote Originally Posted by June7 View Post
    Maintaining FirstAttempt field seems to have little purpose. Are subsequent attempts allowed? And as already asked, what should happen if field is unchecked?

    A normal command button could serve instead of checkbox. Perhaps only populate fields if they are Null unless you want to allow change.
    Thank you for the suggestion! I will try a command button in place of the checkbox. The intent of FirstAttempt was to aid in reporting. That may not be needed, however.

    As for if unchecked, I would need the other fields to change to null.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    With checkbox or try a toggle button:
    Code:
    If Me.FirstAttempt Then
        Me.attempdt = Date
        Me.attempttm = Time
    Else
        Me.attempdt = Null
        Me.attempttm = Null
    End if
    or
    Code:
    Me.attempdt = IIf(Me.FirstAttempt, Date, Null)
    Me.attempttm = IIf(Me.FirstAttempt, Time, Null)
    With a command button and no FirstAttempt field:
    Code:
    Me.attempdt = IIf(IsNull(Me.attemptdt), Date, Null)
    Me.attempttm = IIf(IsNull(Me.attempttm), Time, Null)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Hiding Fields Based on Checkbox
    By zashaikh in forum Forms
    Replies: 4
    Last Post: 12-31-2017, 06:04 PM
  2. Hide/Show fields at Form based on checkbox
    By cap.zadi in forum Forms
    Replies: 8
    Last Post: 04-22-2016, 05:08 AM
  3. Replies: 3
    Last Post: 06-25-2015, 12:54 PM
  4. Updating a Checkbox in a Form
    By groovnvirginia in forum Forms
    Replies: 3
    Last Post: 02-15-2012, 08:29 AM
  5. Report Fields Visible Based on Checkbox
    By ghillie30 in forum Access
    Replies: 2
    Last Post: 09-21-2011, 09:04 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