How to stop a continous form from flipping records

[ManuelLavesa]

Hello All, I just have a problem with a continuous form. I made a form for order entry that uses a continuous form as a subform. The problem is that when I am entering records into the continuous form, it does not keep them on the way I enter them. Sometimes the second or third record is moved automatically to the top of the form. I don't know what is causing this flipping of records. Ideally I would like to keep the records the way I entered them so that it will print in the same order. Does any one know which property if any is causing this effect. It does not seem to be moving them in alphabetical order it is just random. Thank you for your help and ideas.

[CJ_London]

It is unlikely to be random - you probably have a sort set on the form or underlying query

[ManuelLavesa]

You are correct it was in the underlying query, the sort was for OrderID. The curious thing is that all the items have the same OrderID so I did not expect this behavior. Now I noticed another problem with the continuous form , I have a field named Discount, this field has a value of either 0 or 5 depending on the products in the order. The problem is that the value for all the products is changed to the value of the last product added to the order. Any ideas of what could be causing this error. As each product ordered should have its own discount value. thank you for your help

[CJ_London]

records are not stored in the table in the order they are input. They may fill up a space created by a deleted record for example.

You say a field named discount - do you mean this is an unbound control or is the control bound to this field?

[ManuelLavesa]

Well I will try to explain it better... The continuous form is based on a query that contains the following Fields: Product, Quantity, Price, Discount, and Subtotal, all these fields are bound to two tables. Discount is on the Orders table together with Price and Quantity. Product is on the product table. The subtotal is a calculated field within the query. The product is on a combobox and the AfterUpdate Event contains the following VBA

If Me.Parent.CustDiscount = 1 And Me.ProductDiscount = 1 Then
 Me.Discount = 5
 Else
 Me.Discount = 0
 End If
End Sub

This code is supposed to find out if the product and customer get the 5 % discount or not. When I order only one product then it works fine, but when there are many products in the continuous form then the last entry changes the value of Discount for all the products. So that if it is a discounted product then all the products get discount, and the reverse. I don't understand why it changes the values of the previous records within the same OrderID. It only happens to the Discount Field and not to the Price or Quantity. Thanks again for your help and ideas.

[CJ_London]

I would expect your form to have one table (orders) with the following fields/controls

OrderLinePK primary key for record, no control required
OrderFK - to link back to your order header (only field, no control required)
ProductFK - to link to product table - a combo box with as a minimum 3 columns - productPK (bound column), ProductDescription (visible column), ProductDiscount (hidden column)
Quantity
Price
Discount

this line

If Me.Parent.CustDiscount = 1 And Me.ProductDiscount = 1 Then

implies you do not have this arrangement. If you did it would be

If Me.Parent.CustDiscount = 1 And Me.ProductFK.Column(2) = 1 Then

[ManuelLavesa]

I did what you suggested and unfortunately the problem persists. I did the setup with the combobox and the three columns with the ProductDiscount on the 3 column. the discount is given to the correct customers and products. So this part works, the problem is when I enter another product which has no discount. It automatically erases the value 5 to 0. I don't know what is causing this error. Thanks for all your help so far.

[ManuelLavesa]

Hello Ajax, Thank you for your help... I solved the problem, I had the Field Discount on the wrong table and that is why it could only have only one value for the whole OrderID. And that was the last value added. Now it works great... Thanks again for making me think...