Continuous form problems

[Chrisopia]

I have a continuous form, (a sub form on a main form).

I run the following code on the form to simply work out the discount into two fields into the form, "Discount" and "CalcDisc"

Private Sub Form_Load()
Me.Discount = Nz(Me.Price * (Me.DiscPm / 100), 0)
Me.CalcDisc = Nz(Me.Price - Me.Discount, 0)
End Sub

One shows how much money is saved, and one shows the new total...

simple right?

wrong?

For some reason, and I blame the continuous form,
it only does this for the top line. Maybe its the load event?

for example:
Price Disc/Pm Discount CalcDisc
£10.00 10 £1.00 £9.00
£11.00 10
£12.00 10


It's really annoying!?

 

[Kiwiman]

Howzit

If your record source of your form is a query, you can have these two calculated fields in the query, and will be calculated for all records

 

[missinglinq]

And yes, code in the Form_Load event only affects the first record. In a Single View form you could place your code in the Form_Current event, but in a Continuous or Datasheet view form, Les' idea is on the money.

If your form is based on a query, create two calculated fields

Discount: Nz([Price] * ([DiscPm] / 100), 0)

and

CalcDisc: Nz([Price] - [Discount], 0)

Now, use these two calculated fields as the Control Sources for the two textboxes on your form, as you would any other field.

If you form is based on a table, create

1. A query, using all the fields in the table
2. Do the calculated fields, as instructed above
3. In Design View, change the form's Record Source from the table to the query

 

[Chrisopia]

Re: Continuous form problems - DSum problem...

By using that, it has worked, great stuff!

However I come across a problem for my next stage... adding them up to subtotal.

I have the boxes:
DiscountTotal and SubTotal
in my main form (the rest are in the sub form)

Now when I go to produce a DSum function, it requires a "domain"... which is usually the query or table name.

however I am using a query source made from a SQL function. For some reason the SQL code works, but a query doesn't.

How would I construct a DSum function with no domain?

 

[Kiwiman]

Howzit

Try this approach

• On your subform Header \ Footer - have a Text box that sums your Dsicount field (you can make this invisible)
• Give this control a name like txtDiscTotal
• On your main form have your text box for your discount total reference thh txtDiscTotal control on your subform - the control source

The control source of your Total Discount Control on the main form will look something like

=[yoursubformctrlname].Form!txtDiscTotal