By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

Print Preview Multiple Reports (all Employees within an Office)

P: 1
I would like to print preview a report that is a compilation of individual reports for each employee within an office. The sequence is to select an office - this function should select all employees within the office. Unfortunately, it is only returning one employee to print preview. I am not sure where I am going wrong, however, I have a feeling it is within the loop, just not sure how to correct.

Private Sub confirmed_Click()
Dim stDocName As String
Dim ctrl As Control, frm As Form, tName As Variant, BranchName As Variant
stDocName = "teller_perf_std"
Set frm = Forms!emp_chooser

If frm!sel_teller = True Then
Set ctrl = frm!teller_list
DoCmd.OpenForm "R_menu", acNormal
Forms!R_menu!eid.Enabled = True

For Each tName In ctrl.ItemsSelected
Forms!R_menu.eid = ctrl.ItemData(tName)
DoCmd.OpenReport stDocName, acNormal
Next
End If

If frm!sel_branch = True Then

If IsNull(frm!branch_sel.Column(0)) Then
MsgBox "You did not select a branch. Please do so and try again. I might not be so polite next time!"
DoCmd.Close acForm, "confirm_teller", acSaveNo
Exit Sub
End If

DoCmd.OpenForm "R_menu", acNormal
Forms!R_menu!eid.Enabled = True
BranchName = frm!branch_sel.Column(0)

Dim Acnn As ADODB.Connection
Dim rstTellers As ADODB.Recordset
Dim SQLcmd As ADODB.Command
Dim sql As String

sql = "SELECT [Employee ID], [no longer employed] FROM [Teller Database] WHERE [Office ID]='" + BranchName + "' and ([no longer employed]= No)"
Set Acnn = New ADODB.Connection
Acnn.Open CurrentProject.Connection
Set rstTellers = New ADODB.Recordset
Set SQLcmd = New ADODB.Command
Set SQLcmd.ActiveConnection = Acnn
SQLcmd.CommandText = sql
Set rstTellers = SQLcmd.Execute

Do While Not rstTellers.EOF
Forms!R_menu!eid = rstTellers![Employee ID]
DoCmd.OpenReport stDocName, acPreview
rstTellers.MoveNext
Loop

rstTellers.Close
Acnn.Close
End If

DoCmd.Close acForm, "confirm_teller", acSaveNo
DoCmd.Close acForm, "Emp_chooser", acSaveNo
End Sub
Mar 13 '17 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.