Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2008
    Posts
    18

    Unanswered: Is is possible to have a Bound Control Source and a formula?

    I'll try my best to explain my dilemma, or perhaps oversight…

    My database has a table (MasterTable) with multiple fields and a form. On the form, I have two check boxes and a text box. The form has MasterTable in it’s Control Source

    CheckBox1 has values of Y=1 N=0 and field Criteria1 for its Control Source
    CheckBox2 has values of Y=1 N=0 and field Criteria2 for its Control Source

    I need TextBox1 to show the total value of the two check boxes (0,1 or 2)

    Currently, I can only get the correct total by putting the following formula in the Control Source of the TextBox1:

    =(IIf([CheckBox1],1,0))+(IIf([CheckBox2],1,0))

    The same formula does not work in Default Value of TextBox1 properties.

    My problem is that because I have to use a formula in Control Source of the
    TextBox1, I can’t specify a field name from MasterTable. Thus I can’t write
    the value of TextBox1 to MasterTable like the Y/N values of CheckBox1/ Criteria1 and CheckBox2/ Criteria2

    Hopefully that makes sense...

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, if the 2 checkboxes are bound you probably shouldn't store the calculated value, since it can always be calculated from their values. That said, if you need to store it, you'd place that value in the textbox with code in the after update event of both checkboxes:

    Me.TextBoxName.Value = (IIf([CheckBox1],1,0))+(IIf([CheckBox2],1,0))
    Paul

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is is possible to have a Bound Control Source and a formula?
    No. It is either bound to a field or it is a formula.

    You can, however, have a bound control and set it to the result of a formula with event procedures (VBA). That does effectively the same thing.

    But why would you want to?? If it's a formula then it doesn't need to be stored, it can just be calculated each time.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2008
    Posts
    18
    Quote Originally Posted by StarTrekker
    No. It is either bound to a field or it is a formula.
    Yah, thats whats I suspected...

    Quote Originally Posted by StarTrekker
    You can, however, have a bound control and set it to the result of a formula with event procedures (VBA). That does effectively the same thing.
    Yay! That's the squirrel I need to chase! I would forever be endebted if you could throw me a hint on how i could do that... for that exact formula... =(IIf([CheckBox1],1,0))+(IIf([CheckBox2],1,0))

    So as the end-user checks or unchecks the boxes, the sum would automatically update...

    Quote Originally Posted by StarTrekker
    But why would you want to?? If it's a formula then it doesn't need to be stored, it can just be calculated each time.
    In actuality, this database is for evaluation purposes... the end-user actually interfaces with a bunch of checkboxes and comment fields... each eval is then added to the master tables, so having the totals will just make it easier to create any additional forms / reports at a later time...

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Was the first response invisible?

    Quote Originally Posted by pbaldy
    That said, if you need to store it, you'd place that value in the textbox with code in the after update event of both checkboxes:

    Me.TextBoxName.Value = (IIf([CheckBox1],1,0))+(IIf([CheckBox2],1,0))
    Paul

  6. #6
    Join Date
    Nov 2008
    Posts
    18
    Quote Originally Posted by pbaldy
    Was the first response invisible?
    I saw it but i didn't completely understand your answer... wouldn't that create a problem if I put the same --- Me.TextBoxName.Value = (IIf([CheckBox1],1,0))+(IIf([CheckBox2],1,0) --- formula in the AfterUpdate of both checkboxes?

    Or is duplicating that formula just meant to to cover all the possible combinations of check box updates? So if I click CheckBox1, the value of TextBox1 updates... then if I click CheckBox2, the value of TextBox1 updates again... and so on and so forth?

    I'm very much an Access beginner...

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No, it wouldn't create a problem. As you surmise, it simply ensures that the calculation updates when you change either checkbox. Anything more complicated I would create a function for, but this I would just duplicate.
    Paul

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd still recalculate it whenever needed rather than store the result of a formula in a field.

    And yes, Paul, your posts are invisible
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2008
    Posts
    18
    StarTrekker and pbaldy, thank you, thank you! Your advice worked like a charm!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •