 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / April 2007Use Input Box Value as Creteria
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 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  SignatureGood Luck BS"D > 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. [quoted text clipped - 26 lines] > > End Function I didnt uncer stand the Do Something part > 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 [quoted text clipped - 64 lines] > > > > End Function 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 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.