Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2007
    Posts
    47

    Unanswered: What is wrong with my code

    Hi,

    Can any one help me where I am going wrong in the below code. It doesn't work.

    Private Sub Sales_App_Value_AfterUpdate()
    Dim X As Integer
    X = Avg(comp1_AdjPrice + comp2_AdjPrice + comp3_AdjPrice + comp4_AdjPrice + comp5_AdjPrice + comp6_AdjPrice) / 6
    If Sales_App_Value > 2 * X / 3 And Sales_App_Value < X Then
    Value_Range = "upper 1/3"
    ElseIf Sales_App_Value < 2 * X / 3 And Sales_App_Value > X / 3 Then
    Value_Range = "middle 1/3"
    ElseIf Sales_App_Value > 0 And Sales_App_Value < X / 3 Then
    Value_Range = "lower 1/3"
    End If
    End Sub

    Private Sub form_current()
    Call Sales_App_Value_AfterUpdate
    End Sub


    Thanks,

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What is it not doing or doing that it shouldn't be doing????

    Why don't you substitute hard values for your fields and "run the numbers" to check that your logic is correct ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Can't tell what's "wrong" with your code, but...

    You should put that code in a function that you call from both the OnCurrent and AfterUpdate events, rather than calling the AfterUpdate from the OnCurrent.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jan 2007
    Posts
    47
    What I was trying to do is to make 'Value_Range' field auto compute the values when 'Sales_App_Value' is entered with an value.

    When I tried to give value to all the input fields, value_range field is showing blank which is supposed to showup with the computed value.

    "You should put that code in a function that you call from both the OnCurrent and AfterUpdate events, rather than calling the AfterUpdate from the OnCurrent."

    So you want me to write a seperate function and call the function in both OnCurrent and AfterUpdate events. Can I use the same above logic in this function.

  5. #5
    Join Date
    Jan 2007
    Posts
    47
    I upadated my code and it look like below now,

    Public Function value_range()
    Dim X As Double
    X = Avg(comp1_AdjPrice + comp2_AdjPrice + comp3_AdjPrice + comp4_AdjPrice + comp5_AdjPrice + comp6_AdjPrice) / 6#
    If Sales_App_Value > 2 * X / 3 And Sales_App_Value < X Then
    value_range = "upper 1/3"
    ElseIf Sales_App_Value < 2 * X / 3 And Sales_App_Value > X / 3 Then
    value_range = "middle 1/3"
    ElseIf Sales_App_Value > 0 And Sales_App_Value < X / 3 Then
    value_range = "lower 1/3"
    End If
    End Function

    Private Sub form_current()
    Call value_range
    End Sub

    Private Sub Sales_App_Value_AfterUpdate()
    Call value_range
    End Sub

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    My first guess is that none of your tests are evaluating to true.
    Put this in as the last part of your IF section

    Code:
    Else
      value_range="Nothing"
    End If
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jan 2007
    Posts
    47
    I tried putting else in my code but nothing shows up in value_range field.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Debugging 101

    Try stepping through your code to see if you can pinpoint the trouble spot.

    Is the function being called?
    Are the statements evaluating the way you expect?
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    X = Avg(comp1_AdjPrice + comp2_AdjPrice + comp3_AdjPrice + comp4_AdjPrice + comp5_AdjPrice + comp6_AdjPrice) / 6


    Avg then divide by six?
    And yes, you definately need an else statement - as mentioned above it seems that none of your statements are evaluating true - perhaps because of the above.

    Give it a shot
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Posts
    47
    I have modified my code and is as below. I still doesn't work. I am not sure whether my approach is my correct or not. Please advise me. Is there any way I can attach the screenshot of my form?

    Private Sub Sales_App_Value_AfterUpdate()
    Dim X As double
    X = (me.comp1_AdjPrice + me.comp2_AdjPrice + me.comp3_AdjPrice + me.comp4_AdjPrice + me.comp5_AdjPrice + me.comp6_AdjPrice) / 6.0
    If me.Sales_App_Value >= 2 * X / 3 And me.Sales_App_Value <= X Then
    me.Value_Range = "upper 1/3"
    ElseIf me.Sales_App_Value <= 2 * X / 3 And me.Sales_App_Value >= X / 3 Then
    me.Value_Range = "middle 1/3"
    ElseIf me.Sales_App_Value > 0 And me.Sales_App_Value <= X / 3 Then
    me.Value_Range = "lower 1/3"
    Else
    me.Value_Range = "Excedded value"
    end if
    End Sub

    Private Sub form_current()
    Call Sales_App_Value_AfterUpdate
    End Sub

  11. #11
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I was going to say that your problem was you are using "Me." instead of "Me!" when referring to the field values on your form, but then I noticed you didn't use Me syntax at all in your first post.

    For grins, try this

    Code:
    Public Function value_range_change()
    Dim X As Double
    'Put a break point on the next line 
    X = (comp1_AdjPrice + comp2_AdjPrice + comp3_AdjPrice + comp4_AdjPrice + comp5_AdjPrice + comp6_AdjPrice) / 6
    If Sales_App_Value > 2 * X / 3 And Sales_App_Value < X Then
    value_range = "upper 1/3"
    ElseIf Sales_App_Value < 2 * X / 3 And Sales_App_Value > X / 3 Then
    value_range = "middle 1/3"
    ElseIf Sales_App_Value > 0 And Sales_App_Value < X / 3 Then
    value_range = "lower 1/3"
    Else
    value_range="Exceeded Value"
    End If
    End Function
    
    Private Sub form_current()
     value_range_change
    End Sub
    
    Private Sub Sales_App_Value_AfterUpdate()
     value_range_change
    End Sub
    Make your change on the form, and your code window will pop up, with the
    X=....
    line highlighted. Hit F8 to step through, and see which line it's evaluating to.
    Inspiration Through Fermentation

  12. #12
    Join Date
    Jan 2007
    Posts
    47
    I appreciate all your help.

    I did put a break point and hit F8. But Nothing happens. I am not sure why.

    I ran the code in the front end and I am getting below error message.

    The Expression After Upadte you entered as the event property setting produced the following error:
    Procedure declaration doesn't match description of event or procedure having the same name.

    * The expression may not result in the name of a macro, the name of a user defined function, or event procedure.

    * There may have been error evaluating the function, event or macro

    But all the names in vba code match to those in the form. I don't know why it doesn't work. May be it is not possible make fields compute in the current form.

    Thanks,

  13. #13
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What do you mean by "Nothing happens"? Do you mean the code doesn't
    execute after you update the Sales_App_Value field?

    Make sure the Form's OnCurrent property and the Sales_App Value fields
    AfterUpdate property both say [Event Procedure]. If they do, click on the elipsis to the left of it, and make sure it goes into the code I've given you.
    Inspiration Through Fermentation

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Private Sub form_current()
     CALL value_range_change
    End Sub
    
    Private Sub Sales_App_Value_AfterUpdate()
     CALL value_range_change
    End Sub
    Is value_range a textbox?
    If so: value_range.value="Something or other"
    George
    Home | Blog

  15. #15
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Sorry, george. Call is not needed, and value_range.value is redundant.

    What we need from ujjwalla are clearer answer about what the problem is,
    other than "nothing happens".
    Inspiration Through Fermentation

Posting Permissions

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