Problem with requery statement

[crowegreg]

I've created an order entry database for a customer. Within one form, orders, their is a sub-form for the items. Within this sub-form, the following fields exist.
item
color
qty1
qty2
qty3

The tab order for the sub-form is in the order of the fields listed.
For each qty field, within the after update event their is a requery statement and set focus for the next qty field.

When I enter the first record in the sub-form, everything works correctly. When I enter the second record, after entering the qty1, pressing the tab key takes me to the item field for the first record. I've determined that the requery statement is causing the problem. If I remove the requery statement within the after update event for each qty field, everything works properly.

With the requery statement not being executed, the sub-total within the main form is not being re-calculated. What should I do to correct this problem?

Thanks in advance!!

[ranman256]

The requery will refresh the data as if you opened it for the 1st time and reset to the 1st object. So requery would not work for this.

Put a refresh button on the form to recalc all when done editing. (like web page shopping carts do)
(or just do the calculation on the 2 fields, rather than requery)

[burrina]

Try Me.Recalc. Or here is another suggestion.

This example assumes an AutoNumber field named "ID":

Private Sub cmdRequery_Click()
Dim varID as Variant 'To save the primary key


If Me.Dirty Then 'Save First
Me.Dirty = False
End If
varID = Me.ID


Me.Requery


If IsNull(varID) Then 'Must have been a new record
RunCommand acCmdRecordsGotoNew
Else
With Me.RecordsetClone
.FindFirst "ID = " & varID
If .NoMatch Then
'Do Something."
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub




If your primary key is a text field, change the appropriate line to:


..FindFirst "ID = ' " & varID & " ' "
note: spaces between ' and " in the above are there for clarity & should be
removed, leaving you with:


..FindFirst "ID = '" & varID & "'"

HTH