Form Coding Question for Access 2007 Db

[mishmish3000]

Hello everyone! I'll attach a Word 2007 document and the database in question... basically, we're developing a disaster database where we can quickly generate questionnaires to send out to the field. These will assess people's needs and allow us to help them more effectively and efficiently. I have one form already where the disaster coordinator can choose a pre-made report and print it out for the field staff.

My question is: can we make a form that has the questions in their subject matter categories, where a disaster coordinator can pick and choose among them to create "on the fly" questionnaires for printing and distribution to field staff? I'm not sure how to do that and help would be appreciated. I know how to let them choose "canned" reports, but what if they want to make their own, by selecting from the numerous CASPER questions... I'm sort of stuck!

Hope everyone is looking forward to a nice weekend! We had tornadoes here in Nashville last night but luckily I don't think there were any very serious injuries, just quite a bit of damage to roofs, cars, trees...
MishMish3000

[Mark L]

I'd create a table that has 3 fields: Category, Question, Selected (yes/no).

Have a form that displays all the records in that table in a subform. The coordinator could then just click the Selected box for any question he/she wanted on the report. If you got fancy, you could add buttons to "Select All", or "Select all in a Category", etc. Your report would then use this table as its RecordSource. You could create a Header based on Category, etc.

[mishmish3000]

What a cool idea! I felt really stuck over how to present them their "choices". I'll try that out and see...
Thanks!
MishMish3000

[HansV]

Mark's suggestion is probably best, but just for fun I've attached an unfinished setup that uses a tab control and list boxes to let the user select fields. The check boxes that you already had show/hide the tabs (except the Basic Demography, that one's fixed).

CASPER Db Form Question.zip

[mishmish3000]

Cool!!! I'll download it and take a look. I've already begun Mark's solution but I'll definitely look at yours, too, Hans! I really appreciate everyone's help and ideas! This is great!
Have a super weekend, everyone!
MishMish3000

[mishmish3000]

Greetings, humans!
The CASPER database returns... I made a new table with all the questions and the text of each question; the table also has a Yes/No field for Selected. I then made a subform within Form1 so the user could, if they wished, create an ad-hoc report instead of one of the "canned" ones by selecting the questions they want.
MY question is: how do I get the selected questions to show up, along with their category, on the CASPER AD HOC Report? I know that may seem like a goofy question but it's been one of those days.
Any help would be appreciated. We're SO close!
Thanks
MishMish3000

[Mark L]

Your RecordSource for the report needs to be a query, not the table itself:

SELECT * FROM tblSelectQuestion WHERE SELECTED=True

[HansV]

Create a query that selects the records from the questions table for whom the check box has been ticked, and use this as the record source of the report.
See the attached version.

TN CASPER.zip

[mishmish3000]

Thanks, Hans! I'll try it out.
MishMish3000

[mishmish3000]

Hi there! Hans, your query seems to work pretty well but when I was trying it out, I ran into a small issue. Please see the attached zipped file with the Db and a Word 2007 document with screen shots and an idea I had.
Thanks so much!
MishMish3000

[HansV]

I see all three choices when I open the report:

x521.png

Perhaps the third one was pushed to the second page with your printer setup? (Different printers have different margins)

You can put a button cmdAdHocPreview on Form1 with On Click code

Code: Select all

Private Sub cmdAdHocPreview_Click()
  On Error GoTo ErrHandler
  DoCmd.OpenReport "CASPER AD HOC Report", acViewPreview
  Exit Sub

ErrHandler:
  If Err = 2501 Then
    ' Report canceled
  Else
    MsgBox Err.Description, vbExclamation
  End If
End Sub

[mishmish3000]

I bet that's what it was...
I'll put the button and the code you so helpfully provided, Hans, on Form1.
Thanks!!!
MishMish3000

[mishmish3000]

Thanks!!! Thanks to everyone who helped! This is SOOOO cool. It works like a charm!
MishMish3000