Solved

Why do text boxes in an Access Report move to the previous group/section when being repositioned?

Posted on 2013-12-23
10
321 Views
Last Modified: 2013-12-30
I have to develop a 16 page report that will contain student information.  Parents will be provided this report (16 pages for each student) to update or correct current data.  I developed the report by programming 8 groups.  All 8 are grouped on the student ID#.  The intent is to program 2 pages per group.  All works well until I get to the 5th-6th page.  Once there, when I try to reposition a textbox in the current group it jumps to the previous group.  Pages on the report will contain data fields, some questions requiring yes/no answer by the parent, and one image.

I've tried grouping on different fields but the same occurs.  Am I exceeding MS Access capability?

I'm using MS Access 2010.

Thanks in Advance
0
Comment
Question by:rlopez11
  • 5
  • 5
10 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
Your problem sounds more like a table structure problem.   Questionnaires can be developed easily by using  a couple of tables.  The key is to not hard code individual questions.  At its simplest, a questionnaire schema might be:

tblSurvey
SurveyID (autonumber primary key)
Survey Desc

tblSurveyQuestions
QuestionID (autonumber primary key)
SurveyID (foreign key to tblSurvey)
QuestionGroup (used to allow you to group questions for nicer formatting)
QuestionSeq (order within a group)
QuestionText
AnswerOptions (yes/no or multiple choice, etc)

tblSurveyResponse
SurveyResponseID (autonumber primary key)
PersonID (foreign key to tblPersons)
SurveyDate

tblSurveyAnswers
SurveyAnswersID
SurveyResponseID (foreign key to tblSurveyResponse)
QuestionID (foreign key to tblSurveyQuestions)
SurveyAnswer

With a normalized schema, you can easily create a survey of any length and have a place to record the responses.
0
 

Author Comment

by:rlopez11
Comment Utility
Pat,
Thanks for your response.  I see exactly what you mean.  So that you can have a better picture of what I'm trying to do and see if your proposed solution will work, I've attached a copy of the first several forms, which is really what the parent is updating.  They are in WORD--in this format the parent has to manually complete all the forms.  The goal is that by inserting all the already stored data the parent does not have to manually complete all the forms.  If no information has changed the parent would simply sign the forms.

I can certainly develop tables that contain the questions and form information.  I'm not concern about storing the information in Access.  The parent's response/updated information is stored in a third party software.  Additionally, the forms must become part of the student's file.  As you can see also the forms must be in English and Spanish.

Thanks for your help
Re-EnrollmentApp.doc
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The first part fits exactly.  The rest will require some thinking.  You will most likely end up with a combination of techniques but it will be easier to manage than your current process.

Normalize things like the contact list and drug list and use subreports so you can display them in columnar format.

It also looks like you intend to print these forms with English on one side and Spanish on the other.  That is easy enough to do when each page is hard-coded but not so easy to control when they are variable as they will be with my suggested method.  Can you print separate documents so you have one for each language?  It shouldn't matter if you are emailing the parent a pdf.  You just need to know which language to send.
0
 

Author Comment

by:rlopez11
Comment Utility
Pat,
Again, thanks for your response.  Obviously, the suggested method will take some time to complete.  Should I leave the question open or should I close it?  If I'm to close it, before I do, do you know the answer to one of the original questions:  Am I exceeding Access' report capacity by trying to hard code such a document?

Thanks
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Everything has limits.  You'll need to take a look at the limits help entry for your version of Access.  I'm guessing that with 16 pages of unique controls, you are exceeding some limit.  I think Access only allows a space of 22 x 17 inches for formatting (2 pg wide, 2 pg long) a report.  A size that I have never come close to exceeding in 20 years BTW.  I'm not sure how having subreports affects the total size.  I've never had to face the problem.

Another solution is to create the "report" with Word or Excel.

You need to think about why you are doing this with Access.  Are you intending to reprint the report with filled data?

Going the Word route will require coding since you would need to automate Word to populate the fields.  Using my suggestion of a schema change doesn't necessarily require coding.  You can probably get by with queries and property settings.  It would reduce the page size to something manageable since most of the data is actually one line of the detail section that simply prints as many times as you have rows of data to fill it.  Because of the variable nature of the questions, you will probably still need a couple of subreports that are formatted specifically for certain types of data.  I'm sure you were given a document to reproduce.  If the user will allow flexibility in presentation order, it will help you greatly.

Using Excel as a "report" gives you the ability to send the workbook to the parents and have them return it filled with data (if they have the capability) and you could then import directly from the spreadsheet which would reduce the data entry burden.  This would also require some code since the Excel file will not be a simple list which could be imported with a one line TransferSpreadsheet expression.  You could also pick out the data from a Word document but I find Word much more difficult to automate so I would use Excel unless the client insisted on Word.

It's Christmas Eve.  Give yourself a week to think about a workable schema and discuss possible reformatting with the user and then post back.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:rlopez11
Comment Utility
Thanks. Will get back by the end of this week.  Merry Christmas!!
0
 

Author Comment

by:rlopez11
Comment Utility
Pat,
Hope your had a great Christmas!  Good news-- I hope? I'm programming the last form of the English version of the packet.

Summary (so far):
1. I developed four tables-- English Form (EF_#-#) 1-4.
2. Each table contains fields for questions or statements used in two forms. In some cases the fields are memo type fields-- to be able to bold or underline text.
3. I created a report for each form.  I have 8 reports (for simplicity and trial/error purposes now).  Later I plan to program 2 forms per report; I will end up with 4 reports instead of 8.
4. Next I created the main report to which I added the 8 reports as sub-reports. Two of the eight reports have sub reports.

I just added the unfinished 8th report to the main one and it works great!  Each student has an 8 page form packet already filled-out with the existing data on hand!

My next step is to program the Spanish version.  Once I program two forms per report I will then do the same for the Spanish version. If all goes well I will end up with a main report with 8 sub-reports-- 4 containing the English version (2 forms per report) and 4 containing the Spanish version.

Your thoughts? So far it appears to be working great.  I will be printing packets for about 400 students at a time-- and, so far it takes Access less than a minute to format that many.

Thanks for your help!!  Please advice on closing the question.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Separating the English and Spanish versions cut the size in half and that solved your immediate problem.  I would have normalized the schema but if you're happy with how this works, that's fine.
Merry Christmas, Happy New Year

To close the question, accept one or more of the answers and award a score.
0
 

Author Closing Comment

by:rlopez11
Comment Utility
Great advice!  I spent almost 5 weeks trying to solve it on my own.  With Pat's advice I was able to accomplish my goal in 5 days.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Next time, come visit us sooner:)
Happy New Year.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now