access multiple parent forms reusing the same subForm - weirdness happening with the data on the subform

Question

Hi. I cannot figure out why Access is doing what it's doing and could use your help.

I am trying to ask a bunch of questions in sequence and collect answers to each one. Think a simple web survey form. (and please don't tell me to use web tech. the client specified access)

the table is nothing special - a person id as a number, a question id as a number, an answer as text, and an answer as a number.

Now, there are two background drivers here. The client wants the flow of the questions to be configurable. The client wants a simple way to define the question text. So there is a table of form transitions defining the current form, which form comes next, and which form precedes this one. That's all good. There is a second table with a list of questions.

There are three types of question answers: a simple text response (answer in a textbox), a simple choice response (answer in option box), and a combination answer of text and choice.

So I created three templates as subforms, one for each question type. OnLoad the parent form, I look into the database, pull out the appropriate question, pull out any past responses, and push those values into the subform. The first time I hit each form, the subform displays correctly.

The problem occurs if I revisit a form. For example, I go to form 1 and see what I entered previously. I have a button to go to the next form and one for the prior form. These buttons do two things: DoCmd.close and DoCmd.OpenForm. I populate the OpenForm from the state table. So I hit the next button and go to form 2. I see there what I entered previously.

Now the fun begins....

If I hit the previous button (to form 1), I go to right containing (parent) form. But, the subform is off. I see the question id and text value from form 2. Worse, the database table with the answers changed. I watch all the events by putting a Debug.Print in each event on the template. I can't see where the database is changing. I put a break on the template's textbox OnChange, but the break isn't hit.

I thought when a form closes, the transaction is done for the subform and the parent. But this doesn't seem to be the case?? Any suggestions?? It's as if the subform is cached. Is there a way to flush the buffer and force a brand new instance of the subform. I'm speaking in a java sense as I'm still new to access vba.

Thanks.

Mike

Answer 1

The StackOverflow is similar. The Me. is the same as Forms("form name").Form.

Answer 2

Me.subformcontrolname.SourceObject = "name of subform"

I always create my subforms first before I embed them on a main form. So lets say you create a subform names sfrmQuestion. You then add a subform control to your main form and name the control sfrmNewQuestion. So the code would be:

Me.sfrmNewQuestion.SourceObject = "sfrmQuestion"

Answer 3

OK, your code looks OK, its looks like you are populating the controls on the subform in your code. Its not how I would do it, but I'm not sure why its causing the behavior you are experiencing.

The way to do a brand new instance is to reset the SourceObject.

Answer 4

Thanks, Scott. I'll try to give a code snippet to make what I'm saying clear. I'm not controlling the data through the SourceObject property.

If I don't put a myForm.requery or refresh() at the end, I get a Write Conflict message when I revisit the first form. If I put it in, I get the weird updating behavior.

the parent form onLoad() calls a sub who decorates the parent form and then calls this subroutine. The subform has no user defined event routines.

Public Sub SetUpSubForm(ByVal myForm As Form, ByVal subFormName As String, ByVal questionNumber As Long)

' because subforms load before the form, set all the particulars about the subform during the form load and \_ 

  then requery to set the saved values.  There are three steps.  First, set the question number. \_ 

  The personID should be set by the form-subform relationship.  Second, get any existing answers. \_ 

  If there's no response value, set a cue into the textbox.  Third, post the question text. 

Dim mySubForm As Object 

Dim priorAnswer As String 

'Set mySubForm = myForm.Controls(subFormName)   *- need a FQN so this isn't helpful* 

myForm.Controls(subFormName)!QuestionID = questionNumber 

priorAnswer = GetPriorAnswer(questionNumber, myForm.StudentID) 

' GetPriorAnswer is effectively DLookup(answer, answertable, where studentID & questionNumber)

If Helpers.NullOrEmptyString(priorAnswer) Then 

    myForm.Controls(subFormName)!Response.value = "Enter text here"   

' a prompt for the textbox where the user puts her answer - disappears with OnFocus

End If 

myForm.Controls(subFormName)!Response = priorAnswer 

myForm.Controls(subFormName)!questionLabel.Caption = DLookup("[QuestionText]", "[Tutor Application Questions]", \_ 

                                   "[ID]=" & questionNumber) 

myForm.Requery  <--- see not above

End Sub

Answer 5

Hi Mike, I'm an independent adviser and will try to help.

Its not easy to visualize what you are referring to, but I may be able to suggest some things. A subform is a control on a main form. One of the properties of a subform Control is the SourceObject property. This property specifies what form will be displayed in the subform control. Is that how you are changing the subform? If not, I would switch to that method.

Another point is there is a specific structure for a survey type database. The base structure looks like this:

tblQuestions: QuestionID (PK), Question, QuestionAnswerType

tblRespondent: RespondentID (PK), FirstName, LastName, other info about respondent

tblResponse: ResponseID (PK), RespondentID (FK), ResponseDate

tblAnswer: AnswerID (PK), QuestionID (FK), ResponseID (FK), Answer

It sounds like you may have something similar, but its not clear. The QuestonAnswerType would indicate Plain Text, Multiple Choice, etc. And it would dictate how you set up the form.

When I've done this in the paste, I use a single Answer form, but I hide the Controls that are not applicable to the QuestionAnswerType. Alternatively, you can use a specific Subform for the QuestionAnswerType and just change the SourceObject. of the subform control.

You might require additional fields which can control question flow, etc.

If you need any further clarification on this please feel free to ask.