[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Posted on 2013-12-23
Medium Priority
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
Question by:rlopez11
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
LVL 39

Accepted Solution

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:

SurveyID (autonumber primary key)
Survey Desc

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)
AnswerOptions (yes/no or multiple choice, etc)

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

SurveyResponseID (foreign key to tblSurveyResponse)
QuestionID (foreign key to tblSurveyQuestions)

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

Author Comment

ID: 39737732
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
LVL 39

Expert Comment

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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 39738087
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?

LVL 39

Expert Comment

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.

Author Comment

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

Author Comment

ID: 39744755
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.
LVL 39

Expert Comment

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.

Author Closing Comment

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.
LVL 39

Expert Comment

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

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

649 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