Problem with Report and Forms

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hello All,

I need some help with a form and report. I have a Report Listing form
that lists all reports for each users security group. There is one
report where the users have to select their name from a field on the
Report Listing form that passess to the report and it all works fine
until they try to print the report. When they try to print the report
it brings up the box from the query where the form is passing in their
name requesting them to enter their name. I think the problem is when
they open the report listing form and select this report then their
name in the drop-down box and press print preview it open the report
and closes the report listing form which had their name selected.

How to do I get this work so they don't have to type in their name
multiple times?

~John
 
Hello All,

I need some help with a form and report. I have a Report Listing form
that lists all reports for each users security group. There is one
report where the users have to select their name from a field on the
Report Listing form that passess to the report and it all works fine
until they try to print the report. When they try to print the report
it brings up the box from the query where the form is passing in their
name requesting them to enter their name. I think the problem is when
they open the report listing form and select this report then their
name in the drop-down box and press print preview it open the report
and closes the report listing form which had their name selected.

How to do I get this work so they don't have to type in their name
multiple times?

~John
Click to expand...

Please post your code and the SQL of the report's recordsource. We can't see
it from here!

You should certainly be able to open a Report referencing a form control (the
comb box (rather than a prompt), but it's not clear from what you post how you
have these set up.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
Please post your code and the SQL of the report's recordsource. We can't see
it from here!

You should certainly be able to open a Report referencing a form control (the
comb box (rather than a prompt), but it's not clear from what you post how you
have these set up.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com
Click to expand...

Here is the code from the form rptDialogSingle that the users use to
select the report they want:


' Module : Form_frmRptDialogSingle
' Author : Sandra Daigle
' (e-mail address removed)
' www.daiglenet.com/msaccess.htm
' Copyright : Sandra Daigle
' Please feel free to use this code
' without restriction in any application you develop,
' whether private or commercial.
'
' This code may not be resold by itself or as
' part of a collection.
' Description:
' Procedures : cmdExit_Click()
' cmdRunRpt_Click()
' Form_Open(pintCancel As Integer)
' ReportID_AfterUpdate()
' ReportID_DblClick(pintCancel As Integer)
' RequeryReportId()
' rptGroupId_AfterUpdate()
' RunReport(pintRptView As Integer)

' Modified : 04/07/03 - 09:17
' Author: Sandra Daigle
' 04/07/03 SMD Cleaned with Total Visual CodeTools 2002
'
' --------------------------------------------------

Private Const mcStrModule As String = "Form_frmRptDialogSingle"
Option Compare Database
Option Explicit

Private Sub cmdExit_Click()
' Comments :
' Parameters: -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo Proc_Err
'TVCodeTools ErrorEnablerEnd
DoCmd.Close acForm, Me.Name

'TVCodeTools ErrorHandlerStart
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "An Error Has occurred" & vbCrLf _
& "Error Number: " & Err.Number & vbCrLf _
& "Error Description" & Err.Description
Resume Next
'TVCodeTools ErrorHandlerEnd

End Sub

Private Sub cmdRunRpt_Click()
' Comments :
' Parameters: -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo Proc_Err
'TVCodeTools ErrorEnablerEnd
If Me.ReportID.ItemsSelected.Count > 0 Then
RunReport intRptMode
Else
MsgBox "Please select a report and then try again."
End If

'DoCmd.Close acForm, Me.Name


'TVCodeTools ErrorHandlerStart
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "An Error Has occurred" & vbCrLf _
& "Error Number 1: " & Err.Number & vbCrLf _
& "Error Description" & Err.Description
Resume Next
'TVCodeTools ErrorHandlerEnd

End Sub

Private Sub Form_Open(pintCancel As Integer)
' Comments :
' Parameters: pintCancel -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo Proc_Err
'TVCodeTools ErrorEnablerEnd
Me.RptGroupID.SetFocus
Me.RptGroupID = Me.RptGroupID.Column(0, 0)
RequeryReportId
Me.ReportID.Requery

'TVCodeTools ErrorHandlerStart
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "An Error Has occurred" & vbCrLf _
& "Error Number 2: " & Err.Number & vbCrLf _
& "Error Description" & Err.Description
Resume Next
'TVCodeTools ErrorHandlerEnd

End Sub

Private Sub ReportID_AfterUpdate()
' Comments :
' Parameters: -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo Proc_Err
'TVCodeTools ErrorEnablerEnd
Me.begdate.Enabled = Me.fUseDateRange
Me.EndDate.Enabled = Me.fUseDateRange
Me.cmdCalDate1.Enabled = Me.fUseDateRange
Me.cmdCalDate2.Enabled = Me.fUseDateRange
Me.Environment.Enabled = Me.fUseEnvironment
Me.Analyst.Enabled = Me.fUseAnalyst

If Me.fUseDateRange = True Then
Me.begdate.SetFocus
End If

'TVCodeTools ErrorHandlerStart
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "An Error Has occurred" & vbCrLf _
& "Error Number 3: " & Err.Number & vbCrLf _
& "Error Description" & Err.Description
Resume Next
'TVCodeTools ErrorHandlerEnd

End Sub

Private Sub ReportID_DblClick(pintCancel As Integer)
' Comments :
' Parameters: pintCancel -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo Proc_Err
'TVCodeTools ErrorEnablerEnd
RunReport acViewPreview

DoCmd.Close acForm, Me.Name

'TVCodeTools ErrorHandlerStart
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "An Error Has occurred" & vbCrLf _
& "Error Number 4: " & Err.Number & vbCrLf _
& "Error Description" & Err.Description
Resume Next
'TVCodeTools ErrorHandlerEnd

End Sub

Private Sub RequeryReportId()
' Comments :
' Parameters: -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo Proc_Err
'TVCodeTools ErrorEnablerEnd
Dim strSQL As String
strSQL = "SELECT tblReports.RptFileName, tblReports.RptName, " _
& "tblReports.RptDescription, tblReports.fDateRange,
tblReports.fEnvironment, tblReports.fUseAnalyst,
tblReports.fUseManager " _
& "FROM tblReports INNER JOIN " _
& "tblRptGroupMembers ON tblReports.RptId =
tblRptGroupMembers.RptId " _
& "WHERE tblRptGroupMembers.RptGroupId=" & Me.RptGroupID & " "
_
& "ORDER BY tblReports.RptFileName; "
Me.ReportID.RowSource = strSQL

'TVCodeTools ErrorHandlerStart
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "An Error Has occurred" & vbCrLf _
& "Error Number 5: " & Err.Number & vbCrLf _
& "Error Description" & Err.Description
Resume Next
'TVCodeTools ErrorHandlerEnd

End Sub

Private Sub rptGroupId_AfterUpdate()
' Comments :
' Parameters: -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo Proc_Err
'TVCodeTools ErrorEnablerEnd
Dim strSQL As String
RequeryReportId
Me.ReportID.SetFocus

'TVCodeTools ErrorHandlerStart
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "An Error Has occurred" & vbCrLf _
& "Error Number 6: " & Err.Number & vbCrLf _
& "Error Description" & Err.Description
Resume Next
'TVCodeTools ErrorHandlerEnd

End Sub

Private Sub RunReport(pintRptView As Integer)
' Comments :
' Parameters: pintRptView -
' Modified : Sandra Daigle - 04/07/03 - 09:17
'
' --------------------------------------------------
'TVCodeTools ErrorEnablerStart
On Error GoTo Proc_Err
'TVCodeTools ErrorEnablerEnd
Dim fOk As Boolean
fOk = True
If fUseDateRange Then
If IsNull(Me.begdate) Then
fOk = False
MsgBox "Please enter the beginning date."
Me.begdate.SetFocus
ElseIf IsNull(Me.EndDate) Then
fOk = False
MsgBox "Please enter the ending date."
Me.EndDate.SetFocus
End If
End If
' If fUseEnvironment Then
' If IsNull(Me.Environment) Then
' fOk = False
' MsgBox "Please select the environment."
' Me.Environment.SetFocus
' End If
' End If
' If fUseAnalyst Then
' If IsNull(Me.Analyst) Then
' fOk = False
' MsgBox = "Please select an analyst for the report."
' Me.Analyst.SetFocus
' End If
' End If
If fOk Then
DoCmd.OpenReport Me.ReportID, pintRptView
DoCmd.Close acForm, Me.Name
End If

'TVCodeTools ErrorHandlerStart
Proc_Exit:
Exit Sub

Proc_Err:
MsgBox "An Error Has occurred" & vbCrLf _
& "Error Number 7: " & Err.Number & vbCrLf _
& "Error Description" & Err.Description
Resume Next
'TVCodeTools ErrorHandlerEnd

End Sub


----------------------------------------------------

Here is the code behind the report


Private Sub Report_Close()
DoCmd.Close acForm, "frmRptDialogSingle"
End Sub

Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True


' Open Sales By Category Dialog
DoCmd.OpenForm "frmRptDialogSingle", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmRptDialogSingle") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub

--------------------------------------------------------------------------


Here is the SQL for the queries that feed the four sub-reports.

SELECT DISTINCT [Reimb Mgmt Activity Log].ID, [Reimb Mgmt Activity
Log].Environment, [Reimb Mgmt Activity Log].[Task Type], [Reimb Mgmt
Activity Log].[Task Description], [Reimb Mgmt Activity Log].Priority,
[Reimb Mgmt Activity Log].[Current Task Status], [Reimb Mgmt Activity
Log].[Task Log Date], DateDiff("d",[Task Log Date],Date()) AS
OPEN_TAT, [Reimb Mgmt Activity Log].[Task Lead], [Reimb Mgmt Activity
Log].[Task DEADLINE_Date (aka Upd Analysis Completed)]
FROM [Reimb Mgmt Activity Log]
WHERE ((([Reimb Mgmt Activity Log].[Current Task Status])<>"99 - Done"
And ([Reimb Mgmt Activity Log].[Current Task Status])<>"98 - Done-IA"
And ([Reimb Mgmt Activity Log].[Current Task Status])<>"99 - InAct")
AND (([Reimb Mgmt Activity Log].[Task Lead])=[Forms]!
[frmRptDialogSingle]![Analyst]))
ORDER BY [Reimb Mgmt Activity Log].Environment, [Reimb Mgmt Activity
Log].[Task Type];

SELECT DISTINCT [Reimb Mgmt Activity Log].ID, [Reimb Mgmt Activity
Log].Environment, [Reimb Mgmt Activity Log].[Task Type], [Reimb Mgmt
Activity Log].[Task Description], [Reimb Mgmt Activity Log].Priority,
[Reimb Mgmt Activity Log].[Current Task Status], [Reimb Mgmt Activity
Log].[Task Log Date], [Reimb Mgmt Activity Log].[CV Assigned], [Reimb
Mgmt Activity Log].[CV DEADLINE Date], [Reimb Mgmt Activity Log].[CV
Completed Date], DateDiff("d",[CV Completed Date],[CV DEADLINE Date])
AS CV_TAT
FROM [Reimb Mgmt Activity Log] RIGHT JOIN [Analyst Report - Open Tasks
by Analyst] ON [Reimb Mgmt Activity Log].[CV Assigned] = [Analyst
Report - Open Tasks by Analyst].[Task Lead]
WHERE ((([Reimb Mgmt Activity Log].[Current Task Status])<>"99 - Done"
And ([Reimb Mgmt Activity Log].[Current Task Status])<>"98 - Done-IA"
And ([Reimb Mgmt Activity Log].[Current Task Status])<>"99 - InAct")
AND (([Reimb Mgmt Activity Log].[CV Assigned])=[Forms]!
[frmRptDialogSingle]![Analyst]))
ORDER BY [Reimb Mgmt Activity Log].Environment, [Reimb Mgmt Activity
Log].[Task Type];

SELECT DISTINCT [Reimb Mgmt Activity Log].ID, [Reimb Mgmt Activity
Log].Environment, [Reimb Mgmt Activity Log].[Task Type], [Reimb Mgmt
Activity Log].[Task Description], [Reimb Mgmt Activity Log].Priority,
[Reimb Mgmt Activity Log].[Current Task Status], [Reimb Mgmt Activity
Log].[Task Log Date], [Reimb Mgmt Activity Log].[LV Assigned], [Reimb
Mgmt Activity Log].[LV Completed Date], [Reimb Mgmt Activity Log].[LV
DEADLINE Date], DateDiff("d",[LV Completed Date],[LV DEADLINE Date])
AS LV_TAT
FROM [Reimb Mgmt Activity Log] INNER JOIN [Analyst Report - Open Tasks
by Analyst] ON [Reimb Mgmt Activity Log].[LV Assigned] = [Analyst
Report - Open Tasks by Analyst].[Task Lead]
WHERE ((([Reimb Mgmt Activity Log].[Current Task Status])<>"99 - Done"
And ([Reimb Mgmt Activity Log].[Current Task Status])<>"98 - Done-IA"
And ([Reimb Mgmt Activity Log].[Current Task Status])<>"99 - InAct")
AND (([Reimb Mgmt Activity Log].[LV Assigned])=[Forms]!
[frmRptDialogSingle]![Analyst]))
ORDER BY [Reimb Mgmt Activity Log].Environment, [Reimb Mgmt Activity
Log].[Task Type];

SELECT DISTINCT [Reimb Mgmt Activity Log].ID, [Reimb Mgmt Activity
Log].Environment, [Reimb Mgmt Activity Log].[Task Type], [Reimb Mgmt
Activity Log].[Task Description], [Reimb Mgmt Activity Log].Priority,
[Reimb Mgmt Activity Log].[Current Task Status], [Reimb Mgmt Activity
Log].[Task Log Date], [Reimb Mgmt Activity Log].[PV Assigned], [Reimb
Mgmt Activity Log].[PV DEADLINE Date], [Reimb Mgmt Activity Log].[PV
Completed Date], DateDiff("d",[PV Completed Date],[PV DEADLINE Date])
AS PV_TAT
FROM [Reimb Mgmt Activity Log] RIGHT JOIN [Analyst Report - Open Tasks
by Analyst] ON [Reimb Mgmt Activity Log].[PV Assigned] = [Analyst
Report - Open Tasks by Analyst].[Task Lead]
WHERE ((([Reimb Mgmt Activity Log].[Current Task Status])<>"99 - Done"
And ([Reimb Mgmt Activity Log].[Current Task Status])<>"98 - Done-IA"
And ([Reimb Mgmt Activity Log].[Current Task Status])<>"99 - InAct")
AND (([Reimb Mgmt Activity Log].[PV Assigned])=[Forms]!
[frmRptDialogSingle]![Analyst]))
ORDER BY [Reimb Mgmt Activity Log].Environment, [Reimb Mgmt Activity
Log].[Task Type];


Thanks for your help,
~John
 
Back
Top