Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2012
    Posts
    33

    Unanswered: why is my calculated control not updating?

    Hi everyone:

    if anyone could resolve this mystery for me, I would really appreciate it. i have tried for many hours to figure out why my unbound calculated control will not compute new anwers without closing the form and re-openning it.

    all i need it to do is determine the difference between two times: EndTime and StartTime. the form i am using once had an updatable query as its record source, but I decided to use controls on the form to accomplish the computations that I need. So, now the record sourse is a simple query.

    this is my primary problem

    file is attached.

    secondarily, i have a combobox on the form that will not show more items in its list except the first item. could someone explain what is wrong with it?

    thanks everyone!
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Problem #2 is simple; Your Combobox has the Query q01PickPackNShip as its RowSource, and q01PickPackNShip only has one Record in it, hence only one item is displayed in the dropdown.

    As to Problem #1, in the AfterUpdate events of both EndTime and StartTime,

    • Check that both EndTime and StartTime are populated, i.e. Not Null
    • Use the DateDiff() Function to determine the elapsed Time between the two
    • Assign this Value to the appropriate Unbound Textbox.

    Linq ;0)>
    Last edited by Missinglinq; 04-20-12 at 00:00.
    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
    Apr 2012
    Posts
    33

    Thanks Missinglinq

    Thanks Missinglinq

    With your help I was able to get my combox working pretty much the way I want it to.

    In using your suggestion for the solution to my primary problem I wrote the following code for my StartTime textbox's AfterUpdate event:

    'Compute values for the Hours textbox

    Private Sub StartTime_AfterUpdate(StartTimeValue As Long, EndTimeValue As Long, EndTime As TextBox,
    StartTime As TextBox, Hours As TextBox, HoursValue As Long, SolutionValue As Long)

    If IsNull(StartTime.Value) Then End Sub
    If IsNull(EndTime.Value) Then End Sub

    StartTimeValue = StartTime.Value
    EndTimeValue = EndTime.Value

    SolutionValue = DateDiff(n, EndTimeValue, StartTimeValue)
    Hours.Value = SolutionValue

    End Sub


    Now I am getting the following "warning"

    The expression After Update you entered as the event property setting produced the following error:

    Procedure declaration does not match description of event or procedure having the same name.


    One thing I did to try to make this all work was to put in zeros for the Default Value for the StartTime and EndTime textboxes (which didn't help tho).

    If anyone out there could give me some hints on how to make my code work or just explain what this warning actually means, I would really really appreciate it.

    Thanks much!

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

    ...code for my StartTime textbox's AfterUpdate event:

    Private Sub StartTime_AfterUpdate(StartTimeValue As Long, EndTimeValue As Long, EndTime As TextBox,
    StartTime As TextBox, Hours As TextBox, HoursValue As Long, SolutionValue As Long)
    The lines above, the Sub Header, is responsible for your error message. The AfterUpdate event of a Control is defined by Access and you cannot modify it by adding arguments, as you've done! You've constructed it as if it were a Function, with data to return, which it isn't. If you let Access generate the Sub, using the Code Builder, or used the dropdown boxes in the Code Module, you would have gotten

    Private Sub StartTime_AfterUpdate()


    and that's the way the Sub Header of an AfterUpdate event must be constructed!

    Quote Originally Posted by ShovelinFishHeads View Post

    StartTimeValue = StartTime.Value
    EndTimeValue = EndTime.Value

    SolutionValue = DateDiff(n, EndTimeValue, StartTimeValue)
    Hours.Value = SolutionValue
    You're making this more complicated than it need be by assigning Control Values to Variables before using the DateDiff() Function. If I've followed this correctly, and 'Hours' is the name of the Control that holds the result of the calculation, this sh0ould do the trick:

    Code:
    Private Sub StartTime_AfterUpdate()
    
      If IsNull(Me.StartTime.Value) Then End Sub
      If IsNull(Me.EndTime.Value) Then End Sub
    
      Me.Hours = DateDiff("n", Me.EndTime, Me.StartTime)
    
    End Sub
    Because you can never predict which Control a user will fill in first, this same code needs to go in the
    EndTime_AfterUpdate event, as well.

    Notice a couple of things:

    The Interval argument, in this case the n, has to be enclosed in Double Quotes, when used in the DateDiff() Function.

    Also, when working in a Form's Code Module, and referring to a Control, such as a Textbox or Combobox, you can do so by using the 'Me' Construct plus the Control's name. This tells Access that you're referring to a Control on the Form whose Code Module you're in.

    Also, since Value is the Default Property of Textboxes, Comboboxes and Listboxes, you can omit the .Value and Access understands that you're referring to the Control's Value Property.

    So since Hours, StartTime and EndTime are Controls on your Form, and Value is the Default Property of these Controls, you can refer to them as

    Me.Hours
    Me.StartTime
    Me.
    EndTime

    Hope this is all clear! Post back with questions or problems.

    Linq ;0)>
    Last edited by Missinglinq; 04-20-12 at 20:18.
    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
    Apr 2012
    Posts
    33

    errors?

    looks like I'm amost there.....

    as can probably be dudcued faily easily by now, most of my coding "skills" are in the Excel area.

    Missinglinq:

    when I used your last code sample, which I was certain would work without any troubles, I got two error messages. Access had heartburn with the "Then End Sub" statements that I had originally put into the code. I changed these to "Then End" and it looks like were all good there, I think.

    Second issue with heartburn is "Complile error: method or data member not found" with ".Hours =" highlighted.

    maybe you could suggest (or anyone else) what I need to do to get around this if your not too tired of helping with my issues.

    I'll be working on fixing it myself in the mean time.

    thanks.....much appreciate all the assistance

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. I think a more correct version of the code should use the Exit Sub instruction not the End instruction which will reset every variables in the program:
    Code:
    Private Sub StartTime_AfterUpdate()
    
      If IsNull(Me.StartTime.Value) Then Exit Sub
      If IsNull(Me.EndTime.Value) Then Exit Sub
    
      Me.Hours = DateDiff("n", Me.EndTime, Me.StartTime)
    
    End Sub
    2. Is there a control named Hours in the form?
    Have a nice day!

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I'm sorry, i missed the End Sub in your code and carried it over into my example, and as Sinndho said, it should be Exit Sub.

    And as Sinndho also said, do you actually have a Control named Hour? I said

    "If I've followed this correctly, and 'Hours' is the name of the Control that holds the result of the calculation..."

    assuming that if that was incorrect, you'd say so. That error message is the Access Gnomes' way of saying that they can't find a Control, on that Form, named Hour.

    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

  8. #8
    Join Date
    Apr 2012
    Posts
    33

    oh yeah

    thanks again for all the contributions to this thead guys

    when I looked at the error, i figured it had something to do with recognizing the name of the control, but I assumed that the name is correct.

    might just be "Hour"

    I'll have to check and get back later. can't check right now....

  9. #9
    Join Date
    Apr 2012
    Posts
    33

    Ok, I edited the code

    forgot that I had named the control "TimeDiff" so I edited the code and then used it for the StartTime and EndTime controls so that they both work to update the TimeDiff control.

    Then I realized that in doing this, I am getting the same calculated answer in every record on my form for TimeDiff instead of a unique value for each record that has a unique StartTime or EndTime value (because we are dealing with continuous records here). Sorry, I probably should have made that clear to begin with.

    Can we manipulate the code to accomplish this?

    If not, I guess I can always use a second query that calculates all of the values that I need and use the answers provided by that query to drive a subform that appears as a second line for each record that shows all of the calculated values. The more I think about it, the more this makes sense to me.

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

    ...forgot that I had named the control "TimeDiff" so I edited the code and then used it for the StartTime and EndTime controls so that they both work to update the TimeDiff control.
    Glad you got that part straight!
    Quote Originally Posted by ShovelinFishHeads View Post
    If not, I guess I can always use a second query that calculates
    You should be able to do the calculation in the Query that you said your Form was based on; you don't have to have a separate one for calculations. That's one advantage in basing Forms on Queries, even when only a single Table is involved.

    Or you can do the calculation in the Control Source for TimeDiff. The syntax is slightly different:

    =DateDiff("n",[EndTime],[StartTime])

    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

  11. #11
    Join Date
    Apr 2012
    Posts
    33
    Yeah exactly....

    after leaving my last post, I put some thought into it and realized I don't need to put calculated values into a table so an updatable query is not necessary (do I have that assumption right?). I'll put my calculations into the query i'm currently using (last set of about 9 columns) and put the calculated values in a second row of text boxes on the form that reads the query. And I will still have continuous records.

    Thanks again

  12. #12
    Join Date
    Apr 2012
    Posts
    33

    Locked combobox box?????

    something strange is happening. i had this form working just the way I want it to and then this is:

    my Employees combobox is giving me a 'Locked' error when i try to update the value in the bombobox in the second record in my form even though it is not locked and enabled. using continuous records. it works perfectly on the first record.

    i cant make any sense out of this.

    why is this happening?

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you lock (or change any other property) of a control on a continuous form (or on a form in Datasheet view), the modification affects all instances of the control (i.e. every "repetition" of the control on the form).
    Have a nice day!

  14. #14
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Continuing with Sinndho's line of thought, have you done anything to lock the Combobox after using it in the first Record?

    And this is a completely separate issue, and should be placed in a new thread with an appropriate title. You're much more to get help with this issue that way!

    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

  15. #15
    Join Date
    Apr 2012
    Posts
    33

    making new thread

    yeah i had planned to make a new thread for this; should be up in a few minutes.

Posting Permissions

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