Access Report Help

[chibearsfan6]

We created a report based off of other reports. Because of this access keeps asking us to input the employee id. We were wondering if anyone knew of any way to eliminate some of these pop ups. I can not attach the report because the file is to large.

[June7]

How do you create report based off of ther reports - subreports?

If you want to provide db for analysis, follow instructions at bottom of my post. If still too large, upload to fileshare site such as box.com and post link to the file.

[chibearsfan6]

Here is the file zipped. The report we based off of other reports is called Test_Employee. The Employee ID we have been using is 1 to test the database. Thank you for your suggestion to zip the file.

[qa12dx]

i am assuming u r basing ur reports off an employeeID. use a unbound textbox on the form where u r triggering the report. textbox property.visible.no
set the id in this textbox and reference your report on the textbox.
soemtimes that is not an option as u r too ahead in ur design and to do this would be more of a pain in the rear. make a copy of ur db, remove most of the data, compact the db, zip it, post, we can take a look. what u attached is a windows shortcut i am assuming, from the file size...

[June7]

Did not provide database. The file would be .mdb or .accdb, not .lnk extension.

[chibearsfan6]

Are you able to open it now?

[June7]

I looked at the report called Test_Report which has multiple subreports. What you need to do:

1. include EmployeeID in the RecordSource for the main report

2. remove the EmployeeID criteria parameter from the subreport queries - could just bind the subreports to tables, queries aren't really needed if no filter/sort criteria are used

3. set the Master/Child links properties of each subreport container control

4. add textboxes in the Detail section bound to fields of the main report RecordSource

I think your data structure could be simplified by combining some tables. For example the 'Service to ...' tables could be one table by adding another field for the service type (Community, School, University, Profession).

What's the difference between data in tables 'Professional Certifications' and 'Professional Development Certificates'?

Advise no spaces, special characters, punctuation (underscore is exception) in names.

'Dates' fields are set as text instead of date/time type.

[chibearsfan6]

Can you please explain to me a little more how to go about steps 3 and 4. setting the Master/Child links properties of each subreport container control and adding textboxes in the Detail section bound to fields of the main report RecordSource.

[June7]

3. Select the subform container control. The Master/Child links properties are on Data tab of Property Sheet dialog. Double click the ellipses (...), set the links to pk/fk fields.

4. Drag textbox control to main report Detail section, set ControlSource property.

[chibearsfan6]

We did what you said previously but now its asking us to input the employee id 2 times. I attached a zipped file with the database if you can see what I mean. Also,say someone has does not have any professional experience how do we get that part of the report to not show up when we run it.

[June7]

The two prompts are because the main report and a subreport are both bound to Test_Employee query. Remove the subreport (the first one in Detail section). Put textboxes bound to fields of the main report in place of the subreport.

Not showing a subreport when there is no data requires code. However, even if the subreport is not visible, blank space will be left in its place because even though not visible the subreport control still occupies space on the report.

[chibearsfan6]

So what would be the best and easiest way to set it up so unused information would not be visiable.

[June7]

Google: Access subreport has no data

Review:

If you don't even want a label for the subreport when there is no data, size the subreport as small as possible, maybe even use code in the main report Detail format event to set subreport container control height property to 0.
http://www.access-programmers.co.uk/...d.php?t=212478

If you still want a label with a textbox that says "No Data".
http://www.tek-tips.com/viewthread.cfm?qid=1529666