GoToRecord in Subform

[MatMac]

I have a subform called "ProjectPAAFSubform", within a form called "Project"

ProjectPAAFSubform is in continuous form mode and comproses a list of questions. Depending on how the user answers these questions, new questions are appended to the recordset.

Say the user is halfway down the list and answers a question which yields a new question. My problem is that, after DoCmd.Requery and Refresh commands to make the new question visible in the form, the form is then reset to the top of the list, so the user is viewing the first question again. This is likely to cause the user to input the wrong data in the wrong question.

I have tried to use

DoCmd.GoToRecord acDataForm, "ProjectPAAFsubform", acGoTo, 4

where "4" is the record number of question, to take the user back to his/her starting point, but I get the error

"The object ProjectPAAFsubform is not open"

even though clearly it is.

Any ideas?

Thanks.

 

[dkinley]

Just swagging here .. but thinking it might be because it doesn't have the focus because I am thinking that when you refresh the focus goes back to the main form. Try something like ....

Me!subForm.SetFocus 
Me!subForm.Form!SomeControlNameYouWant.SetFocus 
DoCmd.GoToRecord , , acLast

You can substitute the last statement with what you have - I just wrote it like this since you might be adding a question at the end of the recordset.

-dK

 

[MatMac]

Thanks DK, but that doesn't seem to help.

Used...

[Forms]![Project]![ProjectPAAFsubform].SetFocus
[Forms]![Project]![ProjectPAAFsubform].Form![response].SetFocus
DoCmd.GoToRecord acDataForm, "ProjectPAAFsubform", acGoTo, 4

to set the focus to my subform and a control within it.

Still get the same error message :-s

M.

 

[dkinley]

Hmmm. Another swag here ....

Just for grins check the name of your subform. In the VBE, check the code behind the main form. Make sure that your form code starts with Option Compare Database and the next line is Option Explicit.

Next ... anywhere in the code area (again the main form) type in Me. (with period) to get the intellisense to pop up. Make sure the subform name you are using is in the list that pops up after you hit the period after Me.

The reason is sometimes what we think is the name isn't what is registered in the name of the subform properties - just wanting to make sure that is ruled out.

-dK

 

[MatMac]

Hi - appreciate your help.

Tried this - Yes my subform name - ProjectPAAFsubform - is listed.

Wat does the "Option Explicit" do?

Could the problem be something to do with using continuous form mode?

M.

 

[dkinley]

Nah - continuous or not - I know of a solution using a clone recordset but think there is a much easier solution, I just want to rule out the obvious first.

The Option Explicit forces variable definitions and makes things easier when variable usage gets complicated as well as ensure your variables are within scope.

I am going to put together something and test it here since your problem or my mind is more tangled than initially thought.

-dK

 

[MatMac]

Appreciate that. M

 

[dkinley]

Okay ... knew there was a way - I just haven't done something like this in a long time and it was tugging at me in the back of my head.

First, I am assuming that this code is in the AfterInsert() event of the subform. Switch your line to the following ...

DoCmd.GoToRecord acActiveDataObject, , acGoTo, 4

It's the "acActiveDataObject" that should do the trick.

-dK

 

[MatMac]

Fantastic - you're a genius! Well done and many thanks )

M.

 

[dkinley]

You betcha. I am relieved that it was a simple fix and within something I could handle and get you going.

Good luck!

-dK

EDIT: I do have to say. With the problem you posed and the implementation that you are going for is pretty slick in an 'adaptive' testing way. Not that you need or require my approval, but want to say I like the logic behind this and placing it in my 'in case I ever need' box. Thanks!