Unanswered: Goto specific record on subform without filtering to a single record
I've scoured for the answer to this and come across recordset cloning and bookmarks which I don't think will work for me in this instance.
I have a database consisting of tblSurvey, tblQuestion, tblQuestionSub and tblAnswer to form the basis of a questionnaire/survey. I have tblCase and tblResponse for the completed questionnaire/survey returns.
When the database user enters the response, they input a unique autonumber AnswerKey which links back through to the sub-question, question, and survey.
When the AnswerKey is entered, I want to display a related record in a subform consisting of the Question and sub-question (sub question is zero length where not applicable) for that AnswerKey. The subform needs to maintain navigation to the other question and sub-question combinations for the particular survey as single forms.
I have retrieved the QuestionNumber and QuestionSubKey primary keys via a DLookup when the AnswerKey is entered and copied the values into unbound fields on the parent form. Now what I want to do is to use those values to display the record corresponding to the AnswerKey WITHOUT filtering to a single record so that the user still has the other Question and subquestion records relating to the survey to navigate through.
Could anyone suggest how to accomplish this? Many thanks for any assistance as usual folks.
It looked to me like the bookmark functionality involved cloning the parent recordset. The QuestionNumber and QuestionSubKey pk values I need aren't actually in the parent form recordset when inputting the survey responses.
When inputting the survey responses I have a 'Survey' form with a subform called 'Case' to represent a completed survey with a further nested subform 'Response'. Only the AnswerKey pk is actually entered to subform 'Response' therefore I have retrieved the related QuestionNumber and QuestionSubKey pk's from tables that are otherwise not involved at that point using DLookup.
The problematic subform which displays the related Question and sub-question data is also attached to the main 'Survey' form and shows all question and sub-question records for the visible survey initially as single forms. By copying the DLookup values to unbound form fields on the main form I've achieved a filter on the subform record source by referencing the unbound fields. However, it's not really what I would expect to see as a user and complicates things with the further need for an unfilter button.
You can clone any recordset. Here's an example of opening a different form and going to a record on that form. To do a subform, you'd just need to change the form reference, and perhaps in your case get the values from your DLookup: