M
Max
Hello, I want the Which_Month sub vlaue from the input box to be part of my CRI
Private Sub Command11_Click()
Dim LBx As ListBox, criName As String, criStatus As String, Cri As String,
DQ As String, itm
DQ = """"
Set LBx = Me!List2
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criName <> "" Then
criName = criName & ", " & DQ & LBx.Column(1, itm) & DQ
Else
criName = DQ & LBx.Column(1, itm) & DQ
End If
Next
criName = "[Assigned Team Member] In(" & criName & ")"
Debug.Print criName
Else '=0 nothing in listbox
MsgBox "Please select an Analyst", vbCritical
Exit Sub
End If
Set LBx = Me!List4
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criStatus <> "" Then
criStatus = criStatus & ", " & DQ & LBx.Column(0, itm) & DQ
Else
criStatus = DQ & LBx.Column(0, itm) & DQ
End If
Next
criStatus = "[Status] In(" & criStatus & ")"
Debug.Print criStatus
Else '=0 nothing in listbox
MsgBox "Please select one or more Status", vbCritical
Exit Sub
End If
Call Which_Month
If Check_Records Then
Cri = criName & IIf(criName > "", " and ", "") & criStatus
DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri
Set LBx = Nothing
Else
MsgBox "There are no records to view", vbOK
End If
End Sub
Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of month you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Month in MM format: Enter '0' for All",
"Required Data")
If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _
"where [Submit Date] LIKE '" & strPrompt & "*' " & ""
Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If
End Sub
Public Function Check_Records() As Boolean
If DCount("*", "Step1_Member_Status") > 0 Then
Check_Records = True
End If
End Function
Private Sub Command11_Click()
Dim LBx As ListBox, criName As String, criStatus As String, Cri As String,
DQ As String, itm
DQ = """"
Set LBx = Me!List2
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criName <> "" Then
criName = criName & ", " & DQ & LBx.Column(1, itm) & DQ
Else
criName = DQ & LBx.Column(1, itm) & DQ
End If
Next
criName = "[Assigned Team Member] In(" & criName & ")"
Debug.Print criName
Else '=0 nothing in listbox
MsgBox "Please select an Analyst", vbCritical
Exit Sub
End If
Set LBx = Me!List4
If LBx.ItemsSelected.Count > 0 Then
For Each itm In LBx.ItemsSelected
If criStatus <> "" Then
criStatus = criStatus & ", " & DQ & LBx.Column(0, itm) & DQ
Else
criStatus = DQ & LBx.Column(0, itm) & DQ
End If
Next
criStatus = "[Status] In(" & criStatus & ")"
Debug.Print criStatus
Else '=0 nothing in listbox
MsgBox "Please select one or more Status", vbCritical
Exit Sub
End If
Call Which_Month
If Check_Records Then
Cri = criName & IIf(criName > "", " and ", "") & criStatus
DoCmd.OpenReport "Step1_Member_Status", acViewPreview, , Cri
Set LBx = Nothing
Else
MsgBox "There are no records to view", vbOK
End If
End Sub
Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of month you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Month in MM format: Enter '0' for All",
"Required Data")
If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _
"where [Submit Date] LIKE '" & strPrompt & "*' " & ""
Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If
End Sub
Public Function Check_Records() As Boolean
If DCount("*", "Step1_Member_Status") > 0 Then
Check_Records = True
End If
End Function