You have a form. On the form is a multi-select list box. Lets say there are 6 items selected in the list box: 1, 3 & 6. - Correct assumption
Initiating form:
Code:
Private Sub AOOOB_Click()
Me.AOSelects = " <> 'Defer' And (AOVote) <> 'Hold' and (AOVote) <> 'Deny'" 'and (AOVote) <> 'Approve'
Me.O6Selects = ""
TempVars!AOSelects = Me.AOSelects.Value
TempVars!O6Selects = Me.O6Selects.Value
Call subCreateQuery(1)
DoCmd.OpenForm "frmOOBChangeSelect"
End Sub
Private Sub CCBOOB_Click()
Me.AOSelects = " <> 'Open'"
Me.O6Selects = " is Null"
TempVars!AOSelects = Me.AOSelects.Value
TempVars!O6Selects = Me.O6Selects.Value
Call subCreateQuery(1)
DoCmd.OpenForm "frmOOBChangeSelect"
End Sub
Private Sub GOOOB_click()
Me.AOSelects = "<> 'Open'"
Me.O6Selects = " <> 'Defer' and (O6Vote) <> 'Hold'"
TempVars!AOSelects = Me.AOSelects.Value
TempVars!O6Selects = Me.O6Selects.Value
Call subCreateQuery(1)
DoCmd.OpenForm "frmOOBChangeSelect"
End Sub
You want to include data from (??) in the email body (strBdyMail)? All 3 select items in the list box?
A: If you have selected the three items (1, 3 & 6) that is correct.
Q: Why are you using strWhere and strWhere2? Since they will have identical values, why not just have strWhere??
A: I am using StrWhere for the report, and strwhere2 for the email in the subject body and file naming conventions. Unless there is another way to get rid of the ' marks around the numbers from strwhere.
Q: What are "NIE" and "Tod"? Controls on the form? Variables?
NIE is a control that's automatically generated on the main table and picked up by the query in the Query module. TOD is today's date formatted in a module
TOD:
Code:
Option Explicit
Function Tod() As String
Tod = Format(Date, "dd mmm yyyy")
End Function
Q:Where do the values for "Dates", DaysOpen", "Priority", "OOBNumber", "AOVote", ... come from?
A: These come from a module that makes a query from the main table - It filters all the data down to the records that meet the criteria when one of three buttons are selected.
Query Module:
Code:
Option Compare Database
Option Explicit
Sub subCreateQuery(arg)
Dim sSQL As String
Select Case arg
Case 1
sSQL = "SELECT [CRNo]+([SubNo]*0.01) AS OOBNumber,Priority, CRID, [Level], NIE, ChangeType, DateID, " _
& "ChangeRequested, Rationale, AOVote, O6Vote, Notes, ActionItems, Hr, ActionComplete," _
& "Format([DateID],'dd mmmm yyyy') AS Dates, " _
& "Format(Now()+([Hr]/24),'hhnn dddd, mmm d yyyy') AS [Time], " _
& "Format(Now()+([Hr]/24),'hhnn dddd, mmm d yyyy') AS DTG, [Unit] & Chr(13) & Chr(10) & " _
& "[Section] AS Units, [HBVersion] & Chr(13) & Chr(10) & [ApproxPage] AS HBVers, " _
& "[MTOEPara] & Chr(13) & Chr(10) & [BumperNum] AS MTOEParas, " _
& "[Requestor] & Chr(13) & Chr(10) & [Sponsor] AS People, " _
& "DateDiff('d',[DateID],[DateClosed]) AS DaysOpen, [Priority] & ' ' & [Level] & ' OOB Change Request(s)' AS Label, " _
& "Chr(9) & Chr(9) & [Unit] & Chr(9) & Chr(9) & [Section] AS Unitss, [HBVersion] & Chr(9) & Chr(9) & [ApproxPage] AS HBVerss, " _
& "[MTOEPara] & Chr(9) & Chr(9) & [BumperNum] AS MTOEParass " _
& "FROM TblChangeRequest " _
& "WHERE (([ActionComplete])= False) AND (([AOVote])" & TempVars!AOSelects & " AND (O6Vote) " & TempVars!O6Selects & " and (([CRNo])<>0));"
Call fcnCustomizeSQL("qRecSourceOOBChanges", sSQL)
Case 2
Case 3
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