Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2012
    Posts
    7

    Unanswered: Check Box update in Form

    Hi,

    Im very new to VB and I think I am going to need to write some code, so I wanted to see if anyone could help out with what im trying to do.

    I have a check box in a table ("increase") and when I check this box I need a few things to happen

    1. Increase Price field in the same table (+4)
    2. Increase the contract end year (+1)
    3. Uncheck a "option" check box in the same table

    But also need the ability to uncheck the "increase" and the fields to return to the original price and year. But this is not as important as getting it to update the first time.

    I have a from for this based on a query I created (I need to look at only some products and perform this task)

    Can anyone help give me some pointers on what expressions I will need and the structure in which to write it. I do not know VB, so any help would be appreciated.

    Thanks.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What is the Datatype of the Field that holds the 'contract end year,' and does it only hold the year, or is it a complete date?

    Linq ;0)>
    Last edited by Missinglinq; 06-17-12 at 23:23.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2012
    Posts
    7
    It is just the year ex. 2012 so I just need to +1

    Thanks

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The question still remains, hat is the Datatype of the Field that holds the 'contract end year? 2012 could be defined as Text or as a Number, and how you add 1 to it varies accordingly.

    Also note that using a Checkbox for this means that you can only add to or take away from these things once. If you wanted to add to the Price and Contract End Year more than once, you'd need to use another approach, such as using a Command Button.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jun 2012
    Posts
    7
    The contract end year is defined as a number.

    I only get one option on this, if I decide to take the option the next year the option is no longer available. So I think the check box can work.

    I did test using Y/N combo box, and that way would work too, but figured a check box would be easier on the user end.

    Thanks for your help

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by aharper View Post

    ...I only get one option on this, if I decide to take the option the next year the option is no longer available...
    Yes, in that case a Checkbox should do nicely!

    This code, in the 'Increase' Checkbox AfetrUpdate event in the Form's Code Module, should do the job, increasing Price and CYE and unticking the 'option' Checkbox, when the 'Increase' Checkbox is ticked, and reversing everything when it is unticked:
    Code:
    Private Sub IncreaseCheckbox_AfterUpdate()
     
     If Me.IncreaseCheckbox = -1 Then
      Me.Price = Me.Price + 4
      Me.[Contract Year End] = Me.[Contract Year End] + 1
      Me.OptionCheckbox = 0
     Else
      Me.Price = Me.Price - 4
      Me.[Contract Year End] = Me.[Contract Year End] - 1
      Me.OptionCheckbox = -1
     End If
     
    End Sub
    You'll have to substitute your actual names for the Controls where I used the names IncreaseCheckbox, Price, Contract Year End and OptionCheckbox.

    Also note, in the code, that I had to use Square Brackets around the name Contract Year End. This construct is necessitated by your use of Spaces in the name. Spaces, as well as Special characters, which is to say non-AlphaNumeric characters, really should be avoided in Access Field and Control names. Many Special Characters are used in VBA code to denote specific things, and their use in names tends to confuse the Access Gnomes! The Square Brackets, in this case, tells Access that Contract Year End is the name of a Control or Field. You would have been far better off to have named it ContractYearEnd or Contract_Year_End, where the Brackets would have been unnecessary.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jun 2012
    Posts
    7
    Wow that worked!! Thanks so much.

    PM sent on a side subject.

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got it working!

    Sorry, don't do PMs. Prefer everything to be done through the forum so that everyone can benefit!

    Good luck on your project!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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