Requery only one record on a form

[mkallover]

I have a large form and at the top there is a subform (subMyProj) that displays a list of projects and their status. This form is not editable, it's used as an overview/navigation tool. Farther down on the form I have another subform (subMainInfo) where the user is allowed to change the status of the project using a combo box. The "On Change" event of that field has code to requery subMyProj when a user changes the status.



My issue is that when subMyProj is requeried it completely refreshes the form and takes the user back to the first listed record. What I'd like to happen is the Status of the selected project be changed but for the entire form to not be refreshed.

Does that make sense? Any ideas?

[ajetrumpet]

actually, the requery is necessary I think. this has been asked numerous times. what you can do though, is use something like the following to workaround that issue:

Dim I As Long
I = formname.CurrentRecord
formname.Requery
DoCmd.GoToRecord acDataForm, formname.Name, acGoTo, I

[mkallover]

Interesting. Thanks. I used your code but when called I am getting an "Object doesn't support this property or method error" and when I select Debug the line that begins "I = ..." is highlighted.

This code is on the On Change event of a combo box in frmMainInfo

Dim I As Long

I = Forms!frmMain!frmMainMyProj.CurrentRecord

DoCmd.RunCommand acCmdSaveRecord
Forms!frmMain!frmMainMyProj.Form.Requery

DoCmd.GoToRecord acDataForm, Forms!frmMain!frmMainMyProj, acGoTo, I

Is this problem that the user is clicking in another subform so there is no CurrentRecord in frmMainMyProj?

[ajetrumpet]

to be honest MK, the code is meant for one form only, but it can be adopted for anything. I apologize for not mentioning that.

subforms are so strange too. The best way to help you with this scenario is to look at what you've got and do it myself, because the possibilities are many with subforms.

things like setting focus to the sub first, then a control, then going to a new record...yada yada...it's sometimes a mess. if you want to upload, I'll have a look for you. It may take a while today though. I'm doing other things.