How to reference a data field on a record in form view (datasheet) ?

[Paul7905]

I have a "subform" that is designed so as to show a table in datasheet view recordsource is a table). I have a field "QTY" in the table that has a data format as "number" with no default value (when the form is opened the field appears blank (as the users wish it to) ~ this field is a "work" field that the user keys a number into that is eventually cleared by an Update query when the User exits the form ~

I am trying to figure out how to reference this field as the user navigates to each record.

Let me tell you what I am trying to do.

I have a "text box" (defined "general number&quot that is visible on the "main" form which keeps a running total of the values keyed in the "QTY" field on the subform. To ensure that the running total is correct, I have to be able to ascertain what the value is in the "QTY" field should the user navigate "back" to it once it has been updated.

I decided to use the "Before Update" to place code to increment the running total "QTY" and this is working fine; I am able to reference the field as Me!QTY and add the quantity to the field on the main form by referencing it as follows "Forms!Main.Total" with no problem.

I am having trouble when the user updates a record, navigates to other records and updates them and then returns to a previously updated record and changes the QTY field to a value other than that initially entered.

To ascertain if the user entered a quantity in a record, updated it (the update is happening automatically when the user moves to another record apparantly) went to other records and then goes back to a recored that they previously updated, I need to capture the quantity in the QTY field in the record when they navigate BACK to it BEFORE they enter any changes in the field.

I figured I could do this during the GotFocus event for the field on the record in the subform ~ I coded as follows in the gotfocus event :

note: "QtyPrior" is a work field i have Dim'd that i will use to move the prior value so i can compare it to the new value they key if any and then compute the difference which I can add or subtract from the overall total (which i will grab in the BeforeUpdate event)

Private Sub Qty_GotFocus()
If Me!Qty = Null Then
QtyPrior = 0
ElseIf Me!Qty > Null Then
QtyPrior = Me!Qty
End If
End Sub


The problem i am having is that when moving to a new record, the

If Me!Qty = Null Then
QtyPrior = 0

statement is failing. in debug mode, i can see that the Me!Qty field is "null" when moving to a new record but the compare is failing, it falls right though to the Elseif statement. but, if i move to a record on which the user has previously keyed a QTY, then the code picks up the amount ok ~ Trouble is, the QTYPrior field holds the value in it when moving to a new record that has had nothing keyed into it previously and it will not initialize.

Basically, what i need to know is, how, on a form showing records in datasheet view, can I absolutely access specific data fields on a record when the user navigates to it (BEFORE) the user changes anything (i know how to do it after they key something and before they do the update using the BeforeUpdate event).

do i need to be using an index to the "current record" (where the arrow is on the left of the datasheet view)
or something like that ?

help is most appreciated ~

Thanks ~ whew ~ hope i explained this adequately

 

[Chopper]

Paul,

I have encountered something similar, and here is what worked for me:

I used the On_Enter event to set the existing value to a form-level variable (so it can be used by the Before_Update event) which it looks like you are doing with QtyPrior.
Also, instead of comparing the value to null, examine the length.

Private Sub Qty_On_Enter()
If len(Me!Qty) = 0 Then
QtyPrior = 0
ElseIf len(Me!Qty) <> 0 Then
QtyPrior = Me!Qty
End If
End Sub

Give it a try.

 

[Paul7905]

I struggled with all this for a while then discovered that there is a way to access the &quot;oldvalue&quot; in the &quot;before update&quot; logic. I just capture the old value in the field (checking it to see if it is &quot;null&quot; of course) ~ worked great ! thanks for the tip however