Requery Jumps to Top

[philbullock1223]

At a point within my code I need to requery a subform. When I do-so, the subform jumps to the top record. I have tried the below code to remedy the problem, but can't seem to figure out why it doesn't work.

ID = Forms![MainForm]![Subform]![ID] 'Get the ID the subform is currently on
Forms![MainForm]![Subform].Requery 'Requery the subform
Forms![MainForm]![Subform].SetFocus 'SetFocus to the subform
DoCmd.FindRecord ID 'Go to the record I was previously on

There is no error. The focused record litteraly jumps around to seeminly random records. The subform is based on an SQL query that has an OrderBy statement that could be giving it issue, but I don't know.

I can't figure it out. Any help would be greatly appreciated.

Phil

 

[MarkK]

Does this code make sense to you?

dim frm as access.form
dim id as long

set frm = Forms![MainForm]![Subform]
id = frm.ID
frm.Requery
with frm.Recordsetclone
  .findfirst "ID = " & id
  if not .nomatch then frm.bookmark = .bookmark
end with

The subform is a sort of ambiguous object to select with docmd. What happens in this code is that the recordset in the subform is directly manipulated so there is no ambiguity about where the 'find' operation is occuring.
I create a local reference 'frm' to the Subform.Form object to make the code more readable.
hth
Mark

 

[RainLover]

At a point within my code I need to requery a subform.

Try Refresh instead of Requery.

 

[philbullock1223]

I appreciate all the help, thanks.

I implemented the code you provided and somewhat understand it. I am getting an error:

Error 13: Type mismatch on line "Set frm = Forms![MainForm]![SubForm]"

Any reason why that would happen.

 

[MarkK]

Yeah, try Rainlover's advice first.

Forms![MainForm]![Subform].Refresh

 

[MarkK]

My mistake, amend this line...

Set frm = Forms!MainForm!Subform

...to...

Set frm = Forms!MainForm!Subform.Form

 

[philbullock1223]

Thanks a million! The altered code works great! I truly appreciate the help!

Phil

 

[RainLover]

Good to see you got it.

Suggest you search both Requery and Refresh in Access help to understand the difference.