Refresh Subform without losing place

[ejstefl]

Hello,

I have a subform that is based on a SQL statement that sums by Dollar Amount and groups by Account. I have it set up so the user can double-click on an account, which opens an unbound form. I then have the form execute a SQL INSERT INTO statement, which works beautifully.

The form opens as a pop-up, and I have it requery the subform when it closes. However, when the pop-up closes, I go back to the first record on the sub-form. I need it to "remember" the record I was on and take me back to that one. What is the standard procedure for doing this?

Thanks in advance!

Eric

 

[llkhoutx]

Keep an recordsource index key (e.g. Key)and after the refresh when you form reappears

dim db as dao.database
dim rs as dao.recordset
set rs = me.recordsetclone
rs.findfirst "Key=" & me!key
if rs.nomatch then
'error
end if
me.bookmark=rs.bookmark
rs.close
db.close
set rs=nothing
set db=nothing

 

[Spidey]

I have been struggling with this problem as well. There are several post that address this issue but they are all Code related solutions and I new to using Code in Access.

Here is what I was trying to accomplish.
I work for a delevoper. I had a table that tracked information about our lots. One piece of information is the orientation of the house and how the garage related to the house, ie... Front loaded, alley loaded, side loaded, etc... I also wanted a simple picture represention of the lot and a sample house on the lot. I created a table with a picture in it and a discription. I then created a subform with only the picture in it so I could display the picture chosen in the form. I then used a combo box to select the picture by discription, but stored the Picture KeyCode. I then just set up the subform to show the picture that was selected. The problem I was having was when I selected a new picture, the picture wasn't updating without me doing a requery. This was resetting the form back to Lot 1 everytime the requery was run. I then figured out that if I changed to a different record and then changed back that the image was updated.

The solution I used to avoid a requery was, in the combo box that choose the picture, I wrote a short and easy macro in the OnChange Event. The macro simply moved forward to the next record and then back to the previous record which was the record I started on. This refreshed the image and does it so fast you don't even realize it changed records. Hope this helps some of you if you don't want to mess with learning code. I am sure this only works with simple subforms and such but it might just be the answer you are looking for.