[RESOLVED] #Error message appear in textbox Amount

[KGComputers]

Hi!!!


I have a textbox in my subform called amount based on a query that computes for the amount...However when I type the product code in my subform, a #error will appear in the amount textbox..However when I press the tab key and enter the quantity ordered, the [#error] will be removed and the correct computation for the amount will be displayed on the textbox...


How do I make the #error message disappear? Even if the user has not typed any quantity ordered....

Im using ms access 2000/2003..

Thanks

[RobDog888]

Sounds like your missing some code in the product code textbox change event and you only have code in the OnExit event which is why it works?
Can you post your code?

[KGComputers]

Hi RobDog888,

I havent done any code yet....Actually, in the query OrderDetails Design View

Here is the formula to compute the amount:

Amount: CCur(Products.Product_Price*[Quantity_Ordered]*(1-[Discount])/100)*100

I assume that the amount variable will compute a null value from any of the variables involved...Since the user has not typed any quantity yet, I assume that Quantity, Product_Price, and Discount has null/zero values...

[RobDog888]

Correct. But instead of a nullstring value in the textbox it displays the #Error instead by design.

Where do you have the code? in which property or procedure.

[KGComputers]

Actually,

I have just started a little...

Here is the vba code for my subform.

Private Sub Product_ID_AfterUpdate()
    Form_ShortTermSalesDetailForm.Quantity_Ordered.Value = 1
End Sub
 
Private Sub Product_ID_BeforeUpdate(Cancel As Integer)
    Me.Quantity_Ordered.Value = 0
    Me.Amount.Visible = False
End Sub
 
Private Sub Product_ID_Change()
    Me.Amount.Value = 0
End Sub
 
Private Sub Quantity_Ordered_AfterUpdate()
    Me.Amount.Visible = True
End Sub

My problem is when I set the Default value of every textbox to zero, MS Access will generate an error since the fields cannot be updated...

[KGComputers]

Hi RobDog888,

I just figured it out....I just added a new field in my ShortTermSalesDetail Table called Product_Price...This will receive the value from the table products field called product price...I substituted the formula of my query

from this:

Amount: CCur(Products.Product_Price*[Quantity_Ordered]*(1-[Discount])/100)*100

to this:

Amount: CCur([ShortTermSalesDetail].[Product_Price]*[Quantity_Ordered]*(1-[Discount])/100)*100

The difference is that the first formula uses the price directly from the Products table while the second formula uses an ordinary field name that will receive the value from Products.Product_Price...

So I guess thats where the #error message came from...Since Products.Product_Price can't have the default value zero since is bound to the Products Table..

Then I put an ado code for ShortTermSalesDetail].[Product_Price to receive the value of Products.Product_Price.

Here is the code:

Do While rstitem.EOF <> True
       If itemcode = rstitem.Fields("Product_ID") Then
                            
       Me.ShortTermSalesDetail_Product_Price = rstitem.Fields("Product_Price")
                            
      End If
      rstitem.MoveNext
 Loop

By the way, are there any websites concerned with pure VBA development(codes,tutorials...and etc...? I am already using MSDN and functionx.com...Thanks in advance.....