469,908 Members | 2,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,908 developers. It's quick & easy.

How to remove blank image spaces from access report

mb60
37 32bit
sir
I created a multiple choice questions data bank in ms access. some questions have images. in the report blanks gaps are appearing when the question is without image. please help me in this regard. For your kind persual here the database is attached

thank you

mbtilak
Attached Files
File Type: zip QUESTION PAPER.zip (192.6 KB, 231 views)
Jan 15 '10 #1

✓ answered by TheSmileyCoder

I have taken a look at your database, but it keeps getting errors which is probably due to the nature of your table design.

Why do you have 50+ fields in a single table? Your trying to use a database like an excel sheet, instead of a database.

I suggest you read this article on Database Normalization and Table Structures. You will need to get a better understanding of how databases work.


Assuming you always expect 4 possible answers to a question,
I would suggest a structure something along the lines:
tbl_Questions (the table)
with the fields:
KEY_Question, Autonumber, Primary Key
tx_Question, Text Field, with the actual question.
tx_AnswerA, Text Field
tx_AnswerB, Text Field
tx_AnswerC, Text Field
tx_AnswerD, Text Field.
tx_CorrectAnswer, Text Field

Assuming you might have a different amount of possible answers for each question, you need 2 tables, but you can read about that in the article mentioned.

19 5787
TheSmileyCoder
2,321 Expert Mod 2GB
Try having the image in a sup-report, and set teh supreport properties
CanGrow=True
CanShrink=True
Then also set the same for the Detail section of the report.
Jan 15 '10 #2
ADezii
8,800 Expert 8TB
Just subscribing, have downloaded the DB and will look at it later. BTW, there are 7 Reports, which one are you referring to?
Jan 15 '10 #3
mb60
37 32bit
Thank you
Report English Paper
Jan 15 '10 #4
mb60
37 32bit
Thank you for hint.
I am not successful in getting the result
Jan 15 '10 #5
TheSmileyCoder
2,321 Expert Mod 2GB
I have taken a look at your database, but it keeps getting errors which is probably due to the nature of your table design.

Why do you have 50+ fields in a single table? Your trying to use a database like an excel sheet, instead of a database.

I suggest you read this article on Database Normalization and Table Structures. You will need to get a better understanding of how databases work.


Assuming you always expect 4 possible answers to a question,
I would suggest a structure something along the lines:
tbl_Questions (the table)
with the fields:
KEY_Question, Autonumber, Primary Key
tx_Question, Text Field, with the actual question.
tx_AnswerA, Text Field
tx_AnswerB, Text Field
tx_AnswerC, Text Field
tx_AnswerD, Text Field.
tx_CorrectAnswer, Text Field

Assuming you might have a different amount of possible answers for each question, you need 2 tables, but you can read about that in the article mentioned.
Jan 15 '10 #6
mb60
37 32bit
thank you sir
I tried the table structure given by you.
Regarding images the same problem is persisting
Even the image is used as subreport the blank spaces are continuing in the report.
Jan 17 '10 #7
TheSmileyCoder
2,321 Expert Mod 2GB
Try setting your SQL of the supReport's recordsource to:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Question Bank].ID, [Question Bank].Image
  2. FROM [Question Bank]
  3. WHERE ((([Question Bank].Image) Is Not Null));
The Is Not Null clause will ensure you only get results when there is something in the image field.
Jan 17 '10 #8
ADezii
8,800 Expert 8TB
If you wish to include ALL Records on your Report whether or not they have an Image assigned, and if you wish to remove the 'Spaces' created by the blank Object Frame containing the Images, this can be done via code in the Format() Event of the Report, but it will be a little tricky. Essentially what you would do is:
  1. For Records that do not have an Image:
    1. Set the Height of the Image Control to 0.
    2. Adjust the Top Property of all Controls below the Image Control, basically moving them UP proportionally.
    3. Resize the Detail Section of the Report, basically shrinking it by the Height of the Image Control.
  2. For Records that do have an Image:
    1. Maintain their relative positioning by utilizing the their Tag Property to store their original Top Values.
P.S. - The explanation is simple, the implementation would be much more difficult.
Jan 17 '10 #9
TheSmileyCoder
2,321 Expert Mod 2GB
A sup-report has the beauty of only appearing when there is something to show. No need to programmatically change it all.
Jan 17 '10 #10
ADezii
8,800 Expert 8TB
Sorry TheSmileyOne, I was under the impression that the Sub-Report approach was not working.
Jan 17 '10 #11
TheSmileyCoder
2,321 Expert Mod 2GB
The Issue I had forgotton to take into account was that since the image is stored in the same table as the question, there is an ID for each "Image row", whether or not there is an image. Im used to working with subform/subtables, in which case there would only be an entry if you actually added a image. Thats why the "Is not Null" clause should be included.
Jan 17 '10 #12
mb60
37 32bit
@ADezii
sir
what is that code?
Jan 18 '10 #13
mb60
37 32bit
@mb60
I request you to to make adjustments to meet the requirement in the attachment file. This will help the teaching community in making objective type of questions. Thank you
Jan 18 '10 #14
ADezii
8,800 Expert 8TB
Try TheSmileyOne's Sub-Form approach first, since creating the code that I previously described would be time consuming to say the least.
Jan 18 '10 #15
mb60
37 32bit
@TheSmileyOne
Sorry for disturbing you once again. How to use the code given by you in setting SQL of the sub-report?
Jan 18 '10 #16
ADezii
8,800 Expert 8TB
You are definately not disturbing me, mb60. The code has absolutely nothing to do with defining the SQL for the Sub-Report. The code will dynamically re-position the Image Control and all Controls below it based on whether or not the Image Control actually contains an Image. The Detail Section of the report will be resized (shrunk) accordingly.
Jan 18 '10 #17
mb60
37 32bit
@TheSmileyOne
sir
your technic worked. removed the blank spaces. but images of all records are appearing in the sub report even though they are not selected by using yes/no check box option.
inspite of writing the entire code I used the ' is not value' in the criteria of query.
Feb 3 '10 #18
TheSmileyCoder
2,321 Expert Mod 2GB
Well im kinda guessing here, since your post isn't that accurate.

Remember that a subreport must be linked to your main report. Select the subreport from within the main report, and select its properties. You should see "Link Child Fields" and "Link master fields". Clicking the 3 small dots on the right should allow you to set the link criteria. In this case you want to link via ID in main report to ID in child report.
Feb 3 '10 #19
mb60
37 32bit
@TheSmileyOne
Sir
As you directed I carried out the changes in my database. It is successful.
my sql is as follows

SELECT Table1.ID, Table1.Q, Table1.[1], Table1.[2], Table1.[3], Table1.[4], Table1.im, Table1.im.FileData, [im].[FileFlags] AS Expr1, Table1.im.FileName, [im].[FileTimeStamp] AS Expr2, Table1.im.FileType, [im].[FileURL] AS Expr3, Table1.sel
FROM Table1
WHERE (((Table1.im.FileName) Is Not Null) AND ((Table1.sel)=-1));

I have a check box named as 'sel' for each record. suppose if a record without image is selected it is not appearing in the report though it has text. I request your help to overcome this problem

Thank you
Feb 4 '10 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by qwweeeit | last post: by
4 posts views Thread by colleen1980 | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.