Solved

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

Posted on 2013-12-23
10
322 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
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 34

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
 

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 34

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 34

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 34

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

914 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

21 Experts available now in Live!

Get 1:1 Help Now