confused: I have a dilemia with a survey type of form I created. I took a look at
Duane Hookom's db and i followed his table layout by inlcuding 4 tables.
I am having a problem with my form called frmFim, I am not able to get my pre-made responses to display in my combo form and when it does display, I either, get all the responses for all questions or a null value.
What I am trying to do is a person is evaluated by 20 some questions and possible anwers are from 1-8, which I pre-defined in a tblTransferReponseList.
If someone can take a look at it and give me some advice, why this isn't working.
It is going to be difficult (read Impossible) to do what you want with your existing design. A "patient-survey-questions-answers" relationship is needed to track answers to questions for patients. I do not see where that exists with your design.
I reconfigured a copy to have these tables.
[Patient], Patient data no change from original
[Survey], Near the same as you had it
[SurveyQuestionList], QuestionID and Name associated to a SurveyID
[SurveyResponse], Index of: SurveyID, QuestionID, PatientID
[frmFim2] is the Survey Form with a Subform to show the Questions.
[frmResponses] is a subform with an event procedure to write the selected PatientID from the Main frmFim2 Form when an answer is Selected.
frmFim2 uses an 'On Current' event procedure to Requery the frmResponses when the Patient Record is changed.
[frmFimSurvey] has a combo box to select the Survey Name and Requeries the Sub form frmResponses when the combo name is changed.
You have a ton of other tables forms etc. in our database, i left most of the stuff in there but it is not used in this example. You'll still need to refine this to make it work within your database.
Bill, I did have an anwsers table: tblFimScoreResponses, this table would store the answers. tblTransferResonseList was the list of possible answers. I took a look at your attached file but it is the sameone I posted.
Requery, do you mean it would be.
Me![Rspns].LimitToList = Me!LmtLst
something like this
Ronbegone: Bill, I did have an anwsers table: tblFimScoreResponses, this table would store the answers.This table has only 3 columns (ResponseID, Response, QuestionID) I don't see how this can hold all the responses for multiple patients and Surveys?
Unless you have only one patient and one survey? There should be a relation to the PatientID and Survey-Response. If you use unique QuestionID's then you may get away without an association to the survey... The Table I added for this in the example was "tblSurveyResponse", see Questions_DB.mdb File. this used the fields (PatientID, SurveyID, QuestionID, Response)
Ronbegone: tblTransferResonseList was the list of possible answers.With the example I just created a list of values numbers 1-8 for the combo list. Still this is only an example. For you're working database you may want to incorporate this table to give answers lists based on the survey/question. Just having a Number for the answer is pretty abstract. A text field could be added give a more descript question. unless you have a checklist or some other way to know what number is what answer?
Ronbegone: I took a look at your attached file but it is the sameone I posted.I just DL'd my own file to check. Look carefully the zipped file Questions_DB.mdb contains the forms (frmFim2, frmResponses), it also contains the tables (tblSurveyResponse, tblSurveyQuestionList) which were not in your original mdb. You'll also see the Form frmFim2 works much smoother, least on my PC If you look at the code behind these forms you'll see how functionality was added to maintain the answers to questions in the list.
I may have missed the point of your original question, Not being able to load the pre-defined answers to question when your form opens? but with the current structure of your DB I do not see how it can work to track survey data. So pre-loading survey answers is not the most critical problem withthe DB. Having a table structure that supports your data requirements may be. Look at my example again. It should be of some value to you to understand how I related fields between tables to maintain survey/patient/questions.