Code causes new record with unwanted zero

L

Laurence Lombard

I realise that it is not good database method to do calculations on a form
but I would like to try it anyway for the following reasons:
1) I am familiar with Excel and VBA therefore find this method more
understandable.
2) I would like to keep my database simple - ie a form based on a Table
only.
3) other reasons which would make this post long and unwieldy.

My problem is this:
The code below updates the amount if UnitPrice or Quantity is changed. When
I am in the last record of this Form and I exit UnitPrice (using tab or
Return) a new record is generated with a zero in Quantity. How can I avoid
this happening. (note how I used IsNull to avoid an error if Quantity is
empty)

Private Sub Quantity_Exit(Cancel As Integer)
RecalculateV2
End Sub

Private Sub UnitPrice_Exit(Cancel As Integer)
RecalculateV2
End Sub

Sub RecalculateV2()
If IsNull(Me!Quantity) Then TempQuantity = 0 Else TempQuantity = Me!Quantity
Me!Netto = Me!UnitPrice * TempQuantity
End Sub

Thanks
Laurence
 
N

Nikos Yannacopoulos

Laurence,

See answers in line.

HTH
Nikos

Laurence said:
I realise that it is not good database method to do calculations on a form
Click to expand...
Who said that? It's perfectly fine. What is advised against is storing
calculated values in tables.

but I would like to try it anyway for the following reasons:
1) I am familiar with Excel and VBA therefore find this method more
understandable.
2) I would like to keep my database simple - ie a form based on a Table
only.
3) other reasons which would make this post long and unwieldy.

My problem is this:
The code below updates the amount if UnitPrice or Quantity is changed. When
I am in the last record of this Form and I exit UnitPrice (using tab or
Return) a new record is generated with a zero in Quantity. How can I avoid
this happening. (note how I used IsNull to avoid an error if Quantity is
empty)
Click to expand...
There are two ways you can go, not sure which one you are after:

1. Change the tab/enter keys behavior so you go back to the first tab
stop in the same record, instead of moving to a new one, so no new
record inserted after the last one, or

2. Prevent the updating when in a new record, while there is no value in
the quantity control, but allowing it to run when a quantity is input.

For the first one, open the form in design view, display the form's
properties and change the Cycle property (tab Other) from default All
Records to Current record. Note: affects all records, not just the last one.

For the second one, see below.
Private Sub Quantity_Exit(Cancel As Integer)
RecalculateV2
End Sub

Private Sub UnitPrice_Exit(Cancel As Integer)
RecalculateV2
End Sub

Sub RecalculateV2()
If IsNull(Me!Quantity) Then TempQuantity = 0 Else TempQuantity = Me!Quantity
Me!Netto = Me!UnitPrice * TempQuantity
End Sub
Click to expand...
Add a line to the sub like:

Sub RecalculateV2()
If Me.NewRecord And IsNull(Me!Quantity) Then Exit Sub
If IsNull(Me!Quantity) Then TempQuantity = 0 Else TempQuantity = Me!Quantity
Me!Netto = Me!UnitPrice * TempQuantity
End Sub

(watch out for wrapping in your newsreader).
 
Top