Use Input Box Value as Creteria

M

Max

I have the follwoing code: and I would like the month that is passed by the
input box be used in the second function as creteria.

Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the 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", HERE) > 0 Then
Check_Records = True
End If

End Function
 
O

Ofer Cohen

I assume that you want to run the second function from the first one, in that
case you can pass a value to the next function

e.g:

Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the 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")
' ** Call the function here
If Check_Records(strPrompt ) Then
Do Something
End If
'*********
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(MyMonth as String) As Boolean

If DCount("*", "Step1_Member_Status", "[MonthFiledName] = '" & MyMonth &
"'") > 0 Then
Check_Records = True
End If

End Function
 
M

Max

I didnt uncer stand the Do Something part

Ofer Cohen said:
I assume that you want to run the second function from the first one, in that
case you can pass a value to the next function

e.g:

Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the 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")
' ** Call the function here
If Check_Records(strPrompt ) Then
Do Something
End If
'*********
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(MyMonth as String) As Boolean

If DCount("*", "Step1_Member_Status", "[MonthFiledName] = '" & MyMonth &
"'") > 0 Then
Check_Records = True
End If

End Function
--
Good Luck
BS"D


Max said:
I have the follwoing code: and I would like the month that is passed by the
input box be used in the second function as creteria.

Public Sub Which_Month()
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the 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", HERE) > 0 Then
Check_Records = True
End If

End Function
Click to expand...
Click to expand...
 
M

Max

Here is my whole code for this:


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
 
O

Ofer Cohen

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
 

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

Compile Error 3
Format Date 4
Code Help 2
Word macro that can call cells out of Excel for a find and replace 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Recordset 5
Making a macro only work on certain files 2
Import Excel sheets to Access 2

Top