none
How to set a checkbox value based on whether another field is blank or not RRS feed

  • Question

  • Hi,

    I'm trying to set the value of a checkbox to 0 (NO) if another field is blank, else set the checkbox value to -1 (Yes) if the field is populated.

    I've set up an after update event for the field called "VI_Picture_1_NotesMultiLineTextBox" as follows

    This ends up having no effect from my tests. Any suggestions?

    Thanks, Nathan.

    Tuesday, August 11, 2015 11:55 PM

Answers

  • Hi. Just another option... If you're not intending for the user to change the checkbox manually, then perhaps you could consider just using a Calculated Field to indicate a Yes or a No. Just a thought...
    Wednesday, August 12, 2015 1:17 AM
  • Hi Nathan. I don't have a web app to test this with right now; but hopefully, the following images will help:

    1. In table design view, add a calculated field and use the expression IsNull([OtherFieldNameHere])

    design view

    2. Here's what it should look like in normal (table) view:

    table view

    Please let us know if that doesn't work for you and maybe someone with access to a web app could give us a better solution.

    Good luck!

    Wednesday, August 12, 2015 2:44 AM

All replies

  • An update event trigger will not be activated if it is never changed - left blank.  You need to trigger off another object.

    Have it default to No and set to Yes on update if NULL or 'zero length' string.


    Build a little, test a little

    Wednesday, August 12, 2015 1:07 AM
  • Try using VBA:

    Private Sub VI_Picture_1_NotesMultiLineTextBox_AfterUpdate()
    Me.Visual_Inspection_YNYes_No = IsNull(Me.VI_Picture_1_NotesMultiLineTextBox) End Sub

    Just a word of advice. You really should consider reducing the names of your objects. Try to be descriptive, but brief. The names you have here are outrageously too long with redundant and unnecessary descriptors.

    Best practice for controls is as follows:

    • sfmClients - Sub form control
    • txtClientName - Text box
    • lbxAccounts - List box
    • cbxAccounts or cboAccounts - Combo box
    • chkStatus - Check box
    • optItem1 - Option button
    • grpItems - Option group
    • cmdOK - Command button
    • etc...

    So an example of your procedure with new control names follows:

    Private Sub txtVIPicture_AfterUpdate()
    
        Me.chkInspectionStatus = IsNull(Me.txtVIPicture)
    
    End Sub
    
    

    Wednesday, August 12, 2015 1:13 AM
  • Hi. Just another option... If you're not intending for the user to change the checkbox manually, then perhaps you could consider just using a Calculated Field to indicate a Yes or a No. Just a thought...
    Wednesday, August 12, 2015 1:17 AM
  • Hi. Just another option... If you're not intending for the user to change the checkbox manually, then perhaps you could consider just using a Calculated Field to indicate a Yes or a No. Just a thought...

    I agree with DB Guy, I can't imagine any reason you need to store additional information to indicate that the field is null.  Testing the field itself should be enough.
    Wednesday, August 12, 2015 1:26 AM
  • Hi Karl, I've amended the macro to be

    And it defaults to No. Is this what you were implying?

    Wednesday, August 12, 2015 2:11 AM
  • Thanks for the suggestion RunningManHD but as I am operating in the access web app I cannot create VBA code. I do understand about the names, I began working on this after they had been created and have not changed them.

    The DB guy, I'll give this a shot as the user has no need to alter the checkbox manually, it's just designed to be a visual indication on the main form as to whether other forms within the table have been completed. In terms of the calculation, would you be able to provide a sample?

    Thanks!

    • Edited by Nathan.DC Wednesday, August 12, 2015 2:16 AM
    Wednesday, August 12, 2015 2:12 AM
  • Hi Nathan. I don't have a web app to test this with right now; but hopefully, the following images will help:

    1. In table design view, add a calculated field and use the expression IsNull([OtherFieldNameHere])

    design view

    2. Here's what it should look like in normal (table) view:

    table view

    Please let us know if that doesn't work for you and maybe someone with access to a web app could give us a better solution.

    Good luck!

    Wednesday, August 12, 2015 2:44 AM
  • I intended that the field in the table default as 'No'.  

    I think 'the DB Guy' has the better idea unless you want to store the Yes/No but normally such is not stored but calculated in a query/form/report when needed.


    Build a little, test a little


    Wednesday, August 12, 2015 8:44 PM
  • ...as the user has no need to alter the checkbox manually, it's just designed to be a visual indication on the main form as to whether other forms within the table have been completed...

    Because of this, you may wish to consider using the On Current Event instead of the After Update.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Wednesday, August 12, 2015 9:00 PM