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

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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:

INSERT INTO tblResult ( MainID, TopicID, Response )
SELECT [Forms]![EntryForm]![ID#] AS MainID, tblTopics.TopicID, Null AS EmptyResponse
FROM tblTopics;

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.

Reply | Quote

Hi Judy,
I am including your database (modified). As you will see I have made the following changes:

a) The response field in the tblResults is now one character (I did this to speed up data entry of the response). You will notice that when you enter an ‘A’ (without the quotes) that the cursor will end up on the next record at it’s response field, so there is no need to use the ‘Enter’ or ‘Tab’ keys (only when you wish to ignore that particular question).

I made the Form add the 63 records (or as many as there are in the tblTopics table when you add a new record in the After Insert event on the main form named entryForm). The requery at the end of the event is to display all the records that are applicable to the main form’s record.

c) The subform now displays all 63 responses and their associated questions.

d) The autoexec macro now has an extra function that deletes all tblResult records where the response field is null.

Hope all this helps,
Pat

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

Sorry about the attachment. I seem to have lost it when I edited the post. Howveer, here it is.

I forgot to mention about setting to True the Autotab property of the response field in the sub form, this allows only a single character to be typed into this field without having to press ‘Enter’ or ‘Tab’.

Pat

Reply | Quote

Wow! Slick as can be! I can’t thank you enough. All sorts of new tricks to look through.

Reply | Quote

You have to watch out for attachments. If you preview a post you will lose your attachments. If you edited and previewed the edit, that’s what happened.

Reply | Quote

Thanks Charlotte.

That’s exactly what happened, I previewed the edit.

Pat

Reply | Quote

Patt,
I’ve played a bit with the tables and query, and I haven’t been able to modify itto avoid issues down the road. Precisely, I know the questions may change [some may be reworded, some deleted, some added] and the tblTopics will need to hold all of them forever with the associated TopicID. However, the field Topic# can be blank and the numbers don’t have to match TopicID and it is against that field that I will need the subform to display, in ascending order. I tried to have the subform display both the question and it’s Topic# using =Dlookup(“[Topic#]”,”[tblTopic]”,”[TopicID]![tblTopic]=[TopicID]![frmAll]”) but that doesn’t work. Perhaps because it really is pulling from the qry?
And I looked at the code you wrote
Private Sub Form_AfterInsert()
Dim ssql As String
ssql = “INSERT INTO tblResult (MainID, TopicID, RowCount)”
‘ ssql = ssql & ” SELECT ” & IDMain & ” as expr1, TopicID, ‘x’ as Response, 1 as expr2″
ssql = ssql & ” SELECT ” & IDMain & ” as expr1, TopicID, 1 as expr2″
ssql = ssql & ” FROM tblTopics”
DoCmd.SetWarnings False
DoCmd.RunSQL ssql
DoCmd.SetWarnings True
frmAll.Requery
End Sub
I am very weak on SQL code. Could you explain whether this is the place I should be making the changes rather than the frmAll field?

Thank you again. It’s what I asked for, I just need to tweak it a bit more so it will work over the long run.
Judy

Reply | Quote

Hi Judy,
You are quite right about keeping all the tblTopics records forever, ie. the used ones, the unused ones you can delete.

What I would do firstly to make sure you don’t delete tblTopics records that are being used is to go into Table Relationships and link the tblTopics table to tblResults (the link should happen automatically when you add the table tblTopics) and don’t forget to check the referential integrity box.
Whether you wish to check the two below (Delete cascade andUpdate cascade) is up to you, they do what they say (eg. Delete cascade means if you delete a tblTopics record it will automatically delete all the linked tblResults records below it, can be very handy when cleaning up a database of unwanted records).

To overcome the problem of :
a) making sure that Topic# is not blank, just check the Required field of the Topic# field in the table design of tblTopics.

to display Topic# on the subform frmAll all you have to do is to introduce a new field (say to the left of the question) and make this the Topic#.
Before doing this you will need to edit the query “qry tblResult” and add the field Topic# to the grid, thereby making this field available for the new field in the subform frmAll.
You will also need to put Topic# as the Order By field for the frmAll subform.

Don’t worry about the SQL I wrote, it just adds all the tblTopics records to the tblResults table when you add a new record to table tblMain Record.

What you may have to worry about in the future is when some of the questions are no longer applicable. What you may have to do is to introduce another yes/no field on the tblTopics record (say CurrentQuestion) to show if it’s one of the current set of questions (some questions may become redundant, not applicable). Then you would need to change the SQL I wrote to inserting a WHERE clause to just select the current questions to add (this means you don’t have to worry about adding non-desired questions to the table tblResults).

If you need further help with this just ask,
Hope all this helps.
Pat

