Results 1 to 3 of 3
  1. #1
    Malvaro is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2008
    Posts
    1

    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
    jase_kross is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2009
    Location
    Orange, CA (USA)
    Posts
    7
    Hi Malvaro-

    One way you could retain MasterTable as control source for TextBox1 and capture the state of the checkboxes in the TextBox control is to use VBA code. Some sample code that you can try (perhaps with a little modification needed; should work if your textbox is named TextBox1, and your checkboxes are named CheckBox1 and CheckBox2) with your form is provided below. Fire up the Visual Basic Editor either through the menu/ribbon or Alt-F11 and place the code in the form module for your form.

    Hope this helps.

    Code:
    Private Sub CheckBox1_Click()
        CheckCounter
    End Sub
    
    Private Sub CheckBox2_Click()
        CheckCounter
    End Sub
    
    Sub CheckCounter()
       If CheckBox1.Value <> 0 And CheckBox2.Value <> 0 Then
            TextBox1.Value = 2
       ElseIf CheckBox1.Value = 0 And CheckBox2.Value = 0 Then
            TextBox1.Value = 0
       Else
            TextBox1.Value = 1
       End If
    
    End Sub

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Assuming that the two Criteria fields are numeric the solution is to bind your form to a *query* of the MasterTable rather than directly to the table. Then do your calculation in the query with a new field and bind the new field to the TextBox1 control if you want to display it. NewField: =Criteria1+Criteria2
    The calculation will update as you change the values of the two Criteria fields.

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

Similar Threads

  1. Rollback of data in a bound subform
    By mrk68 in forum Forms
    Replies: 3
    Last Post: 04-14-2009, 05:01 AM
  2. Control source and calculated values
    By meistersteff in forum Forms
    Replies: 0
    Last Post: 11-23-2007, 07:04 PM
  3. Sending control to specific control
    By wasim_sono in forum Programming
    Replies: 2
    Last Post: 04-19-2007, 08:19 AM
  4. Report will not print a bound textbox
    By vvrt in forum Reports
    Replies: 0
    Last Post: 03-16-2006, 02:16 PM
  5. inserting bound pictures
    By inquisitive in forum Access
    Replies: 1
    Last Post: 01-09-2006, 09:59 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