Subform Saving data from another Subform in a Survey

[Kila]

I am at my wits end with this survey!!! Currently, I am trying to get my form to save data properly. My survey is set up such that each piece of data (Questions, Answers, Completed Surveys, Answers to Completed Surveys, etc.) each has its own table, and they are linked in Relationships by their Autonum ID #s. My problem is the form I am using to enter data in the Completed Survey table and the Survey Results table. This form is mainly just a link for the 2 subforms in it.

My form has a subform at the top that enters survey demographic data into a query that updates the Completed Surveys table and the Patients table...assigning a number to that particular survey and saving the demographic data on the patient.

The bottom half of the form has a subform that moves from one question to another (questions are in a query) using a "Next" button on the primary form. The answers for each question are selected in a dropdown box (another query) and are saved in the a query based on the results table, along with the Completed Survey ID from the top half of the form...or that is what is supposed to happen.

I have identified the problem (I think) by creating visable text boxes in different places so that I can see what the computer thinks the different ID#s are in different places. The Completed Survey ID in the top subform is copied to a global variable referenced by the bottom subform. The problem seems to be if I put the command to copy to the global variable what is in the Upper CompletedSurveyID text box under "Load" in VBA, it works (the lower subform has the correct CompletedSurveyID), but I get messages that a related record is required in the Completed Survey table, and the questions and answers no longer match up consistently. When I put the commands in "Current", the error messages go away, but the 2 subforms no longer match (the bottom subform holds the number from the first record in the table.

What seems to be happening is that the lower subform needs the upper subform to refresh to take the correct number, but for some reason when this happens I get errors.

Any thoughts?! I really need to get this functioning ASAP. My boss is leaning on me & I have been working on this for several weeks! I can pretty it up later...I just want it to WORK!

BTW...I am using Access 97. Thanks for your time!

 

[Kila]

I think we are close...

I tried it. The bottom form (the one that updates questions and answers when the "Next" button on the primary form is clicked) DOES have the Master and Child Forms linked by Question ID. However, when I attempt to do the same with the top subform, It gives the error message "Can't build a like between unbound forms". Any thoughts?

Thanks for your response!

 

[Kila]

I have now made a total mess of things! I have been trying to get this Master/Child thing to work, now even my questions (which WERE working) now come up at random. The question subform WAS linked to the primary form by QuestionID (common to the form and this subform only), and the Demographic subform SHOULD be linked by CompletedSurveyID (common to the form and this subform and the subform referenced above).

Now the questions are coming up at random, and the recordset in the demographic survey only has 1 record. It should have all of them. These things happen even when Master/Child seems to be as it should be.

PLEASE HELP! I am really in a time crunch and I seem to be going backwards!

 

[Kila]

I got into this mess b/c the recordset created by the form with the 2 linked forms was not updatable...I was trying to fix it. Also, I could not figure out where to put the command for the top subform to go to a new record. Every event I put it in errored out b/c the form was not open. I suppose subforms do not open traditionally.

 

[Kila]

I'm sorry. I don't think I explained myself properly. Here is the setup:

Main Form = frmSurvey
* Bound to qryCurrentSurvey, which pulls the questions related to the survey the user selects in a dialog box (at least THAT still works!)
* Each question has a related QuestionID

Top Subform (in header) = frmSurveyDemographics
* Bound to qryActiveSurvey, which has the demographic data and should save it into tblCompletedSurvey and tblPatients (these 2 tables are linked by PatientID)
* Should be linked to fmrSurvey by CompletedSurveyID (should generated here in an autoNum)
* To generate a new CompletedSurveyID, this form needs to go to a new record on opening.

Bottom Subform (in details) = frmSurveyAnswers
* Bound to qryActiveAnswers, which saves data to tblResults and contains QuestionID from tblQuestions
* Linked to frmSurvey (main form) by QuestionID
* Data saved to qryActiveAnswers SHOULD include CompletedSurveyID (from top subform) AND AnswerID (selected from a dropdown based on qrySurveyAnswers that pulls ONLY the possible answers related to the QuestionID)

Having reviewed this, does the main form still need to be what is currently the top form, possibly with the Questions as a subform, and the answers as a subform to the questions?

Thanks for your help.

 

[Kila]

BTW...qryCurrentSurvey ALSO has CompletedSurveyID, but only as a means to get the Master/Child thing to work. Looking at it now as I write to you, I can see that it is NOT updatable. I don't want to update it, but since referential integrety is enforced, that may be the problem.

I really don't need CompletedSurveyID in this query anyway. If I take it out, how can I get the Master/Child problem in frmSurvey to work?

 

[Kila]

OK...I got it working...not quite like you said b/c I had some trouble getting to work properly, but it works (clumsy maybe, but functional). Now I have a new problem that I think should be easy to resolve. My new setup is this:

frmSurvey...Main form
* Has 2 subforms...frmActiveSurvey and frmActiveAnswer
* Is based on qryActiveSurvey

frmActiveSurvey...subform to frmSurvey
* Is not linked to frmSurvey (couldn't figure out how to do this & still pull up a list of all surveys if not all have records related to them)
* Is based on tblSurveys
* Has 1 subform...frmActiveQuestion
* Has a combobox allowing you to select a survey

frmActiveQuestion...subform to frmActiveSurvey
* Is Based on tblQuestions and tblQuestionList (thru an SQL statement)
* Is linked to frmActiveSurvey by SurveyID
* Does not create new records. Only displays records (questions) related to the Survey selected in frmActiveSurvey

frmActiveAnswer...subform to frmSurvey
* Linked to frmSurvey by CompletedSurveyID
* Has a Combo box that displays the results of qrySurveyAnswers (answers related to subform frmActiveQuestion). When one is selected, it is saved in tblResults (using an SQL statement)

SO... My question is now, how do I get frmActiveAnswer to refresh the query and display a new record each time frmActiveQuestion goes to the next question? Sorry I didn't follow your instructions to a T...had trouble getting it to work if the survey in question was not already in the query results.

 

[Kila]

BTW...subform frmActiveAnswers IS based on a query/SQL that links Surveys, Questions, Answers, & Results.

 

[Kila]

OK...I got it working by putting

cboAnswerID.Requery

Under "BeforeInsert", but now you have to select the answer twice for it to save it. But wen I put this command anywhere else (On Click, On Current, etc.) it either does not work at al, or is a record behind the Questions form.

Also, I still need to find out how to go to a new record when the Questions Subform goes to next.

Any thoughts?

Thanks for your help!