Reply | Quote

You wrote :” to overcome the problem of :
a) making sure that Topic# is not blank, just check the Required field of the Topic# field in the table design of tblTopics.”
I was going to make the query criteria be ‘is not null’ and remove the Topic# for any topic which was no longer valid. This seemed easier than adding another field for current since the order may need to be changed around too.

” to display Topic# on the subform frmAll all you have to do is to introduce a new field (say to the left of the question) and make this the Topic#. ”
I had already tried that (see below)
” Before doing this you will need to edit the query “qry tblResult” and add the field Topic# to the grid, thereby making this field available for the new field in the subform frmAll. ”
I had done this, and added the text box to the form. However, no matter how I set it up, it does not seem to work with any third box.
The response field and any single other text field works. Adding a second text or number field makes it not work and I can’t figure out why. I’m sure it must be some simple piece I’m just not considering, but if you have any ideas I’d appreciate your help.

” You will also need to put Topic# as the Order By field for the frmAll subform. ”
Since I can’t get this to work, I don’t know how that will go.

Reply | Quote

Why don’t you post your database and I will make the changes and you can change what you like after that.
Pat

Reply | Quote

I’m posting it, but my objective is to understand what you did to make it work and why what I want to do doesn’t work.
Thanks – Judy

Reply | Quote

Hi Judy,

Ok, you can make the changes, you will learn a lot more if you do it.

To let Topic# be Null will work, as you said just put the ‘is not null’ in the query.

To display Topic# on the form should be quite straight forward, as long as you set to false the Enabled property and set to true the Lock property of the Topic# control on the form frmAll.

When you said putting the Topic# on the frmAll form did not work, what did you mean by did not work (you must realise that just the description ‘does not work’ does not give anyone much to go on. Enough of that.

Ok now try that and get back to us.
It’s always difficult when you don’t know how, but exceedingly simple when you do, that’s what I keep saying to my kids.
Pat

Reply | Quote

Thanks! I did as suggested – made Topic# Enabled be false and Lock property be true- and that works. I don’t know WHY those changes in properties makes it work. I thought you would always want Enabled to be on. And what does the Lock property do? [Sorry to be so simplistic, but in addition to knowing how to do it, I want the whys too.]

When I said putting the Topic# control on the form did not work, the question field then either gave #error# type messages when I put Topic# as one control and added a second one using a Dlookup for the question, or I got ?Name? messages when I just put it straight on the form.
Thank you for your patience.
Judy

Reply | Quote

You reply to my posts at 11.30pm my time, what is it your time when you read posts?

You don’t need a dlookup for the question control, it is supplied by the underlying query of the form frmAll which is a join of 2 tables (tblResult and tblTopics).

As long as the question (and Topic#) control has enabled set to false and locked set to yes you should be ok.
The enabled property allows the control to be entered, the lock property is used to lock the user out from changing the control. Try F1 help when you put the cursor on say the enable event.

In regards to the Question or Topic# control we dont want to go to either control or have the ability to change either control.
In regards to patience, I was learning once too.
HTH
Pat

Reply | Quote

Thanks. The time from the Lounge is 1:19AM but for me it isreally 7:19PM in Austin, Tx.
I appreciate all your help. The db is running smoothly.
Judy

Reply | Quote

Hi Judy – glad you got your DB up and running smoothly. Sorry I couldn’t be of more help, but the alligators are rapidly filling the swamp at the moment! If things don’t get better soon we may have to !

Did you know you can set your local time offset from GMT (or CUT) in Controls by editing your display preferences? I really thought you were working a night shift or something!

Reply | Quote

Hi, Wendell. With that many alligators, soon you’ll be able to walk across the swamp on their backs. Take care.

Given the hours I’ve put in lately, I might as well have been on nights. I’ve cahnged my ‘clock’. Thanks
Judy

Reply | Quote

Hi Judy,
Having just got home from work, I can now have some fun.
Well done with your changes, it seems like you nailed it. Good news.
Hope you learnt a lot. I certainly do on this forum, it’s quite fantastic, what with everyone chipping in and helping everybody else.
Cheers,
Pat

Reply | Quote

Hi devore1

Can you post your final working database example?

I did not follow all the changes.

Thanks, John

Reply | Quote

Gladly. My thanks to all, especially Pat.
Judy

Reply | Quote

Hi Judy,
Thanks for the compliment, but back to business.

I thought you wanted the frmAll records to be shown in Topic# sequence.
If you wish to do this, just go into the query ‘qry tblResult’ and put Ascending in the sort row for the Topic# column.

Cheers,
Pat

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.)

Reply | Quote

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

Reply | Quote

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?

Reply | Quote