DonStary
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
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.
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.
ASKER
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!
Thanks!
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.
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.
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.
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.Vi
Me.rptAuthContactSub.Visib
Else
Me.rptAuthContactsBlank.Vi
Me.rptAuthContactSub.Visib
End If
End Sub
Here is a simple basic sample
JeffCoachman
Access-EEQ24531430-BlankEmptySub.mdb
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.Vi
Me.rptAuthContactSub.Visib
Else
Me.rptAuthContactsBlank.Vi
Me.rptAuthContactSub.Visib
End If
End Sub
Here is a simple basic sample
JeffCoachman
Access-EEQ24531430-BlankEmptySub.mdb
ASKER
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?
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?
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.
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.
ASKER
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!)
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!)
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.
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.
ASKER
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.V
Me.SpousesSubreport.Visibl
Else
Me.SpousesSubreportBlank.V
Me.SpousesSubreport.Visibl
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
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If DCount("*", "Spouses", "EmployeeID=" & Me.EmployeeID) = 0 Then
Me.SpousesSubreportBlank.V
Me.SpousesSubreport.Visibl
Else
Me.SpousesSubreportBlank.V
Me.SpousesSubreport.Visibl
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Design-View.jpg
Compile-Error-Again.jpg
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
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
ASKER
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.
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. )