Replies

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

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.

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

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

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

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?

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?

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.

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

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

I’ll go hunt for articles about this. Thanks. I tried to make one but I’m not sure if all the pieces are there. I can’t tell how the items get named, hold data, or anything else. Perhaps I can find an example from somewhere.
I appreciate your pointing me in this direction.
Judy

I’m not sure I understand. I need a separate form for each of the 100 questions? Do I just place all 100 forms one below the other on another form?
Where or what is the switchboard manager?
Thank you-

Yes, it is a multi-user db. However, tonight I used the copy I have on my computer at home (I am the administrator of the file) to make a copy with only the 3 pieces, and it still has a problem.

No, there are no sub-reports. 1 table, 1 query, 1 report- very straight forward. The filter is only the query itself- no separate filter. And at this point I have only 10-15 rows in the table, so it’s not a question of too big a file. Now that may be the case on the other db since it now holds over 10,000 records. Is there something I should be doing to protect that one against its size?

I took out all but the table (and deleted most of the entries), query and report- still have the same problem for the one filtering for year. I’d attach it, but even at that minimal level it is 1.06mg.

I’ll keep my fingers crossed that a lightbulb goes off for someone. Thanks for your help.
Judy

Thanks. I was hoping to avoid that (importing piece by piece into a clean db). I’d run to do that EXCEPT I am having the same problem with reports in a 2nd database, totally unconnected to the first, stored on a different server (although accessed through the same LAN), whether I run it on my version at home or work.
Everything is fine in the 1st one unless the date in the criteria is after 1/16/2002. In the other database, the criteria is the 4 digit year in the short-date field for enddate.
In both cases, the query runs just fine but the report built from only that query stalls. In the lower right hand corner, where the >>>>>>> shows the progress of the report being generated, the message ‘Formatting page, press ctrl_break to stop’ shows up, the progress bar goes to the end, and it just stops right there.