Refreshing Subform Data

[swenger]

I have a Table which has [Cust_ID], [Qty] and [Price]
I have a Query of the above table with a an extra calculated field called [extended] which is [Qty] x [Price]
I created a SubForm based on this Query which lists all these fields for each row for each record of [Cust_ID] which is on my main Form.
I created another SubForm based on another Sum Query which totals the [extended] for each [Cust_ID]



My issue is that as I enter new lines in the Subform with the details, the Total SubForm is not calculating the new total. If I program a DoCmd.Requery this works, but then it brings the form back to the first record.

How can I have the Subform Total refresh and still stay on the same record.

Thanks,
Sam

[pbaldy]

One option:

http://www.baldyweb.com/Requery.htm

[swenger]

Thanks. worked perfectly!

[pbaldy]

Happy to help!

[swenger]

I copied this code to another form and changed the field on this form. Yet it goes to the first record. Any idea why. The only difference is that the first form that worked the Key field was a number and i declared lngPK2 as Long, but for this new form the Key Field was a short text so I declared lngPK as string. Do I need to change the code to reflect the string instead of a number variable.

Private Sub Test_Click()

lngPK = Me.RMA

On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If

Me.Requery
'return form to original record
With Me.RecordsetClone
.FindFirst "RMA = " & lngPK
If .NoMatch Then 'just in case another user deleted it in the interim
MsgBox "Record not found!", vbCritical
Else 'go to that record
Me.Bookmark = .Bookmark
End If

End With

End Sub

[pbaldy]

Try

.FindFirst "RMA = '" & lngPK & "'"

[swenger]

Didn't work

[pbaldy]

Can you attach the db here?

[swenger]

Open FRMAINfo and browse to the second or third record and click the save button and it will jump back to the first record.

Test.zip

[pbaldy]

Helps to use the actual field name.

.FindFirst "[RMA#] = '" & lngPK2 & "'"

You'll find symbols and spaces in your object names not worth the bother in the long run.

[swenger]

Strange because I had tried that. Even tried now again and still doesn't work. Did it actually work for you?

[swenger]

I realized that after I was further into it and felt it was too much to change the field names and then have to change it in the queries, forms and reports. I started using _ for spaces in my new ones.

[pbaldy]

Yes, the button worked once I changed that line. It would stay on whatever record I was on when I clicked it.

[swenger]

Can you repost your version of the file because I made the same changes and it's not working for me.

[pbaldy]

Here you go.