I currently have a form that contains a combobox with all the employee names. The combo box calls to a table that contains all of the employee records and generates a report based on the selected employee
I have two buttons, export individual report which is done by selecting an employee from the combo box and once the button is clicked exports the report to PDF to the path written
The second button exports all reports as individual reports to the path written (since there are hundreds of employees it would take too much time to export each individually). The PDF is saved by lastname_firstname (column2 and column 1) of the combo box.
Now the second button labels the PDFs correctly as it loops through the combo box, but each time the report is opened the code does not register which employee is being selected and does not update the report with the correct employee records. All of the reports are saved as blanks.
I added the code below, can someone please help figure out a way for when the Report opens it recognizes which employee it is on in the combobox and updates before opening!
Thank you -
Private Sub cmdExportAllToPDF_Click()
-
-
-
Dim intCount As Integer
-
Dim cmbCount As Integer
-
-
-
For intCount = 0 To cmbReportEmployee.ListCount - 1
-
-
DoCmd.OpenReport "Letter Report", acViewPreview, , , acWindowNormal
-
DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", "C:\Users\username\Documents\Stock Letter Reports\" + Forms!ReportOpener!cmbReportEmployee.Column(1, intCount) & "_" & Forms!ReportOpener!cmbReportEmployee.Column(2, intCount) & ".pdf", False, "", , acExportQualityPrint
-
DoCmd.Close acReport, "Letter Report", acSaveNo
-
-
-
Next
-
-
-
-
End Sub
15 1295
I suspect that your problem is that your OpenReport has no filter with it.
Try a Where clause or a Filter to open the report for the correct Employee. Based on your Forms!ReportOpener!cmbReportEmployee.Column(1, intCount & Forms!ReportOpener!cmbReportEmployee.Column(2, intCount)...
Phil
PhilOfWalton,
Yes, that is what I am trying to figure out. Should I call to the table that contains the records and include the column and inCount of the combobox to match to the records?
OK. First I suspect that what you are calling a Combo box is in fact a multi select list box (List0).
Columns are EmpSurname, EmpFirstName & EmpID
Try this code as a basis -
Private Sub Command2_Click()
-
-
Dim itm As Variant
-
Dim Fltr As String
-
-
For Each itm In Me.List0.ItemsSelected
-
Fltr = "EmpID = " & List0.Column(2, itm)
-
DoCmd.OpenReport "Letter Report", acViewPreview, , Fltr
-
Debug.Print Reports!Letter Report.EmpSurName & " " & Reports!Letter Report.EmpFirstName
-
DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", _
-
"C:\Users\username\Documents\Stock Letter Reports\" & Reports!Letter Report.EmpSurName & "_" & Reports!LetterReport.EmpFirstName & ".pdf", False, "", , acExportQualityPrint
-
DoCmd.Close acReport, "Letter Report"
-
Next itm
-
-
End Sub
-
Phil
Ok thanks. I'll try this out. I just checked my form and it is a combo box not a multi select list box.
I had the impression that you wanted to select say half a dozen employees out of all your employees and create a PDF for those selected employees.
If you're doing it from a Combo box, you can only do one at a time. Using a list box allows you to do a "Batch (could be only 1)
Phil
Oh sorry no. I was trying to loop through all the employees and create individual PDF for each, and you're saying that is not possible with a combo box?
Also, I forgot to mention my 'Letter Report' has two subreports which displays two tables of the employee records(which updates when the employee name is selected from the combo box)
If you want to print a report for all Employees, what is the significance of the Combo Box?
This code should print out all Employees -
Private Sub Command5_Click()
-
-
Dim MyDb As Database
-
Dim EmployeeSet As Recordset
-
Dim Fltr As String
-
-
Set MyDb = CurrentDb
-
Set EmployeeSet = MyDb.OpenRecordset("SELECT Employee.* FROM Employee ORDER BY EmpLastName")
-
-
With EmployeeSet
-
Do Until .EOF
-
Fltr = "EmployeeID = " & !EmployeeID
-
DoCmd.OpenReport "Letter Report", acViewPreview, , Fltr
-
Debug.Print Reports!Letter Report.EmpSurName & " " & Reports!Letter Report.EmpFirstName
-
DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", _
-
"C:\Users\username\Documents\Stock Letter Reports\" & Reports!Letter Report.EmpSurName & "_" & Reports!LetterReport.EmpFirstName _
-
& ".pdf", False, "", , acExportQualityPrint
-
DoCmd.Close acReport, "Letter Report"
-
.MoveNext
-
Loop
-
.Close
-
Set EmployeeSet = Nothing
-
End With
-
-
End Sub
-
Phil
The significance of the combo box was to save/print each employee individual but to loop through rather than having to save/print each report manually.
I was working on the original code you provided that used a multi select list box. I created a list box with the same fields as the combo box.
However, the report is still not updating with the records based on the employee selected. It may have to do with the fact that "Letter Report" has two subreports which should be updated with the employees records based on selection.
Try removing the subreports as a test
Phil
I deleted the subreports and got a run time error '2491'
The actions or methis is invalid because the form or report isn't bound to a table or query.
I tried your original way again with the list box and now I am getting an error with my Debug.Print line (I added the subreports back) -
-
Private Sub cmdExportAllToPDF_Click()
-
-
-
Dim itm As Variant
-
Dim Fltr As String
-
-
-
-
For Each itm In Me.lstReportEmployee.ItemsSelected
-
-
Fltr = "Employee ID" & lstReportEmployee.Column(0, itm)
-
-
DoCmd.OpenReport "Letter Report", acViewPreview, , Fltr
-
Debug.Print Reports![Letter Report].[Last Name] & " " & Report![Letter Report].[First Name]
-
DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", "C:\Users\username\Documents\Stock Letter Reports\" & Reports![Letter Report].[Last Name] & "_" & Reports![Letter Report].[First Name] & ".pdf", False, "", , acExportQualityPrint
-
DoCmd.Close acReport, "Letter Report"
-
-
-
Next itm
-
-
-
End Sub
-
What is the RecordSource of your main Report?
The main report doesn't have a record source. It is mainly a place holder/template for the two subreports.
The main report has 3 paragraphs of text for the formal letter followed by the two subreports which should display the employee records
Try setting the RecordSource to a query based on Employees. I would have expected the subreports to be linked to the main report (to keep them syncronised)
Phil
zmbd 5,501
Expert Mod 4TB
Phil, sounds like ERP1201 is attempting JForbes' solution Multiselect list box as query criteria?>Post#2; however, in this post ERP1201 has a bit more information about the project which stands to reason given the aging of the threads.
ERP1201:
+ Runtime 2491 most likely indicates that you still had something pointing to the removed subreports.
+ Post#12
Line#15: missing an "S" Report![Letter Report].[First Name] should be Reports![Letter Report].[First Name]
Without the actual error you are getting it's impossible to tell if that is your issue or if it's a simple typo when you posted the code.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Sreya |
last post by:
Hello..I hope someone can help me with this little that I've been
having for a while. I have a form..which has many fields. one of them
being a test #. what I need a combo box with only certain...
|
by: martin |
last post by:
I have this statement
DoCmd.OpenReport "test", acViewPreview, , " = " & Me.MemberID
to print only the current record. It will pop up a box to define MemberID
every time it is run. What am I...
|
by: project |
last post by:
I want to print an sales invoice with out open crystal report.
I want to know ,with out opening Crystal report, how can I give the
command to be printing invoice.
I'm using the following lines...
|
by: zafar |
last post by:
hi,
I have a table "Tbl_student" and there is field for picture in it
which saves the path of student photo from hardisk. I want the photos
of students on the Crystal report when I need to print a...
|
by: prakashsurya |
last post by:
Hi
i am having a small problem with combo boxes
actually i am preparing a form
in which i have to go through the experience in years
for that i am using 4 combo boxes.
on the first two combo...
|
by: Phil Stanton |
last post by:
I am opening a report (in Preview) from a menu system
I use the following code if there is no data in a report
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no errors in...
|
by: pacarv |
last post by:
I've created a database in Access 2003 with multiple reports that are accessable from the switchboard. The switchboard also has a print button. The user selects the report from the menu which then...
|
by: Dave0291 |
last post by:
Hi there,
I need help with a report I am trying to build for work. I have a simple for called "AuditTotal" with two Date/Time fields "start_Date" and "end_Date" and an open report button. My...
|
by: trivanka |
last post by:
How to open report directly in Access 2010 when we open the file?
Under options - Current Database, I see Display Form.
Do I need to write a VBA code for this and if so, which event.
I also...
|
by: Aftab Ahmad |
last post by:
Hi!
I have a button namely "Open Report" on my form "Orders". I want to open report i.e., "Order Detail" through VBA. I know "DoCmd" is nicely working but I want to show only current record which...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |