Display data after leaving field

[RenaG]

Hi~ I am a newbie to Access (started about 3 wks ago). The project I am working on is to store data from surveys. I have 4 tables:
· tblQuestions (the survey questions);
· tblAnswers (the possible answers to the questions);
· tblIndividualSurvey (stores date, location, and comments for each survey we receive);
· tblResponse (the answers given by the participant).

The form that we use to enter the survey responses has a main form for the data that goes into the tblIndividualSurvey and a subform for the data that goes into the tblReponse. The subform consists of two controls: 1) question field 2) answer field, both are combo boxes. The question field Row Source is:
SELECT tblQuestions.QID,
[QNumber] & " " & [Question] AS Expr1
FROM tblQuestions

The answer field Row Source is:
SELECT tblAnswers.AID,
tblAnswers.Answer,
tblAnswers.QID
FROM tblAnswers
WHERE (((tblAnswers.QID)=[Forms]![frmIndividualSurvey]![sfrmResults].[Form]![QID]));

The question field has the following code on the On Focus and On Exit events:
Private Sub …
Me.answerID.Requery
End Sub

When we key in the question number, it displays the question in the question field. When we tab across to the answer field, the possible answers are available and we can select the available answer. The selected answer updates properly in the tblResponse table.

Here’s the problem – after we tab out of the answer field into the next question, the answer to the previous question remains on the screen (just like we want it to). But when we enter the next question, the answer to the previous question no longer displays. If we click on a previous question, the associated answer displays.

Here’s my question - the questions continue to be displayed even when we aren’t on that field any more. How do I keep the value for each answer displaying on the screen?

TIA!

 

[CBrighton]

It sounds to me like you need to change the subform from a single form to a continuous form. This would show all records in the table bound to the subform with the oldest first (i.e. add new question & responses to the bottom of the subform).

 

[RenaG]

Good morning CBrighton ~ the subform is already a continuous form. It grows as I tab through the fields. The values entered for the questions continue to be displayed but the answers are only displayed when I am on the question associated with that answer. I would like for all the answers to be displayed. I hope that makes sense.

 

[CBrighton]

OK.

Is it as soon as you tab / click into the new record that the answer for the previous becomes invisible?

I'm thiking it's likely caused by events on the subform, most likely (sub)form open / load / current or the event which updates the rowsource of the answer combobox based on the question combobox, or the after update / change event of the answer combobox.

Can you copy the VBA behind that form & paste it into here (please use code tags too)? Confirmation of the control names would be useful too.

Otherwise you could upload a 2003 version of the database and I'll have a look.

 

[RenaG]

I am working in 2007 but saved it as 2003. I think it is probably easier to send you the whole thing instead of bits and pieces.

 

[RenaG]

BTW - we are not using the "Load Tables" button on the frmIndividualSurvey. That was to preload the tblResponse with the questions of the survey but we are just tabbing through the form adding the questions one at a time.

 

[CBrighton]

Well, the cause of the disappearing answers is that the combobox is set to limit to list, and as soon as the second combobox is requeried the revious answer is not in the list so it is hidden.

To turn off limit to list you must have the first column visible and that first column must be the bound value.

It may end up being easier to un-bind the combobox and add a hidden textbox. that way the combobox can have limit to list off & can display the response to the user, but the change event could send the AID value to the hidden textbox which is bound to AID in tblResponse.

 

[RenaG]

To turn off limit to list you must have the first column visible and that first column must be the bound value..

Sorry, I am not fully comprehending what you are saying (remember I am a newbie; you need to speak v-e-r-y s-l-o-w-l-y ).

What are you calling "column"? Is it the first field on the form (ie questionID) or something else? Could you restate the above with more detail? (on a side note: if I turn off limit to list then that would allow the data entry clerk to enter a value that is not in the list, right?)

It may end up being easier to un-bind the combobox and add a hidden textbox. that way the combobox can have limit to list off & can display the response to the user, but the change event could send the AID value to the hidden textbox which is bound to AID in tblResponse.

Did you mean "limit to list on"?

Thanks so much for your time in working through this with me.

 

[CBrighton]

Right click the combobox and view the control properties.

In the Data tab, below the row source property, is one called limit to list. This property defines whether users can input anything they like or whether they are limited to the options on the list.

If you try changing it from yes to no Access will give you an error message (at least, Access 2003 does). The error message will explain that because your bound column (another property in the Data tab) is set to 1 but your column widths (in the first tab, Format) has the first column as 0cm width.


What I am suggesting is deleting the field name in the Control Source property (Data tab), changing the row source so that AID is no longer the first column (for my example I'll use the order Answer;AID;QID, which means I swapped Answer with AID).

You can then create a textbox and set the hidden property (Format tab) to true and set the control source (Data tab) to AID.

Once you have this you just need to create an after update event on the answer combobox which does something like this:

txtAnswerID = AnswerID.column(2)

This will mean that the answer combobox is not limited to list, so all your answers will display. When you select an answer the ID behind that answer is sent to the textbox which is bound to a field in the table.

 

[RenaG]

Thank you so much for breaking it down for me. Here are the changes I made based in my understanding of what you sent. I am also including the db itself (with the changes) in case you want to look at it again.

If you try changing it from yes to no Access will give you an error message (at least, Access 2003 does). The error message will explain that because your bound column (another property in the Data tab) is set to 1 but your column widths (in the first tab, Format) has the first column as 0cm width.

Access 2007 gives the same error message.

answerID Format | Column Widths = 3” (removed the 0” for the first column)
answerID Data | Limit to List =No

What I am suggesting is deleting the field name in the Control Source property (Data tab), changing the row source so that AID is no longer the first column (for my example I'll use the order Answer;AID;QID, which means I swapped Answer with AID).

answerID Data | Control Source = blank
answerID Data | Row Source - swapped tblAnswers.Answer and tblAnswers.AID

You can then create a textbox and set the hidden property (Format tab) to true and set the control source (Data tab) to AID.

Added a text box to the form (Text9)
Text9 Other | Name = AIDTxtbx
AIDTxtbx Format | Visible = No
AIDTxtbx Data | Control Source = AID

Once you have this you just need to create an after update event on the answer combobox which does something like this:

txtAnswerID = AnswerID.column(2)

answerID Event | After Update = AIDTxtbx = answerID.Column(2)


What happens now is that when an answer is changed, every answer that is showing will display the value of the new answer. And the data in the tblResponse table is corrupted. Evidentally I didn't understand something correctly. Can you see what I did wrong?

 

[RenaG]

Got one thing fixed. I figured out that I was referencing the wrong column by saying column(2) when it should be column(1) (that 0 = 1 thing bit me again) so now the data in the table looks correct. Whew!!

 

[CBrighton]

I had a look at doing it myself and found similar issues.

Dynamically assigning the rowsource of a combobox on a continuous form may be more complex than I thought!

 

[RenaG]

Could you explain why the AIDTxtbx always displays the correct value? If I understood that then maybe I could tap into it some how.

I have been playing with the DLookUp function but I get the same result - everytime the value in answerID changes all previous answerID fields in the continuous form changes. But the AIDTxtbx remains correct!

If I just knew how to say:

Look at the AIDTxtbx field and display the "Answer" associated with it.
​

It seems like that would work.

 

[CBrighton]

I think you will end up finding that the rowsource of a combobox on a continuous form must be the same for each record.

The data is recor5ding fine (as you know), but it won't show each combobox as having a unique rowsource.

 

[RenaG]

I got it to work!!!

On the AnswerTxt field, I added to the Control Source this:

=DLookUp("[Answer]","[tblAnswers]","[AID]=[AIDTxtbx]")
​

I hid the AIDTxtbx field; removed the tab stop for AnswerTxt and shortened the length of the answerID field.

I think that is as close as I am going to get. I don't really like the way the form looks but I can live with it.

Thanks for all your help!! I have certainly learned a lot from your input.