Static Record Set

[dick_grayson]

I have a form based on a table with five fields: ID (text field set as the
primary key), Status (text combo field limited to two unique entries, either
Own or Sold), PurchasePrice (currency field), SalePrice (currency field), and
Net (currency field). I am relatively unfamiliar with Visual Basic, so as a
beginning exercise I am trying to write a function that simply takes the
difference between the PurchasePrice and SalePrice fields, constrained by the
values present in Status and SalePrice, and enters the resulting value in the
Net field. I have the following code saved as a module named Module1 that
contains the function:

Option Compare Database
Option Explicit

Public Function CalcNet(Purchase As Variant, Sale As Variant, Status As
Variant) As Currency

Dim Result As Currency

If InStr(1, Status, "O") = 1 Then
Result = 0
Else
If IsNull(Sale) Then
Result = 0
Else
Result = Sale - Purchase
End If
End If

CalcNet = Result
Exit Function

End Function


I then have the following code as part of the form in which these fields
reside:

Option Compare Database
Option Explicit

Private Sub Form_AfterUpdate()
NetProfit
End Sub

Private Sub Form_Current()
NetProfit
End Sub

Private Sub PurchasePrice_AfterUpdate()
NetProfit
End Sub

Private Sub SalePrice_AfterUpdate()
NetProfit
End Sub

Private Sub NetProfit()
Me!Net = CalcNet(Me!PurchasePrice, Me!SalePrice, Me!Status)
End Sub


The function calculates the results exactly as desired. However, the
problem I am having is that I cannot view any other records other than only
the first record when I open the form. The navigation bar at the bottom of
the form window shows that I am viewing record 1 of xx, but I cannot go to
any other record.

 

[Dirk Goldgar]

I have a form based on a table with five fields: ID (text field set
as the primary key), Status (text combo field limited to two unique
entries, either Own or Sold), PurchasePrice (currency field),
SalePrice (currency field), and Net (currency field). I am
relatively unfamiliar with Visual Basic, so as a beginning exercise I
am trying to write a function that simply takes the difference
between the PurchasePrice and SalePrice fields, constrained by the
values present in Status and SalePrice, and enters the resulting
value in the Net field. I have the following code saved as a module
named Module1 that contains the function:

Option Compare Database
Option Explicit

Public Function CalcNet(Purchase As Variant, Sale As Variant, Status
As Variant) As Currency

Dim Result As Currency

If InStr(1, Status, "O") = 1 Then
Result = 0
Else
If IsNull(Sale) Then
Result = 0
Else
Result = Sale - Purchase
End If
End If

CalcNet = Result
Exit Function

End Function


I then have the following code as part of the form in which these
fields reside:

Option Compare Database
Option Explicit

Private Sub Form_AfterUpdate()
NetProfit
End Sub

Private Sub Form_Current()
NetProfit
End Sub

Private Sub PurchasePrice_AfterUpdate()
NetProfit
End Sub

Private Sub SalePrice_AfterUpdate()
NetProfit
End Sub

Private Sub NetProfit()
Me!Net = CalcNet(Me!PurchasePrice, Me!SalePrice, Me!Status)
End Sub


The function calculates the results exactly as desired. However, the
problem I am having is that I cannot view any other records other
than only the first record when I open the form. The navigation bar
at the bottom of the form window shows that I am viewing record 1 of
xx, but I cannot go to any other record.

You are modifying the field [Net] in the form's AfterUpdate event. So
that means, immediately after you save the record, you dirty it again,
so it has to be saved before you can go to another record. But saving
the dirty record fires the AfterUpdate event, which dirties it again, so
it has to be saved, so the event fires ...

It would probably make sense to make this Net field of yours a
calculated field in the query or a calculated control on the form, so
it's never actually stored in the table. However, if you want to
calculate it and save it in the record, at the very least you need to
use the form's BeforeUpdate event instead of its AfterUpdate event.