Refreshing a Cross tab Query Form on Close of a Pop up Form

[tonygg]

Hi. Not sure if I am explaining it correctly but...



I have a cross tab query with the results in a datasheet view style form. It has simple conditional formatting on some columns. If I double click a field on the datasheet I get a new form that I can use to update data (directly in the DB) that is displayed in the cross tab datasheet. On close of that data entry form I want to see the updated data (no additions, just value changes) displayed in the crosstab query.

I have tried using the Me.Form.Requery on my main datasheet and made the other form (TONY PRODUCT PERMS FORM) modal:

Private Sub Product_Reference_DblClick(Cancel As Integer)
Dim ProductRefText As String
ProductRefText = Me.Product_Reference.Value
DoCmd.OpenForm "TONY PRODUCT PERMS FORM", OpenArgs:=ProductRefText
Me.Form.Requery
End Sub

This seems to refresh the sheet before I have updated the data. It also puts the curser back to the top of the datasheet rather than the row I double clicked on.

The form can be closed by either the x on Top RHS or custom cancel or update button s. Ideally I would like the refresh to occur only when I use the "update and close" button in the data entry form.

Any ideas to be able to do this and retain the position in the datasheet form would be gratefully received. Many Thanks

Any help on getting the refresh to happen

[pbaldy]

This should help:

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

Also, use the acDialog argument in the window mode and the code will wait for the popup form to be closed or hidden. Or you can perform the requery from the close event of the popup form.

[tonygg]

Thank you. I hadn't thought of putting it in the close event. That now refreshes correctly.

Still a bit stuck on getting back to the correct row in the cross tab datasheet form. I didn't really understand (sorry) you code with book marks.

I pass the unique value in the first column of the datasheet to the pop up form so I have that available on close. I was wondering if there a way of using it to put the cursor back to that row? Something like Excel Active.Row or Field (x,y) command?

The other thing I noticed is whilst I can set focus on a field in the pop up so the cursor is on it for data entry the mouse does not always move onto the form. Would you happen to know if there is a command to do that?

Thanks once again