2 Comboboxes on form to open different reports 1

[newestAF]

Again, I've searched but am limited on time to find the answer. Here is my code.

Private Sub Command12_Click()
On Error GoTo Error_Handler

Dim stUnitName As String
Dim stReportName As String
stUnitName = Me.Admin_Unit.Value

If Not [Admin Unit] = "ALL UNITS" Then

If [Report Name] = "Date Rip Sent Report" Then
DoCmd.OpenReport "Date Rip Sent Report", acViewNormal, , "[Unit Description] = '" & stUnitName & "'"
DoCmd.Maximize
End If

If [Report Name] = "PDD Report" Then
DoCmd.OpenReport "PDD Report", acViewNormal, , "[Unit Description] = '" & stUnitName & "'"
DoCmd.Maximize
End If

If [Report Name] = "Unit Report" Then
DoCmd.OpenReport "Unit Report", acViewNormal, , "[Unit Description] = '" & stUnitName & "'"
DoCmd.Maximize
End If

Else
If [Report Name] = "Date Rip Sent Report" Then
DoCmd.OpenReport "Date Rip Sent Report", acViewNormal
DoCmd.Maximize
End If

If [Report Name] = "PDD Report" Then
DoCmd.OpenReport "PDD Report", acViewNormal
DoCmd.Maximize
End If

If [Report Name] = "Unit Report" Then
DoCmd.OpenReport "Unit Report", acViewNormal
DoCmd.Maximize
End If
End If


Exit_Handler:
Exit Sub

Error_Handler:
If Err.Number = 2501 Then Err.Clear

If Not Err.Number = 2501 Then
If [Report Name] = "" Then
MsgBox "Please select type of report before continuing.", vbOKOnly, _
"Title of Message Box"
End If
If [Admin Unit] = "" Then
MsgBox "Please select Unit before continuing.", vbOKOnly, _
"Title of Message Box"
End If

Resume Exit_Handler:

End If

End Sub

******END CODE*****

The reports won't open, instead they print. The error code doesn't work. When I leave either field blank, it does nothing. What am I doing wrong? I've worked on this for two days with no progress.

Thanks in advance.

 

[PHV]

The reports won't open, instead they print
Replace all acViewNormal with acViewPreview

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newestAF]

Actually, I had that in the original code. The reports run if I pick something in both fields. The msgbox codes don't work. If either is blank, it gives me the msgbox for pick a report before continuing.

 

[dhookom]

Rather than 3 If sections, can't you simply use:

DoCmd.OpenReport Me.[Report Name], acViewNormal, , "[Unit Description] = '" & stUnitName & "'"

Also, I expect your control values are Null and not "". Try changing your code to:

 If Me.[Report Name] & "" = "" Then

Duane
Hook'D on Access
MS Access MVP

 

[newestAF]

Both works great. One other problem now. If the Admin Name is selected but not the Report Name, the msgbox won't appear. Also, when my reports appear, it doesn't appear in front.

 

[dhookom]

I would add a section of code early like:

  If IsNull(Me.[Report Name]) Then
     MsgBox "Pick a report",vbOkOnly + vbInformation,"PEBKAC"
  End If

You might want to try open your report acdialog or hide the form.

Duane
Hook'D on Access
MS Access MVP

 

[newestAF]

dhookom,

You are the MAN! Saved me a lot of headache looking through all those If Then statements. Much appreciated.

 

[dhookom]

I think you could get by with something as simple as

Private Sub Command12_Click()
On Error GoTo Error_Handler

Dim stUnitName As String

Dim stReportName As String
Dim strWhere as String

strWhere = "1=1 "
stUnitName = Me.Admin_Unit.Value

If IsNull(Me.[Report Name]) Or IsNull(Me.[Admin Unit]) Then
    MsgBox "Please select type of report and Unit before continuing.", vbOKOnly, _
        "PEBKAC"
  Else
    If Not [Admin Unit] = "ALL UNITS" Then
      strWhere = "[Unit Description] = '" & stUnitName & "'"
    End If

    DoCmd.OpenReport [Report Name], acViewPreview, , strWhere
    DoCmd.Maximize
