Access 16 - Report Displaying Selections Without Data
Hi Experts,
Please see the file for the details described below.
I am creating a report to contain Name (Selection 10), Communication (Selection 50) and Address (Selection 60) data. Example 1 shows how the report looks in Report Preview. Example 2 shows how the report looks in Design View.
This example of the report has three Sections. i.e. One Name, two Communication and two Addresses. The number of Communications and Addresses may be from zero to ‘n’ each.
The data for the report is extracted via VB Code to a Generic Report Table used for reporting and the report is generated directly from the table. For the example there are four records created. i.e. Two for the Communication (Selection 50) and two for the Address (Selection 60). The Name (Selection 10) data is included in all four records.
Some of the Selections display without any data. Is there a way to stop the Selections without any data from displaying?
Due to the complexity of the report it is not easy for me to provide the database and code, however it can be done as it is not a large application.
Thanks,
Bob C.
Please see the file for the details described below.
I am creating a report to contain Name (Selection 10), Communication (Selection 50) and Address (Selection 60) data. Example 1 shows how the report looks in Report Preview. Example 2 shows how the report looks in Design View.
This example of the report has three Sections. i.e. One Name, two Communication and two Addresses. The number of Communications and Addresses may be from zero to ‘n’ each.
The data for the report is extracted via VB Code to a Generic Report Table used for reporting and the report is generated directly from the table. For the example there are four records created. i.e. Two for the Communication (Selection 50) and two for the Address (Selection 60). The Name (Selection 10) data is included in all four records.
Some of the Selections display without any data. Is there a way to stop the Selections without any data from displaying?
Due to the complexity of the report it is not easy for me to provide the database and code, however it can be done as it is not a large application.
Thanks,
Bob C.
Are you using report view? That will sadly allways show the empty record. But print preview and normal view should filter it out
ASKER
Hi Anders,
I always use Print Preview so the User can see the report before printing it.
Thanks,
Bob C.
I always use Print Preview so the User can see the report before printing it.
Thanks,
Bob C.
I don't see any attachments.
Jim.
Jim.
ASKER
Hi Jim,
As I noted as soon as I opened the question, I can't figure out how to do an attachment.
Please let me know.
PS: I also now have a code version to upload.
Thanks,
Bob C.
As I noted as soon as I opened the question, I can't figure out how to do an attachment.
Please let me know.
PS: I also now have a code version to upload.
Thanks,
Bob C.
Sorry, didn't see the 2nd comment.
then from here:
either drag and drop files onto the box, or click the box to get a open file dialog box.
Jim.
then from here:
either drag and drop files onto the box, or click the box to get a open file dialog box.
Jim.
ASKER
Hi Experts,
Here is the original question document plus a copy of the pertinent part of the application.
To get to the report at issue navigate as follows.
- On the Welcome Form click the M-00-000 - Contact Management Menu.
- Click on the F-10-010 - Personal Contact Management button
- Select the Reports Tab.
- Click the Printer Icon to preview the report.
The Printer Icon executes an event to specify the Report Name and then calls a function (top of the VB Code Stack) to extract the data to the 00_SELCTION_DATA Table. The report then is run from the the table.
PS: I use this method for all my reports as I have found it works extremely well.
Thanks,
Bob C.2020-09-22 - Report Displaying Selections Without Data.docxCMS.accdb
Here is the original question document plus a copy of the pertinent part of the application.
To get to the report at issue navigate as follows.
- On the Welcome Form click the M-00-000 - Contact Management Menu.
- Click on the F-10-010 - Personal Contact Management button
- Select the Reports Tab.
- Click the Printer Icon to preview the report.
The Printer Icon executes an event to specify the Report Name and then calls a function (top of the VB Code Stack) to extract the data to the 00_SELCTION_DATA Table. The report then is run from the the table.
PS: I use this method for all my reports as I have found it works extremely well.
Thanks,
Bob C.2020-09-22 - Report Displaying Selections Without Data.docxCMS.accdb
It sounds like you only need to apply the shrink & grow properties which are already provided in Ms Access , no VBA code is required.
Nothing happens when I click the print preview button
But where is your database?
ASKER
Hi Experts,
Firs my bad. I left the 00_SELECTION_DATA Table as linked when it should have been Local.
Here is the corrected copy to be used that I believe will work Ok.
CMS.accdb
BTW: Is there a way to delete the original incorrect copy? Please advise.
The suggestion to use the 'Shrink' Property doesn't work for Labels which is where the problem is with them displaying when there is no data associated with them.
Thanks,
Bob C.
Firs my bad. I left the 00_SELECTION_DATA Table as linked when it should have been Local.
Here is the corrected copy to be used that I believe will work Ok.
CMS.accdb
BTW: Is there a way to delete the original incorrect copy? Please advise.
The suggestion to use the 'Shrink' Property doesn't work for Labels which is where the problem is with them displaying when there is no data associated with them.
Thanks,
Bob C.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Bob
I've had another look at your database. I hope I can say this without offending you, because that is not my intent. I would recommend you look for a basic Access course somewhere near you. You are doing a lot of things much more complicated than they need to be. A good introduction to access can help you design faster, as well as making code that is much easier to maintain.
E.g. your report. Instead of combining all the data into 1 table, you should use reports bound to the main table, and sub-reports bound to child tables.
E.g. Use a autonumber for primary key, don't use a primary key that is created by combining several other fields.
I cannot see a way forward to fix your report because the underlying data structure is simply not normalized.
I've had another look at your database. I hope I can say this without offending you, because that is not my intent. I would recommend you look for a basic Access course somewhere near you. You are doing a lot of things much more complicated than they need to be. A good introduction to access can help you design faster, as well as making code that is much easier to maintain.
E.g. your report. Instead of combining all the data into 1 table, you should use reports bound to the main table, and sub-reports bound to child tables.
E.g. Use a autonumber for primary key, don't use a primary key that is created by combining several other fields.
I cannot see a way forward to fix your report because the underlying data structure is simply not normalized.
ASKER
Hi Anders,
No offence taken. Generally I am ok with Tables and Forms but have always struggled with Reports. I prefer to work with VB Code rather than queries to extract / manage data.
The concept of Main / Sub-Reports is very interesting. Can you suggest a book or on-line tutorial particularly for this approach.
Thanks,
Bob C.
No offence taken. Generally I am ok with Tables and Forms but have always struggled with Reports. I prefer to work with VB Code rather than queries to extract / manage data.
The concept of Main / Sub-Reports is very interesting. Can you suggest a book or on-line tutorial particularly for this approach.
Thanks,
Bob C.
ASKER
Hi Chris,
I like your suggestion with using 'Shrink' for Labels as well as the Data.
However I'm not sure:
- What exactly is the 'control record source' and
- How to interprete the function as follows:
IIF(Quantitysold <> " ", "Sales Qty ","")
Could you please clarify?
Thanks,
Bob C.
I like your suggestion with using 'Shrink' for Labels as well as the Data.
However I'm not sure:
- What exactly is the 'control record source' and
- How to interprete the function as follows:
IIF(Quantitysold <> " ", "Sales Qty ","")
Could you please clarify?
Thanks,
Bob C.
The Access For Dummies series is a quite good starting point. It gives a good quick understanding without taking several days to read, and they are usually not that expensive.
I like your suggestion with using 'Shrink' for Labels as well as the Data.
However I'm not sure:
- What exactly is the 'control record source' and
- How to interprete the function as follows:
IIF(Quantitysold <> " ", "Sales Qty ","")
Could you please clarify?
Kindly see the screen shoot below:
However I'm not sure:
- What exactly is the 'control record source' and
- How to interprete the function as follows:
IIF(Quantitysold <> " ", "Sales Qty ","")
Could you please clarify?
Kindly see the screen shoot below:
ASKER
Hi Anders,
I have Office 2016 For 10 In 1 (that includes Access) and also Access 2016 Bible. I didn't look at the Dummys book but I will. I did spend some time going through the Bible but it didn't provide information regarding my specific needs.
Thanks,
Bob C.
I have Office 2016 For 10 In 1 (that includes Access) and also Access 2016 Bible. I didn't look at the Dummys book but I will. I did spend some time going through the Bible but it didn't provide information regarding my specific needs.
Thanks,
Bob C.
ASKER
The Example File is not attached as I can't figure out how to do it.
Please advise.
Thanks,
Bob C.