2 buttons 2 outputs with 1 report

[Thompyt]

How would I use 2 command buttons denoting separate output reports. Using 1 button for 1 query, but using 1 report?



I understand the field names need to be the same in each query, but there are separate filters to get different data.

Open CCB:

SELECT tblChangeRequest.CRID, qryLookup.CRNumbers, qrySwitching.Levels, qrySwitching.DateIDs, qrySwitching.Status, tblChangeRequest.ChangeType, qrySwitching.HBVers, qrySwitching.Units, qrySwitching.MTOEParas, qrySwitching.People, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.ActionComplete, tblChangeRequest.NIE, qrySwitching.DaysOpen, qrySwitching.Levelz, tblChangeRequest.AOVote, qrySettings.CCB, qrySwitching.CRLevel, tblChangeRequest.Change, tblChangeRequest.Level

FROM ((qrySwitching INNER JOIN tblChangeRequest ON qrySwitching.CRID = tblChangeRequest.CRID) INNER JOIN qrySettings ON tblChangeRequest.CRID = qrySettings.CRID) INNER JOIN qryLookup ON tblChangeRequest.CRID = qryLookup.CRID

WHERE (((tblChangeRequest.ActionComplete)=False) AND ((qrySwitching.Levelz)<>"Level 1") AND ((tblChangeRequest.AOVote)<>"Open" And (tblChangeRequest.AOVote)<>"Defer") AND ((tblChangeRequest.SubNo) Is Not Null));

Apen AORB:

SELECT qryLookup.CRNumbers, tblChangeRequest.CRID, tblChangeRequest.NIE, tblChangeRequest.ChangeType, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.Change, qrySwitching.Status, qrySwitching.MTOEParas, qrySwitching.People, qrySwitching.Units, qrySwitching.HBVers, qrySwitching.CRLevel, qrySwitching.DateIDs, qrySwitching.DaysOpen, tblChangeRequest.Change, IIf([FinalVote]<>"","     Date Closed: " & Format([DateClosed],"Long Date"),Null) AS DClosed, tblChangeRequest.SubNo

FROM (tblChangeRequest INNER JOIN qryLookup ON tblChangeRequest.CRID = qryLookup.CRID) INNER JOIN qrySwitching ON tblChangeRequest.CRID = qrySwitching.CRID

WHERE (((tblChangeRequest.ActionComplete)=False))

ORDER BY tblChangeRequest.CRID;

I also have the following I would like to combine & or minimalize:

Note: I have to use text for the email body, not rich text or HTML.

Public Sub SendOpenCCB_Click()
 Dim rs As DAO.Recordset
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem, objOutlookMsg1 As Outlook.MailItem, objOutlookMsg2 As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim objOutlookRecip As Outlook.Recipient
 Dim strMsg As String, MsgChanges As String, TELCodes As String
 Dim CRNum As Variant, DateTypes As Variant, GrpEMails As Variant, MnDay As Variant, NextWed As Variant
  
  On Error GoTo ErrorMsgs
  
  Set objOutlook = CreateObject("Outlook.Application")
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  Set objOutlookMsg1 = objOutlook.CreateItem(olMailItem)

DoCmd.OpenQuery "qryDailyUpdate"
DoCmd.OpenQuery "qryRollupUpdate"

CRNum = DLookup("[CRNumbers]", "[qryOpenCCB]")
NextWed = DLookup("[CCB]", "[qrySettings]")
NIE = DLookup("[NIE]", "[tblChangeRequest]")

If IsNull(CRNum) Then
  With objOutlookMsg
   .Subject = "There are no Open " & NIE & " CCB CR's for " & NextWed
   .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
           "There are no " & NIE & " CCB Change Request actions for the " & NextWed & " CCB." & SigBlock
    .To = "CCB Results"
   .Display
    DoCmd.Close acReport, "rptOpenCCB"
    
Exit Sub
End With
Else
End If

