Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32

    Using the expression builder to determine whether a check box is ticked or not


    I've attempted to use the expression builder using a IIF statement if two of the fields are equal then a tick box is automatically ticked.

    I'm not getting any error messages on the following statement:

    =IIf([2017-18 data subform1].[Form]![Full name of person who agrees to the terms of the Conditi_Label]=[name on form],TRUE, FALSE)

    but nothing happens.

    Is this even possible?

    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
    chkBox doesnt have a formula, you assign on the ON CURRENT event


    Code:
    sub Form_OnCurrent()
    chkBox.value = me.[2017-18 data subform1].[Form]![Full name of person who agrees to the terms of the Conditi_Label]=[name on form]
    end sub
    

    tho Im not sure if your assignment is correct, since I dont know the control names.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You really need to do something about your naming system....

    Either use the FIELD names for the two fields
    Or use the full control name if the controls are bound to the fields
    You can't use a label in this context.

    Assuming this code is being done from the form, use Me.ControlName syntax on both sides of the equals sign
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Sorry, I'm a total noob when it comes to this. How do I trigger a On CURRENT event?

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In design view, go to the property sheet, select form then On Current.
    Add an event procedure

    Or in the VB editor, select Form ...Current

    but I still say you will need to change what you have written as in my first reply
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Ahh yes, I see I put the label in there. So this is far as I've got:

    =IIf([2017-18 data subform1].[Form]![Full name of person who agrees to the terms of the Conditions o1]=[name on form],[Check40]=-1,[Check40]=0)

    which is being put in the expression builder, but still nothing.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Read my comments in post 3
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Hi Colin

    Sorry about the late reply, I'm still struggling to determine where I should put the code - is the coding for an IF statement the same if I choose to create it in Macro builder/expression builder/code builder.

    When you say 'Me.ControlName syntax on both sides of the equals sign' do you mean like this:

    IIf([2017-18 data subform1].[Form]![Full name of person who agrees to the terms of the Conditions o1]=[name on form],Me.ControlName=-1,Me.ControlName=0)

    Apologies if my dunce cap is showing.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry but I'm now utterly confused ...
    Please confirm or correct the following:

    You have a form called ???? and a subform called 2017-18 data subform1
    On the subform, you have a textbox called
    Full name of person who agrees to the terms of the Conditions o1 and another called name on form
    You also have a checkbox called ???? - is it on the form or subform?

    When the Full name of person who agrees to the terms of the Conditions o1 = name on form, you want the checkbox to be True otherwise it should be False

    Also are you going to rename your fields & controls to something sensible? e.g. FullName


    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Sorry,

    I have a form called additional claim1 within it is a subform called 2017-18 data subform1

    on the subform is a textbox called Full name of person who agrees to the terms of the Conditions o1 and another called name on form

    there is (now) a checkbox called Cog signatory the same?



    when Full name of person who agrees to the terms of the Conditions o1 = name on form I want Cog signatory the same? to be ticked as True, otherwise it would be false

    Many thanksf or your ongoing patience.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Oh no the naming gets even worse !!!!!!

    Each time you use a space or special character such as '?' in your database, you have to enclose it in square brackets [] and the chances of making typo errors increases dramatically
    Its also MUCH HARDER to read the code when you have such long-winded names

    Assuming the checkbox is on the MAIN form, use a Form_Current event on the MAIN form
    1. Using Ranman's approach

    Code:
    Private Sub Form_Current()
    
        Me.[Cog signatory the same?]=Me.[2017-18 data subform1].[Form]![Full name of person who agrees to the terms of the Conditions_01]=Me.[2017-18 data subform1].[Form]![name on form]
    
    End Sub
    or with better naming, something like

    Code:
    Private Sub Form_Current()
    
        Me.chkSigSame=Me.fsubData.Form.txtFullNameTC=Me.fsubData.Form.txtFName
    
    End Sub
    The way these both work is that if the 2 textbox values are equal, the expression is true & the checkbox will be true (and vice versa)

    2. Or use If ... Then (NOT IIf) (again using the shortened versions)

    Code:
    Private Sub Form_Current()
    
        If Me.fsubData.Form.txtFullNameTC=Me.fsubData.Form.txtFName Then Me.chkSigSame=True
    
    End Sub
    3. If the checkbox is in the subform then use this in the subform Form_Current event

    Code:
    Private Sub Form_Current()
    
        Me.chkSigSame=Me.txtFullNameTC=Me.txtFName
    
    End Sub
    OR ....

    Code:
    Private Sub Form_Current()
    
        If Me.txtFullNameTC=Me.txtFName Then Me.chkSigSame=True
    
    End Sub
    Good luck with your project. I think you are going to need it!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Hi

    I've managed to get it to work,but only if the 'Name on Form' field has already been inputted, otherwise I am getting the following error:


    'Update or CancelUpdate without Addnew or Edit'

    the field 'Name on Form' is manual entry, so I wish users to enter a name into that field, for the macro to look at the subform field
    Full name of person who agrees to the terms of the Conditions_01

    and thentick the checkbox if they are the same. Does the macro need code to allow for manual entry?



    Last edited by Fostertrident; 02-13-2018 at 10:54 AM. Reason: Solved part of initial problem

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. Macro? The code is for VBA
    2. Which of the various code versions are you using - I gave you 5 options
    3. Is the code from the form or subform current event
    4. Is the checkbox in the form or subform?
    5. When does the error occur?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    Fostertrident is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    32
    Apologies I meant VBA

    I'm using the first solution you provided as the checkbox exists in the main form. I should also clarify that the 'name on form' field exists in the main form I hope I referenced it correctly :

    [Back]

    Private Sub Form_Current()

    Me.[Cog signatory the same?]=Me.[2017-18 data subform1].[Form]![Full name of person who agrees to the terms of the Conditions_01]=[name on form]

    End Sub

    The code exists in the form current event

    The checkbox exists in the form (not subform)

    The error occurs when I enter data into 'name on form' (existing in mainframe) and either tab out of it, or try and save the form, or try and enter data in another field. I.e it is inescapable.
    Thanks

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, it is possible for an expression in Checkbox ControlSource to reference a control or field in subform. Suggest you name the subform container control different from the object it holds, such as ctrDetails. Then expression like:

    =IIf(ctrDetails![Full name of person who agrees to the terms of the Conditions_01]=[name on form], TRUE, FALSE)

    Note I am not using the [Form] class reference. This would be the same in VBA. Even if you don't change name of the container control, this syntax should work.

    Saving calculated data (data dependent on other data) is usually a bad idea. Calculate this value when needed.

    Don't see need for VBA in this situation.
    Last edited by June7; 02-13-2018 at 07:22 PM.
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  2. expression builder
    By frustratedwithaccess in forum Access
    Replies: 7
    Last Post: 11-12-2014, 09:15 AM
  3. Replies: 1
    Last Post: 04-24-2013, 03:47 PM
  4. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  5. Expression builder
    By PJ_d_DJ in forum Access
    Replies: 2
    Last Post: 02-24-2011, 03:38 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