Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unbound/Bound Form Delima (A2K)

    Good Morning Everyone!

    I'm designing a survey database. The survey is mailed to members and returned to us. A member will get more than 1 survey (purpose is to track the quality of health) Once returned we will input the responses from the questions on the survey. The purpose is to determine if the members quality of health improved or not over the time between the 2 surveys.

    Each response has a numerical value to it. Each response varies...some Y/N, some T/F, etc.

    t_RAND_Responses (primary table) holds: "for each question/response there is a new record"
    pkRANDID (autonumber)
    pkQuestionID (numeric)
    pkDEMOID (numeric)
    Response (numeric)
    SurveyDate (short date)

    t_Questions table holds:
    pkQuestionID (autonumber)
    Question# (numeric)
    Question (text)

    ctRAND_Response (crosstab query)
    <pre>
    TRANSFORM Sum(t_RAND_Responses.Response) AS SumOfResponse
    SELECT t_RAND_Responses.pkQuestionID AS [Question#], t_RAND_Responses.pkDemoID AS ID,_
    Sum(t_RAND_Responses.Response) AS Evaluation
    FROM t_Questions INNER JOIN t_RAND_Responses ON t_Questions.pkQuestionID_
    = t_RAND_Responses.pkQuestionID
    GROUP BY t_RAND_Responses.pkQuestionID, t_RAND_Responses.pkDemoID
    PIVOT Format([SurveyDate],"Short Date");</pre>


    which of course is the recordsource for rSurveyEvaluation where a graph is included and each questions response value per survey is reviewed.

    My problem is:

    I have the input form for the survey which has ALL the questions and a combo box for each answer. Currently this form is UNBOUND because I don't know the BEST method to get the data to the tRAND. Keeping in mind that each question/answer will create a new record, my thought was to use an "Update to table" method. It would also have to include the SurveyDate. I just don't think that's the best approach and was hoping someone with far more vba skills than myself would point me in the right direction.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 30 Times in 30 Posts

    Re: Unbound/Bound Form Delima (A2K)

    Good afternoon,

    You could do it like this:

    Create a query based on t_RAND_Responses and t_Questions, joined on pkQuestionID. Sort order is on pkDEMOID and

    Create a continuous form based on this query. Make the text boxes bound to Question# and Question disabled and locked, and format them like labels. The user can only select a Response in each record.

    Create an unbound main form in which you can enter general info about the survey, such as an ID for the survey (pkDEMOID) and the survey data.
    This form has a command button. The On Click code of this button will use the ID and survey date, and combine these with t_Questions to create new records in t_RAND_Responses: one record for each question. Next, it opens the form mentioned above, with a where-condition to restrict the form to the ID for this survey.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> You haven't provided any feedback to replies to your recent questions. This means that other Loungers reading those threads don't know whether the replies were helpful. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound/Bound Form Delima (A2K)

    Sorry for taking so long to reply....my dog is very sick and had to go to the VET.

    I believe your suggestion is just what I was looking for.....I just couldn't think it through ..... I will give it a try this afternoon and reply back.

    As always Hans, thank you.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound/Bound Form Delima (A2K)

    OK Hans,
    I finally found the time to try your suggestion last night.

    I created a query with t_RAND_Responses and t_Questions (q_Responses), joining them by pkQuestionID (this is a 1-to-Many).

    I pulled ALL field from t_RAND_Responses and only Question# and Question from t_Questions. So far so good. Since I have test data in t_RAND_Responses, data was returned, as it should have.

    I created a continuous BOUND form using q_Responses. As expected, all data is returned. <font color=red>not sure I follow your suggestion to make all text boxes bound to Question# or for that matter the reason why</font color=red> but do understand the rest of that suggestion. And all is well.

    Next step. <font color=red>Create an UNBOUND form and base it on the survey, i.e. pkDemoID, Survey Date. </font color=red>OK...no problem.... here's where I'm totally lost: <font color=red>The On Click code of this button will use the ID and survey date, and combine these with t_Questions to create new records in t_RAND_Responses: one record for each question. Next, it opens the form mentioned above, with a where-condition to restrict the form to the ID for this survey</font color=red>

    If I open the BOUND form and attempt to add a new record, my question # and question's disappear and can't add a new record. So how can using the latter part of the 2nd suggestion help if a new record can't be added anyway. The method I chose to open the BOUND form using a combo box and bring in the Survey Date and pkDemoID and go to a new record works fine, but a new record can't be added. Therefore, I haven't been able to test the last part of your suggestion-- <font color=red>Next, it opens the form mentioned above, with a where-condition to restrict the form to the ID for this survey
    </font color=red>

    I'm very interested in your suggestion as your work is so genius and hope you could step me through this once more. However, I totally understand there are so many others that need assistance and believe I can still achieve my goal by using the .Add to each combo box for each question.

    Thanks again and apologize for the long delay in responding.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 30 Times in 30 Posts

    Sample survey Database (Access 2000+)

    I have adapted an existing database and attached it. It doesn't use exactly the same names as your database, but the resemblance should be close enough to understand it.
    Normally, the database window would be hidden and frmMain be displayed on startup, but I have reset the startup options to their default values.

    The On Click event code for cmdOK on frmMain creates a record in tblMain, then appends a series of records to tblResponse. Finally, frmSurvey is opened.

    Take a look at the design of the tables, query and forms. Post back if you have questions.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sample survey Database (Access 2000+)

    Hans, as always I learn so much from you and all the many people here and other boards. I appreciate the effort you made in helping me with this....but this time, I got it all by my wee little self....it's probably exactly as you described, just couldn't grasp it as you were describing it.

    Happy that I got it though and thank you once again.

    I'm going to post my solution, because I hope if I've done something goofy, someone will tell me.

    I have the following:
    pkQuestionID--(FK)--t_Questions
    pkDemoID--(FK)--t_DEMOgraphics
    pkRANDID--(PK)--t_RAND_Responses and (FK) in t_RAND_SURVEY

    I dropped pkDemoID and SurveyDate from t_RAND_Responses and included:
    pkQuestionID(FK)
    pkANDID(FK)
    ResponseValue (numeric)

    t_RAND_Survey:
    pkRANDID(PK)
    pkDemoID(FK)
    SurveyDate

    I created two Bound forms, (f_Survey-bound to t_RAND_Survey) and subform (f_Responses-bound to t_RAND_Responses) linking them by pkRANDID. In f_Responses, I made the question control a dropdown to capture all the questions (36 in all) and I already had my crosstab query and report and all is well.

    Thanks for your help.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 30 Times in 30 Posts

    Re: Sample survey Database (Access 2000+)

    Great! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •