Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple answers in data entry form (A2K)

    Hi,
    I've attached two of the tables from my new db, tblquestions and tblresponselist. As you can see, each question has a variety of possible answers. In some questions, there's the possibility of having up to 11 answers.

    I'm unsure as to how to design a form to make this work. I've looked over some survey database samples, but there is much SQL for which I am not very familiar with.

    Could anyone give me a jumpstart with what to do next? Should I create a query where the answers will be stored and base the form on that? I think I've searched through all the threads on this forum, and I'm not sure how to get started.

    Thanks, Sarah
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Hi Sarah,

    I have had a quick look at your structure and suggest you need to review how you are dealing with the data. Firstly identify those questions where only 1 reponse is valid. These should be the easiest to handle as you will only need one field to capture the data response.

    Those questions where you need to capture more than one response can be handled in a couple of ways, either allow for x (x being the number of fields you need) for storage. The second method is not that easy it you are not that familiar with developing as it involves using a binary and testing the values using VB.

    I've attached your db with a couple of new tables and a form as an example of how you can configure to capture your responses

    Cheers
    Tony
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Sarah,

    I'm not sure I fully understand what your trying to do. Does each question have a set of pre-defined possible responses? Or are responses "open-ended" and merely keyed into the tblresponselist table? You seem to imply that the answers are pre-defined, and the respondee chooses one (or more?) of the responses from a varying length list directly on an Access form. Is that the goal?

    Assuming that is the case, I would setup the form with a label-box into which I could copy the question via its caption property. Be sure to make the box large enough to hold your longest question. Below the question box, I would setup multiple checkbox items, one above the other. The number of items would be the maximum number of possible responses. Label them "chkResponse1" thru "chkResponseX" where X is the highest response number (11?). Label the checkbox labels as "lblResponse1" thru "lblResponseX" in like manner.

    Then, reading your database, get all possible responses for any given question, copying the question into the question label caption. Copy the responses, starting with "1" into the "lblResponse1" caption field for the total number of possible responses. For all response checkboxes, and their labels, that are not used for the current question, set their visible property to False. If a single response is allowed for each question, you can set the Click event on the checkbox to respond to the choice. If multiple responses are allowed per question, then ignore the checkbox click, but set up a separate command button labeled "Record Response" and use its Click event to scan the checkboxes for selected responses.

    Obviously, some details have been omitted, but I hope you get the general idea.

    -- Jim

  4. #4
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Thanks Jim for your response,

    There are pre-defined responses...and maybe calling it a survey database misleads one into thinking it's something different than what it is, but (for example) some questions could be:

    26-01 Using a scale between 1 and 5 (with 1 being very unclean and 5 being very clean), how would you rate the cleanliness of the park? --surveyor would then choose a value between 1 and 5--
    26-02 Are there people in the park? --surveyor would select yes or no--
    26-03 If there are people in the park, how many do you see in your target area? --surveyor would write a unique answer--
    26-04 What are the people doing --surveyor would select from a predefined list--

    So, I think I understand your form formatting but I'm not at work today to have everything in front of me so I'm not able to try out your style. Tomorrow afternoon I'll be back at it, but I do have one question. I did state I could have up to 11 possible answers but that's really for one question. If I were to put all of the possible answers as checkboxes and set the ones I didn't need to visible property = False, there would be hundreds hidden at one time...I might be misunderstanding your layout, but I am not sure how I would make that work.

    Thank you! Sarah

  5. #5
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Tony,
    Like I said to Jim, I am in class and not working today, but I appreciate you making that form.

    If each question only had one possible response, that would be within my skills to do pretty easily, but I'm interested in figuring out how to make this work for the multiple responses (and I am trying not to think about the fact that some responses will require another input...but first things first).

    Do you have any examples, or know where I could find some, of using a binary or example VB that would test particular values.

    Thank you!
    Sarah

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Hi Sarah

    I've put together the attached example of a binary field being used to capture multiple checkbox response/s. There is a form and a query showing how you can then retrieve the results etc. With the form you need to format the controls each time they get the focus. Remember that what you are doing is using 1 field to control x number of tick boxes etc.

    A word of warning too, make sure you understand logically what this is doing and secondly that if you use this method then ensure that you test the result of each field to ensure that is capturing the data correctly. If you get the binary calc wrong the entire question results become invalid. (I inherited this problem recently... not nice and it required all the data to be recaptured)

    Also I'd suggest giving some consideration to after you capture this information what you need to do in the way of reports etc, as once you have it in this format you may need to do what to it? & can you achieve this easily etc... It can take more work to manipulate at times using binary fields depending on the desired outcome.

    All the best
    Cheers
    Tony
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Sarah,

    I am attaching an example of my earlier suggestion. With the checkbox responses, it would be very easy to record multiple responses with the form logic I presented, with a few minor changes. If single response is desired, I think I would change the checkboxes to radio-buttons, and only allow one to be set at a time.

    Sounds like Tony has presented another option, so you now have a couple of choices on how to proceed. Have fun.

    -- Jim
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Wow thanks for that example Tony. At this point, I don't really quite understand it's logic, but I would like to! Any advice on where to read up on binary fields and calculations?

    Thanks, Sarah

  9. #9
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Jim,
    When the Survey form is closed this error appears:
    Run-Time Error 3704: Operation is not allowed when the object is closed

    Any ideas? I really appreciate your advice. If you have a minute, do you think you could maybe do a quick walk through of some of your code? I'm not really that familiar with it, particularly as you are setting recordsets and the like.

    Thanks,
    Sarah

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: multiple answers in data entry form (A2K)

    >>26-01 Using a scale between 1 and 5 (with 1 being very unclean and 5 being very clean), how would you rate the cleanliness of the park? --surveyor would then choose a value between 1 and 5--
    26-02 Are there people in the park? --surveyor would select yes or no--
    26-03 If there are people in the park, how many do you see in your target area? --surveyor would write a unique answer--
    26-04 What are the people doing --surveyor would select from a predefined list-->>

    Are these the only possibilities that you require.

    What you could do is to identify the response to each question and make that the only one visible when the user needs to answer a question, ie. if a combobox is required then you would make the combobox only visible for the response for that question. If you went this way you would need a single form type of form as you could not do it with a Continuous Forms form. If you went this way you would need a table identifying each question with one of the options that you define above.

  11. #11
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Sarah,

    Sorry about the little bug... I was trying to close a recordset twice!

    Attached is a corrected, and slightly expanded version. The Accept button code now shows the way to either accept one answer, or multiple answers per question. I also moved the Accept button to the top of the form, and allowed vertical scrolling so you may resize the form vertically and scroll as needed.

    As to the walk-thru:
    --- Sub Form_Load:
    1. Defines ADO Recordsets for the Questions, Responses and Answers tables.
    2. Opens the Questions table R/O and positions to the first question.
    3. Opens the Answers table R/W and, if prior answers exist, positions to the end of the table.
    4. Calls the poseQuestion subroutine to setup the Question and Response controls on the screen.
    --- Sub Form_Close:
    1. Closes the Questions and Answers table recordsets.
    2. Releases ADO resources for all three recordsets.
    3. Quits Access.
    --- Sub poseQuestion:
    1. Extracts the QuestionID key from the current Question recordset row.
    2. Opens the Responses table recordset, filtering by the QuestionID value.
    3. Determines the number of responses by the resultant recordcount.
    4. Loads the question text into the question label caption field.
    5. Initializes all the possible response fields on the form to invisible.
    6. Reads each possible response and loads it into a respective response caption field for the checkbox.
    7. As a response is loaded to a caption field, the checkbox and caption field are made visible.
    8. When all responses have been loaded, the response recordset is closed, and the question recordset is advanced to the next row.
    --Sub cmdAccept_Click:
    1. I'll leave this as an exercise for your spare time -- I hope there are sufficient comments in and around the code.

    If you still have questions, feel free to fire back...

    -- Jim
    Attached Files Attached Files

  12. #12
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Thanks for the explanations Jim! I'm going to try to work tomorrow on this, and I'm sure I'll be asking more questions. Thanks so much for your time and help..Happy Easter <img src=/S/hugs.gif border=0 alt=hugs width=41 height=25>

  13. #13
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Hi Sarah,

    Sorry haven't got anything bookmarked about binary, it was something that I learnt a couple of years ago when working on a system with a developer. Of course I took it on board with some survey stuff that I have done since. If you decide to use it you can post here if you need some more help.

    Cheers
    Tony

  14. #14
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Hi Tony!
    Thanks for your help, I can't type how much I do appreciate it. Because I work on this project only a few hours a week, research has taken up quite a bit of time on the job, and I'd like to have something to show for the $$ by the time my boss gets back from vacation this week. I'm going to go with Jim's solution for now, but when I'm ready to tackle binary I'll let you know!

    Sarah

  15. #15
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple answers in data entry form (A2K)

    Jim,
    I've been working on putting my field names, etc. into your code for the survey form and it's been going pretty well. However, I've screwed up something as I'm calling a recordset (maybe?) from the wrong place because it can't find any questions. I've attached it, and if you have a minute do you think you could find the source of my error?
    Much thanks as always!
    Sarah
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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