• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

Create a report from an existing form?

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
DonStary
Asked:
DonStary
  • 7
  • 6
  • 3
  • +1
3 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
thenelsonCommented:
<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
 
DonStaryAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
thenelsonCommented:
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
 
DonStaryAuthor Commented:
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
 
thenelsonCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
DonStaryAuthor Commented:
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
 
thenelsonCommented:
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
 
DonStaryAuthor Commented:
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
 
thenelsonCommented:
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
 
DonStaryAuthor Commented:
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
 
thenelsonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
DonStaryAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
DonStaryAuthor Commented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 7
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now