How can I get the inital subreport display on the report to shrink, like it does for the rest of the report?

When I run my report, there is the same subreport displayed for each section. It is a list of skills taught in the class. The first class skills list always covers the first name of the student in the report in the screen report view. I've tried resizing the subreport, setting the shrink and grow to Yes, taking out and putting items back in, all to no resolution. I've put the report and base queries in my sample database, which, wouldn't you know it, doesn't replicate the problem. But maybe you can look at it to see what might cause the problem. I've included a screen shot so you can see what the display error looks like.

The report is the Class Roster Report and the query is the Class Roster query
Class-Roster.PNG
Sample-Training-Database-2015-01-12--2-.
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
I've put the report and base queries in my sample database, which, wouldn't you know it, doesn't replicate the problem.
Then this may be an indication that your db is going corrupt.
Create a new db and import all the objects and see how you get on.

If the issue still persists, then please post your sample database with a shorter name and no special characters.
For example:
SampleTrainingDB20150112.accdb
SampleTrainingDB20150112.mdb

JeffCoachman
0
David BigelowStaff Operations SpecialistAuthor Commented:
When I try to move the database objects, I am getting the error, "The database has been placed in a state by user 'Admin' on machine (it gives my system number) that prevents it from being opened or locked."

I've never had that problem when moving objects to another database: for example, for testing purposes before implementing an object or code.

I am considering applying this fix for that: http://www.dbforums.com/showthread.php?1605962-DBForums-Code-Bank&p=6274786#post6274786.

Your thoughts?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The "fix" you applied is really nothing more than a launcher, and won't resolve the issue you're experiencing.  As Jeff mentions, it would seem you have some corruption starting up in your db. First, make a copy of your database, then do this:

1. Compact the database
2. Compile it - from the VBA Editor, click Debug-Compile and fix any errors that come up. Continue doing this until the menu item is disabled.
3. Compact again

You may also need to Decompile the database. To do that, create a standard Desktop shortcut with this as the Target:

"full path to msaccess.exe" "full path to your database" /decompile

Run that, then run the 3 steps above again.

If you can do those, then try again to move everything into a new, blank database. You  may find one or more objects in the database that won't import. Those may be corrupt, and you'd have to recreate them, either from a recent (and known good) backup, or recreate it from scratch.

Also be sure your installation of windows and Office are fully updated.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jeffrey CoachmanMIS LiasonCommented:
When I try to move the database objects, I am getting the error, "The database has been placed in a state by user 'Admin' on machine (it gives my system number) that prevents it from being opened or locked."
  Has this always happened, ...or is this what happened just now, when you attempted to copy the objects?
...try seeing if thee is a locking file present (.ldb, .laccdb), and delete it.

I've never had that problem when moving objects to another database:
I have, ...in the case of the error you are getting, ...it mostly happens if you do not compact/repair the db before you try to "move" (drag and drop) objects between databases.
The safer, more preferred, method id to create a new db and "Import" all the objects.

Keep us posted.

But back to your original issue of the subreport...
Try to resubmit our sample db
Also include a screenshot of what you have currently, and post a clear graphical example of the exact output you need, ...

JeffCoachman
0
David BigelowStaff Operations SpecialistAuthor Commented:
Here's what I've done so far:
1. Exported the objects (query and report for Class Roster and subreport for Class Skills) into database backups, working back until the report opened correctly (December 15, 2014, I tested about once per week in my list of backups).
2. Split that database into a backend. It still worked.
3. Linked the old backup to the current data tables - report opened incorrectly. (looks like a corrupted table is the problem)
4. I did Compile the frontend side of the database. Somewhere in the middle of all that.
5. To test whether the compile made a difference, I compiled the current database, first.
6. Next, I moved the Roster objects to a working database with older tables. The Roster report worked.
7. Then I linked that database to the current tables, and the roster report did not open correctly.

I've attached a sample database and sample error. Where the report box border breaks up in the sample error, it is covering an employee's name. I've not allowed the screenshot to capture the full name of the employee listed below that covered up name.

The exact object names involved are:
qry_Class_Roster
rpt_Class_Roster
rsub_Class_Skills subreport             (I see there's a space in that name. Does that matter?)

Any other options before I have to re-enter data?
Sample.accdb
SampleError.PNG
0
Jeffrey CoachmanMIS LiasonCommented:
I am not getting the same screen as you...
This is what I get when I open the report:
screen
0
David BigelowStaff Operations SpecialistAuthor Commented:
Yes, that's right. Same table names with different data. I'm thinking that the current tables with the real data may have been corrupted. So, it looks like the objects are in good working order, but the data being accessed is corrupt in the current tables. I didn't want to post those tables with the confidential employee data. But, now that you mention that, I'm thinking to change the link of the Skills table (that's the source of the subreport that isn't displaying correctly), to reference the old working backup, leaving the other tables with the current database, and see if that doesn't solve it. That would be easier to update than all the employee training records.
0
Jeffrey CoachmanMIS LiasonCommented:
So, it looks like the objects are in good working order, but the data being accessed is corrupt in the current tables.
Just to be on the safe side, ...don't take that for granted.
If corruption in the data is present, ...then it may also be creeping into the reports (or other objects) as well
Make a backup of the db as it is now, ...then do all of your testing/repairs on a copy.

JeffCoacman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BigelowStaff Operations SpecialistAuthor Commented:
I put my frontend objects in a working database, then began importing the backend tables one at a time, updating the relationships, and running the report. I found out that my Class Rosters table was the one causing this particular error. Then, importing the Class Rosters table backups, date by date, I found that it corrupted on January 20.

I can create referential integrity between the class pk and fk in the relationship between the class table and class roster table, but not between the UserID pk and UserID fk in the AM Operators table and the class roster table. I suppose I could figure out which User IDs are missing and then update the working table with the information. I think it would be more pure to just see which classes are missing overall, and start from there.

So, my plan is to make this new database the one to use going forward, making a backup of it today and regularly.

Jeff, does this adequately follow your advice to make repairs on a copy? I would continue importing the rest of the tables one a time and testing them, as well.
0
David BigelowStaff Operations SpecialistAuthor Commented:
I've just found the primary culprit. The Class title field was set to grow. When it did, it pushed down the Class Skills list, which caused that list to cross over a name. I adjusted the class name field size and moved the class list down, giving the class name room to grow; now the report is fine. I figured all that out when I did all the above fixes and the class skills list again started crossing over the names list. That's when I realized the class name field size had grown to a second line, unlike the rest of the shorter class names in the rest of the report.
0
David BigelowStaff Operations SpecialistAuthor Commented:
I'm up and operational again, without the report error. Thanks for your help!
0
Jeffrey CoachmanMIS LiasonCommented:
Glad you got it working
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.