what is wrong with my code?

[taniadimitri]

I have the following code in aform,

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 = "exceeded value"
end if
End Sub

Private Sub form_current()
Call Sales_App_Value_AfterUpdate
End Sub

I was trying to make 'value_range' field auto compute based on 'sales_app_value'. With the above code no value is computed and my database is getting corrupted . I am not sure why this is happening. Please suggest if there are errors in my approach. Is at all possible to make this field auto compute.

I am also attaching the screen shot of my form.

Thanks,

 

[RuralGuy]

I would start by making your calculations a *separate* Sub Routine and then call it from both the AfterUpdate event of the control and the Current event of the form. It is entirely possible that *other* things might be taking place in events associated with controls that could corrupt your db. It is just a much better programming practice.

 

[RuralGuy]

Here's the same function in a little more readable form.

Private Sub RateValue()
Dim TheAverage As Double
Dim TopThird As Double
Dim BottomThird As Double

[COLOR="Green"]'-- Calculate the average of all of the fields[/COLOR]
TheAverage = (Me.comp1_AdjPrice + Me.comp2_AdjPrice + Me.comp3_AdjPrice + _
              Me.comp4_AdjPrice + Me.comp5_AdjPrice + Me.comp6_AdjPrice) / 6#

[COLOR="Green"]'-- Calculate the top 1/3 point[/COLOR]
TopThird = (TheAverage * 2) / 3        [COLOR="Green"]'-- The 2/3 point[/COLOR]
[COLOR="Green"]'-- Calculate the bottom 1/3 point[/COLOR]
BottomThird = TheAverage / 3           [COLOR="Green"]'-- The 1/3 point[/COLOR]

[COLOR="Green"]'-- Now categorize the actual value[/COLOR]
Select Case Me.Sales_App_Value

   Case TopThird To TheAverage
      Me.Value_Range = "upper 1/3"
   Case BottomThird To TopThird
      Me.Value_Range = "middle 1/3"
   Case 0 To BottomThird
      Me.Value_Range = "lower 1/3"
   Case Else
      Me.Value_Range = "exceeded value"
End Select

End Sub

Then you would Call RateValue from both the Current event of the form and the AfterUpdate event of the Sales_App_Value control. This is <<<AIR CODE>>> so there may be errors! I would single step the code to see what is going on.

 

[taniadimitri]

Thanks for all your help.

 

[RuralGuy]

Is your problem resolved?

 

[taniadimitri]

Hi,

My problem is not resolved yet. I thought it would be solved with the mofidied code you gave me but it didn't. Again I am kicked out of the database.

May be we can't make a field auto compute in the curent form. Do you have any other suggestions.

Thanks,

 

[RuralGuy]

Create a new/empty mdb and turn off Tools>OPtions...>General Tab> Name AutoCorrect. Turn ON the Compact on Close while you are on that tab. Then import everything from you old db and close the db. Open again and see if you still have the problem. The whole process I just described just takes a few seconds. If the problem is not corrected then you may need to post your db so we can look at it.

 

[taniadimitri]

Awesome! It perfectly works now. I have been going mad with this issue since long time. Do you know why this happened?

I have one more question. Earlier to see the backend database I hold the shift key and double click it. But for the new database I just created opens directly without holding shift key ( I am not sure why is this happening) and I also I don't see the front end of the new database similar to old database. Earlier I used to have a start form by default. How do I make a form default to the database.

Thanks for all your help again.

 

[RuralGuy]

Your db was more than likely corrupt and giving you fits. I believe your old code would have probably worked as well. It is just that what I provided is easier to read and maintain.
If you go to Tools>Startup...> you can select a Display Form/Page that will load when you run the db.

 

[taniadimitri]

Can I store the auto computed field "Value_Range" in a table and populate it in another form. Is this against to any rules?

 

[RuralGuy]

Just to keep from calculating it again? Why do you have it in more than one table? Can't you get the same results with a join in a query? I'm starting to suspect a problem with your table structures. If there is a problem then the further you get into your project, the more difficult the results are to achieve.