With objOutlookMsg1
  .Subject = "Current Open " & NIE & " CCB CR's - " & NextWed
  .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
          "Dial in - " & Telcodes & vbCrLf & vbCrLf & "The attached CRs are available for the " & NextWed & " CCB." & SigBlock
   DoCmd.OutputTo 3, "rptOpenCCB", acFormatPDF, "C:\Temp\" & Tod & " " & NIE & " CCB Open Changes - " & NextWed & ".pdf", , 0
  .Attachments.Add ("C:\Temp\" & Tod & " " & NIE & " CCB Open Changes - " & NextWed & ".pdf")
  .To = "CCB Results"
  .Display
  Kill "C:\Temp\" & Tod & " " & NIE & " CCB Open Changes - " & NextWed & ".pdf"
  DoCmd.Close acReport, "rptOpenCCB"
  End With
 Set objOutlookMsg = Nothing
 Set objOutlookMsg1 = Nothing
 Set objOutlook = Nothing
 Set objOutlookAttach = Nothing
  Exit Sub

ErrorMsgs:
 If Err.Number = "287" Then
  MsgBox "You selected No to the Outlook security warning. Rerun the procedure and click Yes to access e-mail addresses to send your message."
 Else
 MsgBox Err.Number & " " & Err.Description
End If
End Sub

Public Sub SendAOOpen_Click()
 Dim rs As DAO.Recordset
 Dim objOutlook As Outlook.Application
 Dim objOutlookMsg As Outlook.MailItem, objOutlookMsg1 As Outlook.MailItem, objOutlookMsg2 As Outlook.MailItem
 Dim objOutlookAttach As Outlook.Attachment
 Dim objOutlookRecip As Outlook.Recipient
 Dim strMsg As String, MsgChanges As String, TELCodes As String
 Dim CRNum As Variant, DateTypes As Variant, GrpEMails As Variant, MnDay As Variant, NextWed As Variant

  On Error GoTo ErrorMsgs

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

DoCmd.OpenQuery "qryDailyUpdate"
DoCmd.OpenQuery "qryRollupUpdate"

CRNum = DLookup("[CRNumbers]", "[qryOpenCRs]")
DateTypes = DLookup("[DateType]", "[qrySettings]")
MnDay = DLookup("[DayType]", "[qrySettings]")
GrpEMails = DLookup("[GrpEMail]", "[qrySettings]")
TELCodes = DLookup("[TELCode]", "[qrySettings]")

If IsNull(CRNum) Then
With objOutlookMsg
   .Subject = Tod & "  There are no " & NIE & " Open CR's for " & DateTypes & "."
   .Body = "There are no " & NIE & " Change Request actions for the " & DateTypes & " AORB/TEWG." & SigBlock
   .To = GrpEMails
   .Display
    DoCmd.Close acReport, "rptOpenAo"
    DoCmd.OpenForm "frmStart"
Exit Sub
End With
Else
End If

With objOutlookMsg

   .Subject = NIE & " " & MnDay & DateTypes
   .Body = "The email addressing is a living entity.  If there are corrections, additions, or deletions, please notify the sender." & vbCrLf & vbCrLf & _
           "Dial in " & TELCodes & " " & SigBlock
    DoCmd.OutputTo 3, "rptOpenAO", acFormatPDF, "C:\Temp\" & Tod & " " & NIE & " Open Changes - " & DateTypes & ".pdf", , 0
   .Attachments.Add ("C:\Temp\" & Tod & " " & NIE & " Open Changes - " & DateTypes & ".pdf")
   .To = GrpEMails
   .Display
  Kill "C:\Temp\" & Tod & " " & NIE & " Open Changes - " & DateTypes & ".pdf"
  
  DoCmd.Close acReport, "rptOpenAO"
  DoCmd.OpenForm "frmStart"
  
  End With
 
 Set objOutlookMsg = Nothing
 Set objOutlook = Nothing
 Set objOutlookAttach = Nothing
  
 Exit Sub

ErrorMsgs:
 If Err.Number = "287" Then
  MsgBox "You selected No to the Outlook security warning. Rerun the procedure and click Yes to access e-mail addresses to send your message."
 Else
 MsgBox Err.Number & " " & Err.Description
End If
End Sub

I put 1 instance of the Dims under Option Explicit at the top usually.

Can you make the Dims as a separate module?

Can you make a separate module for the Set the same way? Or combine the Set and DIM in a separate module?

This is several wants and wishes in 1 thread though. Right now I'd be more concerned with the 2 CMD buttons opening a different quesry and both going to the same report format.

Thanks

[Micron]

Right now I'd be more concerned with the 2 CMD buttons opening a different quesry and both going to the same report format.

I can only suggest what I'd do, which is to have one command button and 2 option (radio) buttons in an option frame. The button click would ensure one option was chosen, then the code would open the report and either a) assign the appropriate query to the report recordsource property or b) apply a filter to the filter property. The latter is possible if the fields of its data source query are similar enough to simply apply a filter to the underlying records.

[Thompyt]

Micron
I am trying this as a module:

I think I am going wrong with the bolded code. I am only trying to get the filter on certain fields (Level and AOVote)

Sub BoardSel(arg)
    Dim sSQL1 As String
    Select Case arg
        Case 1
            sSQL1 = "SELECT tblChangeRequest.CRID, qryLookup.CRNumbers, qrySwitching.Levels, qrySwitching.DateIDs, qrySwitching.Status, tblChangeRequest.ChangeType, " _
            & "qrySwitching.HBVers, qrySwitching.Units, qrySwitching.MTOEParas, qrySwitching.People, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, " _
            & "tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.ActionComplete, tblChangeRequest.NIE, qrySwitching.DaysOpen, qrySwitching.Levelz, " _
            & "tblChangeRequest.AOVote, qrySwitching.CRLevel, tblChangeRequest.Change, IIf([FinalVote]<>'','     Date Closed: ' & Format([DateClosed],'Long Date'),Null) AS DClosed " _
            & "FROM (tblChangeRequest INNER JOIN qryLookup ON tblChangeRequest.CRID = qryLookup.CRID) INNER JOIN qrySwitching ON tblChangeRequest.CRID = qrySwitching.CRID " _
            & "WHERE ((ActionComplete)= False) AND ((AOVote)" & TempVars!AOVoteSEL & " AND (Level) " & TempVars!LevelSel & " AND ((tblChangeRequest.SubNo) Is Not Null));"

            Call fcnCustomizeSQL("qRecSrcChanges", sSQL1)
    End Select
End Sub
Function fcnCustomizeSQL(qName As String, strPassedSQL As String) As Boolean
    
Dim qthisQuery As DAO.QueryDef
    
    If TempVars!tvEnableErrorHandling = True Then On Error GoTo fcnCustomizeSQL_Error   'if the query has been deleted, create it
    If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
        Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
        Set qthisQuery = Nothing
        Exit Function
    End If

    Set qthisQuery = CurrentDb.QueryDefs(qName)
    qthisQuery.sql = strPassedSQL
fcnCustomizeSQL_Exit:
    On Error Resume Next
    Set qthisQuery = Nothing
    Exit Function
fcnCustomizeSQL_Error:
    MsgBox Err.Number & ", " & Err.Description & ", fcnCustomizeSQL"
    Resume fcnCustomizeSQL_Exit
End Function

This is the output in qRecSrcChanges for where:
WHERE (((tblChangeRequest.[ActionComplete])=False) AND ((tblChangeRequest.[AOVote])<>"False") AND ((tblChangeRequest.[Level])<>"False") AND ((tblChangeRequest.SubNo) Is Not Null));

I figure it's in the coding for the Command Buttons:

Private Sub OpenAOs_Click()
Me.AOSelVote = ""
Me.O6Selects = ""

    TempVars!AOSelVote = Me.AOSelVote.Value
    TempVars!LevelSel = Me.LevelSel.Value
    Call BoardSel(1)
    DoCmd.Close acForm, "frmStart"
    DoCmd.OpenReport "rptOpenAO", acViewReport

End Sub
Private Sub Open_CCB_Click()

Me.AOSelVote = "<>'Open' And <> 'Defer'"
Me.LevelSel = "<>'Level 1'"

    TempVars!AOSelVote = Me.AOSelVote.Value
    TempVars!Level = Me.LevelSel.Value
    Call BoardSel(1)
    DoCmd.Close acForm, "frmStart"
    DoCmd.OpenReport "rptOpenCCB", acViewReport
    
End Sub

I have 2 hidden unbound fields on the form
AOSelVote amd LevelSel

[Micron]

A few things look wrong, but without seeing the db or knowing the data types of all fields involved, it's impossible to pinpoint with certainty. Here's what doesn't look right and/or is suspect:
((AOVote)" & TempVars!AOVoteSEL & " AND (Level) " & TempVars!LevelSel & " AND ((tblChangeRequest.SubNo) Is Not Null));
It looks like you're trying to specify that 3 fields (call them A, B and C) not be Null but you cannot group them and expect Access to apply the test across 3 fields with one application of that test. In other words, you must apply any test to each field or variable, as in A Is Not Null AND B Is Not Null AND C Is Not Null
Secondly, you should Debug.Print the sql output to the immediate window. ((AOVote)" & TempVars!AOVoteSEL & " AND (Level) " & TempVars!LevelSel & " parts don't look right. For one thing, you're including the ampersan (&) in quotes, plus if for example TempVars!AOVoteSEL was equal to YES, you're saying ((AOVoteYES &
Also, regarding <> "False" : If False is a field textual value, then OK. If it's Boolean (True/False, 1/0, Yes/No) then should not have quotes.
Take a look at these issues and be sure to check your sql output as noted. You can copy that to a new query and in sql view, paste it in and see how it works. When you do this, the variables will have been replaced by their actual values.