Form Coding Question for Access 2007 Db
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Form Coding Question for Access 2007 Db
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
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
You do not have the required permissions to view the files attached to this post.
Anne
-
- 3StarLounger
- Posts: 331
- Joined: 11 Feb 2010, 03:55
- Location: Land O Lakes, FL
Re: Form Coding Question for Access 2007 Db
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.
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.
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Form Coding Question for Access 2007 Db
What a cool idea! I felt really stuck over how to present them their "choices". I'll try that out and see...
Thanks!
MishMish3000
Thanks!
MishMish3000
Anne
-
- Administrator
- Posts: 78872
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Form Coding Question for Access 2007 Db
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).
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Form Coding Question for Access 2007 Db
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
Have a super weekend, everyone!
MishMish3000
Anne
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Form Coding Question for Access 2007 Db
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
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
You do not have the required permissions to view the files attached to this post.
Anne
-
- 3StarLounger
- Posts: 331
- Joined: 11 Feb 2010, 03:55
- Location: Land O Lakes, FL
Re: Form Coding Question for Access 2007 Db
Your RecordSource for the report needs to be a query, not the table itself:
SELECT * FROM tblSelectQuestion WHERE SELECTED=True
SELECT * FROM tblSelectQuestion WHERE SELECTED=True
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.
-
- Administrator
- Posts: 78872
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Form Coding Question for Access 2007 Db
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.
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Form Coding Question for Access 2007 Db
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
Thanks so much!
MishMish3000
You do not have the required permissions to view the files attached to this post.
Anne
-
- Administrator
- Posts: 78872
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Form Coding Question for Access 2007 Db
I see all three choices when I open the report:
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
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Form Coding Question for Access 2007 Db
I bet that's what it was...
I'll put the button and the code you so helpfully provided, Hans, on Form1.
Thanks!!!
MishMish3000
Anne
-
- PlatinumLounger
- Posts: 3691
- Joined: 15 Jul 2010, 14:10
- Location: Milton, TN
Re: Form Coding Question for Access 2007 Db
Thanks!!! Thanks to everyone who helped! This is SOOOO cool. It works like a charm!
MishMish3000
Anne