Solved

Create a report from an existing form?

Posted on 2009-06-29
18
718 Views
Last Modified: 2013-11-28
I store emergency contact information for my employees in four tables: Employees, Spouses, Children, and Authorized Contacts.  I have a form (with three subforms) that pulls all this information together for each employee.

I need each of my employees to review their information and make any applicable changes.  When I try to print the form, it prints well except that for any employee with no data in one of the subforms, that subform does not print - it prints a blank box instead, without spaces for them to add data if necessary.  I need those blanks (and their labels) to print so that if, for example, an employee did not have any children but now has a child, they can see that there's a space where they should add the child.

From reading other questions and solutions, it looks like using a form is the wrong way for me to go about this.  It looks like forms really aren't designed for printing, and I should be using a report.  My question then is, can I use the existing form to create a printable report that looks just like it?  I've spent a lot of time getting the form right, and I can't imagine starting from scratch again to create a report with the same box sizes, fonts, etc.

Please tell me there's an easier way?  :)  Thank you.

A screenshot of my blank form is attached.
blank-form.jpg
0
Comment
Question by:DonStary
  • 7
  • 6
  • 3
  • +1
18 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 50 total points
ID: 24740658
You can save a copy of your Form as a Report, which should preserve the majority of your formatting and layout.

In the database window, select the report, right click and select Save As, then select Report, then enter a name for your Report.

As to making blank info ... that's difficult to do, but you could enter a blank record for those employees when they added, at which point Access would print a blank record.
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24740682
<My question then is, can I use the existing form to create a printable report that looks just like it?>
Not that I know of but you can copy a lot of the work. Create a blank report. Open your form in design mode. Click in a section of the form. Press Ctrl A to select all, Ctrl C to copy. Click in the same section of the report, Press Ctrl V to paste. Do that for each section, (header, detail, etc. )
0
 

Author Comment

by:DonStary
ID: 24740762
LSMConsulting, thank you for the "Save As" tip - if it was a snake, it would've bit me!  Unfortunately, it's giving me the same problem as the original form: It doesn't display the subforms unless they contain data.  As for entering a blank record to make those subforms display - I have two issues with this: (1) I have more than 200 current employees and constant turnover, so this would take a lot of extra time, and (2) I export my table data to a web-based emergency notification system, and the system will reject my upload if there are blank/incomplete records in it.

the nelson, when I copy/paste my form into a blank report and then display the report, all the boxes are filled in with "#Name?" instead of the corresponding data.  Is there another step involved?  (I'm a bit of a Reports novice.)

Thank you both...
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24740894
I like Scott's (LSMConsulting) idea.

As far as the space, can it be blank space?  Try creating vertical line next to the controls in the sub report to force the space.
0
 

Author Comment

by:DonStary
ID: 24741047
thenelson - No, I guess I'm asking a lot of Access, but I want it to print the blank subform, with the appropriate blanks and labels.  That way, if someone previously had no information in that category, they could write it in.  For example, an employee might not have had any children when their original form was completed, so they "Children" subform would not display.  They might, however, have a child now.  If there's no Children subform, they won't know they need to add their child's information.  If there's a Children subform with blanks and labels, they will know what information I need.

Thanks!
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24741279
Off the top of my head I think:
you could create a query for the form's or report's record source that adds a blank record for anyone that does not have the record or

you could add coding to the form or report that forces display of the blank controls.

0
 
LVL 84
ID: 24741459
Or you could add a "dummy" table that contains a blank record, and then use that table when you need to print a Blank form.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24743424
DonStary,

Along the same lines, you can also create a blank, hidden subReport.

You can toggle the visibility of this hidden subreport based on if the there is a child record present for the current Parent record.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If DCount("*", "tblAuthContact", "EmployeeID=" & Me.EmployeeID) = 0 Then
        Me.rptAuthContactsBlank.Visible = True
        Me.rptAuthContactSub.Visible = False
    Else
        Me.rptAuthContactsBlank.Visible = False
        Me.rptAuthContactSub.Visible = True
    End If
   
End Sub

Here is a simple basic sample

JeffCoachman
Access-EEQ24531430-BlankEmptySub.mdb
0
 

Author Comment

by:DonStary
ID: 24748212
To thenelson, LSMConsulting, and boag2000,

Thank you all for your help.  I've reached the threshold of my Access understanding, so I would need some help to try any of these last three suggestions.  

thenelson, how or where would I add coding to force display of blank controls?  I've done VERY little work in SQL view (I've created a union query, with help from a book about Access).  Is SQL view where I would add the code?

LSMConsulting, I'm not sure if a dummy/blank table would work because I'm not aiming for a completely blank form - just a partially blank form.

boag2000, I understand the concept of what you're saying, and it sounds like it would work - but where do I put this code?  I apologize, but you'll have to "dumb it down" for me.  Would I need to create additional components (forms, queries, and/or reports) in my database, or just add this code to an existing form or report?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 39

Expert Comment

by:thenelson
ID: 24748734
Of the three, I think Jeff's idea would be the easiest to apply.

Make a copy of the report that is in the sub report that doesn't show up. To do that, go to the database window, Reports. Click on the report, press Ctrl-C, press Ctrl-V And name the copy. Open the copy in design mode. Change the control source of all the text boxes, Comboboxes, etc to =" " (a space between the quotes).  Insert this copy on top of the other sub report.and use Jeff's code to swap them.
0
 

Author Comment

by:DonStary
ID: 24749593
Great, thank you, thenelson.  So to make sure I fully understand...

Right now I have a form with three subforms (four forms total).  I need to perform a "Save As" on each of these four forms to create a report and three subreports (or four *reports* total).  THEN I need to copy my three subreports and create "blank" versions of them by setting the control source to =" ".  Next I need to place my "blank" subreports on top of my "real" subreports in Design View.  Finally, I need to use boag2000's code to tell Access to display the "blank" subreport if there is no data in the "real" subreport.

Final question: Where do I put boag2000's code?

(I am upping the points on this question since it has proven to be tricky, and I want to split points among those who have helped.  We almost have a solution!)
0
 
LVL 39

Expert Comment

by:thenelson
ID: 24749722
As Jeff (boag2000) indicated by the
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
the code would go in the report's detail format event.

Glad you are planning to split the points. It has been a joint effort.
0
 

Author Comment

by:DonStary
ID: 24750472
Okay, I'm stuck, and I feel like I'm so close!  I'm putting this in the report's detail format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If DCount("*", "Spouses", "EmployeeID=" & Me.EmployeeID) = 0 Then
        Me.SpousesSubreportBlank.Visible = True
        Me.SpousesSubreport.Visible = False
    Else
        Me.SpousesSubreportBlank.Visible = False
        Me.SpousesSubreport.Visible = True
    End If
   
End Sub

It's giving me a "Compile Error" (see screenshot) when I try to run it.  I don't really work with code, so I'm trying my best to understand this.  In this code, "Spouses" is the table that contains the spouses, and "EmployeeID" is the field that links the Employees (parent) table to the Spouses (child) table.
Compile-error.jpg
0
 
LVL 39

Accepted Solution

by:
thenelson earned 225 total points
ID: 24751174
The message is telling you it cannot find the subform (subreport) containers you listed. The objects that need to be referenced are the subform (subreport) containers not the forms (reports) within the containers.  The first time you click on a subform in design mode, the container is selected as evidenced by the 8 sizing handles in the corners and on the sides (see the image below). That is when you want to copy the name from the name field to Your code. The next time you click on the subform (subrepot), the form (report) within the container is selected which is evidenced by a black square within the square in the upper right corner (see second image).
Form-Container-Selection.jpg
Form-Selection.jpg
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 225 total points
ID: 24752703
To all,

Obviously it seems that thenelson is doing a better job of explaing my solution than I could have.
;-)

The sample is fully functional you can take the info there and try to apply it to your database.

Basically I created a "Blank" Subreport by copying the existing subreport and clearing out all the Textbox control sources.
I then inserted the blank subreport into the main report.
Then I slightly staggered the two subreports so that it would be obvious that there were two of them.
(Not appear as one, if they were stacked.
So you could not accidentally drag select both and modify both, and not know it)

;-)

Jeff
untitled.JPG
0
 

Author Comment

by:DonStary
ID: 24756530
My Design View doesn't look anything like either of yours (see screenshot).  In the screenshot, I single-clicked the Sposues subreport (you can see the eight resizing handles).  It's saying the name is "SpousesSubreport."  I substituted that into the code (see second screenshot), but it's still giving me the same Compile Error.  It looks like it's hanging up on "EmployeeID," which is the field in both tables (pareent & child) that ties the Employees to their Spouses.  I don't have EmployeeID visible on the report or the subreport - is this part of the problem?  Do I need to show it?
Design-View.jpg
Compile-Error-Again.jpg
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24757019
Do you have a *Control* named EmployeeID on the Main Report?
Is the name of the Field in the table "EmpoyeeID"?

Again, my sample is fully functional, I made it very basic so you could see exactly what was happening.

Perhaps you should start off simple, with just the Employee Main Report and one Subreport.

JeffCoachman

0
 

Author Closing Comment

by:DonStary
ID: 31694448
I thank you all for your assistance and apologize for inadvertently leaving this question open.  I was never able to make this solution work.  Instead, I was able to construct a form in InfoPath that allowed my personnel to update their records electronically.  I think this solution did not work for me due to my limited knowledge, not due to any problem with your expert comments.  Thanks again.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now