End If


Exit_Handler:
Exit Sub

Error_Handler:
If Err.Number = 2501 Then Err.Clear

If Not Err.Number = 2501 Then
   MsgBox "something"

Resume Exit_Handler:

End If

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[newestAF]

Now how do I include the sendobject command? Need to be able to send each individual report to the respective agency.

 

[PHV]

What is YOUR actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newestAF]

**************START CODE******************

Private Sub Command22_Click()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim stUnitName As String
Dim stReportName As String
Dim stReportQuery As String
Dim strSQL As String
stUnitName = Me.[Admin Unit].Value
strEmail = rst(0) + Chr(59) & rst(1) + Chr(59) & rst(2) + Chr(59) & rst(3) + Chr(59) & rst(4)

If IsNull(Me.[Report Name]) Then
MsgBox "Please select type of report before continuing.", vbOKOnly
End If

If Not [Admin Unit] = "ALL UNITS" Then

If [Report Name] = "Date Rip Sent Report" Then
strSQL = ("SELECT * FROM [Date Rip Sent Query] WHERE [Unit Description] = '" & stUnitName & "'")
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst

DoCmd.OpenReport Me.[Report Name], acViewReport, , "[Unit Description] = '" & stUnitName & "'"
DoCmd.SendObject acSendReport, "Date Rip Sent Report", "PDF Format (*.pdf)", strEmail, "", "", rst![Agency] + " - OVERDUE ASSIGNMENT NOTIFICATION RIPS - SUSP: 24HRS", "The attached is a listing of all Assignment Notification Rips for your agency that have not been returned to the MPF. Please provide status or submit Rip to Career Development Representative within 24hrs. Thank you.", False
DoCmd.Close acReport, Me.[Report Name]
rst.MoveNext
End If

If [Report Name] = "PDD Report" Then
strSQL = ("SELECT * FROM [PDD Report Query] WHERE [Unit Description] = '" & stUnitName & "'")
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst

Do Until rst.EOF

DoCmd.OpenReport Me.[Report Name], acViewReport, , "[Unit Description] = '" & stUnitName & "'"
DoCmd.SendObject acSendReport, "PDD Report", "PDF Format (*.pdf)", strEmail, "", "", rst![Agency] + " - Projected Departures Within 90 Days", "The attached is a listing of all members assigned to your agency who are projected for PCS/PCA within the next 90 Days. Please ensure members complete all outprocessing actions and contact Career Development Representative NLT 14 days prior to intended Final Outprocessing. Thank you.", False
DoCmd.Close acReport, Me.[Report Name]
rst.MoveNext
Loop
End If

If [Report Name] = "Unit Report" Then
strSQL = ("SELECT * FROM [Unit Report Query] WHERE [Unit Description] = '" & stUnitName & "'")
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst

Do Until rst.EOF

DoCmd.OpenReport Me.[Report Name], acViewReport, , "[Unit Description] = '" & stUnitName & "'"
DoCmd.SendObject acSendReport, "Unit Report", "PDF Format (*.pdf)", strEmail, "", "", rst![Agency] + " - ASSIGNMENT SELECTION LISTING", "The attached is a listing of all members assigned to your agency who have recently been selected PCS/PCA. Please ensure members complete Initial Outprocessing Briefing on Virtual OutProcessing. Please have member direct questions to appointed Career Development Representative. Thank you.", False
DoCmd.Close acReport, Me.[Report Name]
rst.MoveNext
Loop
End If

Else

MsgBox "You have attempted an authorized action. Please contact your system administrator.", vbCritical
Resume Next
End If

DriverExit:
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:

If Me.[Admin Unit] & "" = "" Then
MsgBox "Please select Unit before continuing.", vbOKOnly
End If

'MsgBox Err.Description, vbCritical
Resume DriverExit

End Sub

**************END CODE**********************

I'm getting a syntax error for my SQL statements.

 

[PHV]

I'm getting a syntax error for my SQL statements
Which line of code highlighted when in debug mode ?
What is the "SQL statement" ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newestAF]

Actually, I figured it out.