How to remove blank image spaces from access report

[mb60]

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

[TheSmileyCoder]

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.

[ADezii]

Just subscribing, have downloaded the DB and will look at it later. BTW, there are 7 Reports, which one are you referring to?

[mb60]

Thank you
Report English Paper

[mb60]

Thank you for hint.
I am not successful in getting the result

[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.

[mb60]

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.

[TheSmileyCoder]

Try setting your SQL of the supReport's recordsource to:

SELECT [Question Bank].ID, [Question Bank].Image
FROM [Question Bank]
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.

[ADezii]

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.

[TheSmileyCoder]

A sup-report has the beauty of only appearing when there is something to show. No need to programmatically change it all.

[ADezii]

Sorry TheSmileyOne, I was under the impression that the Sub-Report approach was not working.

[TheSmileyCoder]

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.

[mb60]

@ADezii
sir
what is that code?

[mb60]

@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

[ADezii]

Try TheSmileyOne's Sub-Form approach first, since creating the code that I previously described would be time consuming to say the least.

[mb60]

@TheSmileyOne
Sorry for disturbing you once again. How to use the code given by you in setting SQL of the sub-report?

[ADezii]

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.

[mb60]

@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.

[TheSmileyCoder]

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.

[mb60]

@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