Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prob: Making data entry form for survey (Access '97)

    I need to build a form to enter the responses to a 63 question survey. The main table is set to hold demographic data for each survey. Results is set to hold the response to the questions, identified by the TopicID#. The topics table holds the text of the questions, TopicID#, etc.

    EntryFrm (both of them) is built off the Main table. But no matter how I try, I can't come up with a secondary table which would have all 63 questions, one below the other, with a response field I could just tab down and enter the single character result (default=null, options = 0,1,2,3) quickly. I can build it as a combobox, but then I'd have to pull down the list to the correct question every time - and that's very time-consuming as well as it forces me to use both mouse and keyboard.

    Is there an easy way to build it?
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Prob: Making data entry form for survey (Access '97)

    OK - I took a quick look at what you are trying to do, but I have one question. Do you want to have 63 responses in tblResult for every survey that is received? Or would it suffice to not enter a record if there was no entry - in other words a null when you do a left join for that TopicID? If most of the questions have Null as the answer, and only some get 0,1,2,3 then it would simplify your data entry problem.

    On your subform you are using a list box which gets pretty cumbersome when you start a new record and you see a new list box below immediately after you start to enter a new record. If the idea of entering only those questions which got a response works, then I would suggest a simple two text-box subform where you keyed the question number, pressed enter and keyed the response for those where there was one.

    If that doesn't work, then you may want to resort to some code that automatically creates 63 records in tblResult each time the entry of a new questionaire is started. The default would be null, and you would only enter the response if there was one, or simply press the enter key if no answer was given. You could even use locked combo boxes (with no tab stop) to display the text description of the question. The response field would be just a simple text box - you could add validation rules to prevent any keying errors. You could run an append query that is triggered by the AfterInsert event on the main form - you would need to provide the MainID# by referencing the form as Forms![EntryForm]![ID#] in your query grid. (By the way, using # characters in names of things in Access is generally not a good idea - the same is true of * and a few other special characters). Let me know if any of this doesn't make sense - hopefully at least some of it will. Looks like an interesting application.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Prob: Making data entry form for survey (Access '97)

    Sorry, I don't understand why you don't just have your subform as text boxes (you could set all the data validation on a the default text box and then drop them all onto the form), and then, if you want to rapidly go a particular answer, have a combo or list box to direct you.

    Have I missed your aim?

    PS - Dealing with medical records. Good luck to you mate.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Prob: Making data entry form for survey (Access '97)

    Ooops. Looks like my post was made a little redundant by the much more erudite simultaneous post from Wendell!

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prob: Making data entry form for survey (Access '97)

    Wendell, thank you but I'm not following all you wrote. Theoretically, every question is supposed to be answered. In reality I know some will be left blank. Basically the answers are NA, Y, or N, OR 0,1,2. If they ledft the question blank, I'd leave the result blank. My problem is that calculations will depend on knowing whether the question was applicable, so NA can't =null. Blank has to =null.
    Whatever way allows for easiest entry - that's what I want. If there can be 63 response boxes, one after the other, so I can TAB/ enter, TAB/ enter my way down, that would be the fastest. Your option to build a code that creates table entries is fine- but I don't know how to do that.
    I am asked to build dbs to hold survey results all the time. Usually the list of items isn't long, or the number of surveys/month is few. This time we will be doing 250+/month, and I want it really EASY to wiz through the data entry part. Any help is wonderful.
    Thanks - Judy

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Prob: Making data entry form for survey (Access '97)

    The simplest way to handle this, assuming your Results table holds one response for each question, rather than a record for each survey, is to use an unbound form. In the AfterUpdate of each control, you run some code to write a record to your Results table or to edit an existing record. You would also need code to populate the survey form if there was an existing survey for that respondent, and to clear the form when a new survey was being entered. It's actually more complex to describe than it is to implement.
    Charlotte

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prob: Making data entry form for survey (Access '97)

    Charlotte - the Main table holds the unique survey records. Results would hold the answers to all 63 questions for each record. Since we don't review the same record more than once, there should be no way we'd have the same record twice (if I understood your third sentence.)

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

    Re: Prob: Making data entry form for survey (Access '97)

    I agree with Wendells idea to populate the results table with all 63 records.

    You could also make a single keystroke to enter data in the response field by setting the AutoTab property of the response field, of course you would have to set the response field length to one. This would then mean just one keystroke per response field therefore saving heaps of time.

    This does not require much code at all to do all this.

    If you like I will post a copy of your database amended to do the above, however I cannot send it till tomorrow night as it's on my PC at home.

    Hope this helps.
    Pat

  9. #9
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: After reflection

    I didn't think this through well enough, sorry. There will be many times when the survey only answers a few of the questions. I still want all 63 questions to come up, but I do NOT want the results table to hold null responses.

  10. #10
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prob: Making data entry form for survey (Access '97)

    Thank you so much! However, I didn't think this through well enough, sorry. There will be many times when the survey only answers a few of the questions. I still want all 63 questions to come up, but I do NOT want the results table to hold null responses. Is that possible?

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: After reflection

    Gee - I'm sorry I gave up the ghost and went to bed last night. I missed all the action!

    After thinking about things a bit, either approach could be used. The first approach involves keying the question number, pressing ENTER (or TAB), keying the answer, and pressing ENTER again. It has the advantage of not requiring any code, but the keying of the question number probably makes it a little more prone to keying errors.

    The second approach involves only a little code which would be placed in the AfterInsert event for the main form, and would run a stored query which creates all 63 results records for that response record with the answer null. (You could build the code with the "Button" wizard and then simply copy it into the form event.) Then the keying would simply involve entering the answer in a text box and pressing ENTER - I guess you could use a combo box as well to limit keying errors. But it would require stepping through all 63 responses as you entered data, and simply leaving the questions with no answer Null. Then at convienent times you could run a delete query which picked out any tblResults records that had a Null answer and delete them.

    Either strategy should work so the right choice probably depends on the quality you desire and the percentage of answers that a particular survey response contains that are not Null. Let us know how it turns out.
    Wendell

  12. #12
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: After reflection

    I'd prefer the second approach, but I don't know how/ understand what you mean by "running a stored query which creates all 63 records." The fewer keystrokes per entry the better. Is there an example I could look at somewhere?
    I know how to build a combobox which would have the questions, but that would require using the mouse each time to run down the list and select the correct question, and then moving into another field, typing the response, etc. That is very slow/ labor-intensive and I want to avoid that.
    I could build a delete query to run every time the db is opened - that would provide a consistent way the get rid of the nulls. Would that be appropriate?

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: After reflection

    I don't know of an example which does this kind of thing, so I tried to create a query based on your database, but I get data validation errors when I try to append the 63 (actually it was trying to do 64 for some reason) records. In any event the SQL looks like this:

    <font color=448800>INSERT INTO tblResult ( MainID, TopicID, Response )
    SELECT [Forms]![EntryForm]![ID#] AS MainID, tblTopics.TopicID, Null AS EmptyResponse
    FROM tblTopics;</font color=448800>


    If you copy this, open a new query and just close the "Add Table" dialog box without adding any tables, then click the SQL button that should be visible on the toolbar, and paste in what you just copied replacing the SELECT; statement that appeared you will have created an append query that comes close to doing what you want. In order for it to run correctly, you must have the EntryForm open, and it must be on the record that you want to create new empty results records for. Once you have pasted in the SQL String, you can switch to Query Design mode and see what it looks like.

    To make the query run, create a new button on the form, making sure the wizard is turned on. It will ask you what you want to do. Pick the Miscellaneous, which will give you the option to Open Query. Pick that one and finish making the button. The problem I ran into was the table didn't want to let me put empty responses in, and I tried to remove that validation, but it still failed for a reason that wasn't clear. I've got an early morning so best I turn in.
    Wendell

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: After reflection

    Thanks for helping out here - I was running on fumes. Unfortunately I don't see any attached database when I look at your message - are others able to see it?
    Wendell

  15. #15
    Star Lounger
    Join Date
    Nov 2001
    Location
    Texas, USA
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fantaastic, but db not attached

    Patt,
    Thanks ever so much! That sounds exactly what I wanted. But I don't see any attachment either.
    Judy

Page 1 of 3 123 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
  •