Code Help

G

Guest

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
 
G

Guest

1. In that case change the Which_Month sub into a Function, so you can return
a value with it.

Function Which_Month() As String
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
' Apply the strPrompt into the function
Which_Month = strPrompt
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 Function

=============================
2. Apply the value returned from the function into a variable MyMonth (that
I declared) and then into the CRI

Private Sub Command11_Click()
Dim MyMonth As String
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

' Insert the value returned from the function into the Variable
MyMonth = Which_Month


' Now you can use the CRI
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

--
Good Luck
BS"D


Max said:
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
Click to expand...
 
G

Guest

1. In that case change the Which_Month sub into a Function, so you can return
a value with it.

Function Which_Month() As String
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
' Apply the strPrompt into the function
Which_Month = strPrompt
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 Function

=============================
2. Apply the value returned from the function into a variable MyMonth (that
I declared) and then into the CRI

Private Sub Command11_Click()
Dim MyMonth As String
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

' Insert the value returned from the function into the Variable
MyMonth = Which_Month


' Now you can use the CRI
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


--
Good Luck
BS"D


Max said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2 list boxes used as creteria 4
Use Input Box Value as Creteria 4
Compile Error 3
Format Date 4
Combo Box Null 1
Multi Select List Not Working 1
no duplicate record 6
Concatenate elements of Combo Box to one string 4

Top