?
Solved

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

Posted on 2013-12-23
10
Medium Priority
?
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39737350
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
ID: 39737732
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 38

Expert Comment

by:PatHartman
ID: 39737976
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:rlopez11
ID: 39738087
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 38

Expert Comment

by:PatHartman
ID: 39738189
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
 

Author Comment

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

Author Comment

by:rlopez11
ID: 39744755
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 38

Expert Comment

by:PatHartman
ID: 39745218
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
ID: 39747348
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 38

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
Suggested Courses

765 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