If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Better way to utilize my form
I have a user go into a Form which allows the user to select a BusinessGroup
with a combo box. Then the user has a command button on the form which launches an event procedure. What happens 1st is: An expression in a Query that says [Enter a YearMonthValue: yyyymm] (comes via a msgbox) Then the following code is executed: Private Sub cmd_OpenReport_Click() DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, , "[Business Group I] = '" & Me.cbxBusinessGroup & "'" DoCmd.RunCommand acCmdPrint End Sub This works fine, but is there a better way? ---I’d like for the user to 1st enter the YearMonthValue in a text box on my form, then select a BusinessGroup in the ComboBox. Then use a command Button to use both the text box and the combo box data as filters to OpenReport. |
#2
|
|||
|
|||
Better way to utilize my form
The only improvement I can think of is to avoid the message box and put the
criteria in a textbox on the form. The query criteria will need to be changed to something like: [Forms]![YourFormName]![YourTextboxName] -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "mlieberstein" wrote in message ... I have a user go into a Form which allows the user to select a BusinessGroup with a combo box. Then the user has a command button on the form which launches an event procedure. What happens 1st is: An expression in a Query that says [Enter a YearMonthValue: yyyymm] (comes via a msgbox) Then the following code is executed: Private Sub cmd_OpenReport_Click() DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, , "[Business Group I] = '" & Me.cbxBusinessGroup & "'" DoCmd.RunCommand acCmdPrint End Sub This works fine, but is there a better way? ---I'd like for the user to 1st enter the YearMonthValue in a text box on my form, then select a BusinessGroup in the ComboBox. Then use a command Button to use both the text box and the combo box data as filters to OpenReport. |
#3
|
|||
|
|||
Better way to utilize my form
Thank You very Much. I appreciate your advice.
After I updated the query, [Forms]![frm_BusinessGroup]![txtYMV] I wanted to put the textbox data (YearMonthValue) into my report. I created a text box and tried the following: =[Forms]![frm_BusinessGroup]![txtYMV] (The formula came through, no data) =Forms!frm_BusinessGroup!txtYMV (Again, The formula came through, no data) Any ideas to get this to work? "Arvin Meyer MVP" wrote: The only improvement I can think of is to avoid the message box and put the criteria in a textbox on the form. The query criteria will need to be changed to something like: [Forms]![YourFormName]![YourTextboxName] -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "mlieberstein" wrote in message ... I have a user go into a Form which allows the user to select a BusinessGroup with a combo box. Then the user has a command button on the form which launches an event procedure. What happens 1st is: An expression in a Query that says [Enter a YearMonthValue: yyyymm] (comes via a msgbox) Then the following code is executed: Private Sub cmd_OpenReport_Click() DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, , "[Business Group I] = '" & Me.cbxBusinessGroup & "'" DoCmd.RunCommand acCmdPrint End Sub This works fine, but is there a better way? ---I'd like for the user to 1st enter the YearMonthValue in a text box on my form, then select a BusinessGroup in the ComboBox. Then use a command Button to use both the text box and the combo box data as filters to OpenReport. |
#4
|
|||
|
|||
Better way to utilize my form
If you are getting the expression [Forms]![frm_BusinessGroup]![txtYMV] (not
formula, that's a spreadsheet term) in the text box on the report it sounds rather like its been entered in the ControlSource property as a string expression rather than a reference to the control, but that seems unlikely and you do not include any delimiting quotes characters around the expression as posted to suggest this is the case. However, you can achieve the same end result without any references to the control as parameters in the query or as the ControlSource property of a text box in the report. You can filter the report by means of the WhereCondition argument of the OpenReport method (which you are doing already, so its just a case of extending the expression), and you can assign a value to the text box on the report by passing it to the report as the OpenArgs argument of the OpenReport method: Const conMESSAGE = _ "Both a business group and YMV value must be provided" Dim strCriteria As String, strArgs as String If Not IsNull(Me.cbxBusinessGroup) And _ Not IsNull(Me.txtYMV) Then strArgs = Me.txtYMV strCriteria = _ "[Business Group I] = """ & Me.cbxBusinessGroup & _ """ And [YearMonth] = """ & strArgs & """" DoCmd.OpenReport "BusinessGroupReport_rpt", _ View:=acViewPreview, _ WhereCondition:=strCriteria, _ OpenArgs:=strArgs DoCmd.RunCommand acCmdPrint Else MsgBox conMESSAGE, vbExclamation, "Invalid Operation" End If This assumes that the field in the report's underlying recordset is named YearMonth and is of text data type. If it’s a number data type omit the delimiting quotes: strCriteria = _ "[Business Group I] = """ & Me.cbxBusinessGroup & _ """ And [YearMonth] = " & strArgs In the report's Open event procedure assign the value passed to the report as its OpenArgs property to an unbound text box in the report: Me.txtYMV = Me.OpenArgs As with this approach you are passing values to the report rather than referencing controls on the form you can if you wish automatically close the form after the button is clicked by adding the following to its code: DoCmd.Close acForm, Me.Name Another possible enhancement would be to include two combo boxes cbxYear and cbxMonth, on the form for the YMV value rather than a single text box, one for the year and one for the month (rather like when entering the expiry date of a credit card in an online form), the former with values over a suitable range of years, the latter with values 01 to 12. You'd then amend the code: If Not IsNull(Me.cbxBusinessGroup) And _ Not IsNull(Me.cbxYear ) And _ Not IsNull(Me.cbxMonth ) Then strArgs = Me.cbxYear & Me.cbxMonth You could if you wish by default set the combo boxes to the current year/month in the form's Open event procedure with: Me.cbxYear = Format(VBA.Date,"yyyy") Me.cbxMonth = Format(VBA.Date,"mm") Ken Sheridan Stafford, England mlieberstein wrote: Thank You very Much. I appreciate your advice. After I updated the query, [Forms]![frm_BusinessGroup]![txtYMV] I wanted to put the textbox data (YearMonthValue) into my report. I created a text box and tried the following: =[Forms]![frm_BusinessGroup]![txtYMV] (The formula came through, no data) =Forms!frm_BusinessGroup!txtYMV (Again, The formula came through, no data) Any ideas to get this to work? The only improvement I can think of is to avoid the message box and put the criteria in a textbox on the form. The query criteria will need to be [quoted text clipped - 28 lines] Button to use both the text box and the combo box data as filters to OpenReport. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200906/1 |
#5
|
|||
|
|||
Better way to utilize my form
great thanks for your assistance, Ken.
"KenSheridan via AccessMonster.com" wrote: If you are getting the expression [Forms]![frm_BusinessGroup]![txtYMV] (not formula, that's a spreadsheet term) in the text box on the report it sounds rather like its been entered in the ControlSource property as a string expression rather than a reference to the control, but that seems unlikely and you do not include any delimiting quotes characters around the expression as posted to suggest this is the case. However, you can achieve the same end result without any references to the control as parameters in the query or as the ControlSource property of a text box in the report. You can filter the report by means of the WhereCondition argument of the OpenReport method (which you are doing already, so its just a case of extending the expression), and you can assign a value to the text box on the report by passing it to the report as the OpenArgs argument of the OpenReport method: Const conMESSAGE = _ "Both a business group and YMV value must be provided" Dim strCriteria As String, strArgs as String If Not IsNull(Me.cbxBusinessGroup) And _ Not IsNull(Me.txtYMV) Then strArgs = Me.txtYMV strCriteria = _ "[Business Group I] = """ & Me.cbxBusinessGroup & _ """ And [YearMonth] = """ & strArgs & """" DoCmd.OpenReport "BusinessGroupReport_rpt", _ View:=acViewPreview, _ WhereCondition:=strCriteria, _ OpenArgs:=strArgs DoCmd.RunCommand acCmdPrint Else MsgBox conMESSAGE, vbExclamation, "Invalid Operation" End If This assumes that the field in the report's underlying recordset is named YearMonth and is of text data type. If it’s a number data type omit the delimiting quotes: strCriteria = _ "[Business Group I] = """ & Me.cbxBusinessGroup & _ """ And [YearMonth] = " & strArgs In the report's Open event procedure assign the value passed to the report as its OpenArgs property to an unbound text box in the report: Me.txtYMV = Me.OpenArgs As with this approach you are passing values to the report rather than referencing controls on the form you can if you wish automatically close the form after the button is clicked by adding the following to its code: DoCmd.Close acForm, Me.Name Another possible enhancement would be to include two combo boxes cbxYear and cbxMonth, on the form for the YMV value rather than a single text box, one for the year and one for the month (rather like when entering the expiry date of a credit card in an online form), the former with values over a suitable range of years, the latter with values 01 to 12. You'd then amend the code: If Not IsNull(Me.cbxBusinessGroup) And _ Not IsNull(Me.cbxYear ) And _ Not IsNull(Me.cbxMonth ) Then strArgs = Me.cbxYear & Me.cbxMonth You could if you wish by default set the combo boxes to the current year/month in the form's Open event procedure with: Me.cbxYear = Format(VBA.Date,"yyyy") Me.cbxMonth = Format(VBA.Date,"mm") Ken Sheridan Stafford, England mlieberstein wrote: Thank You very Much. I appreciate your advice. After I updated the query, [Forms]![frm_BusinessGroup]![txtYMV] I wanted to put the textbox data (YearMonthValue) into my report. I created a text box and tried the following: =[Forms]![frm_BusinessGroup]![txtYMV] (The formula came through, no data) =Forms!frm_BusinessGroup!txtYMV (Again, The formula came through, no data) Any ideas to get this to work? The only improvement I can think of is to avoid the message box and put the criteria in a textbox on the form. The query criteria will need to be [quoted text clipped - 28 lines] Button to use both the text box and the combo box data as filters to OpenReport. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200906/1 |
#6
|
|||
|
|||
Better way to utilize my form
Ken, the code worked great. But there was one problem.
After I entered the 200907 in the text box txtYMV, and chose a Business Group in the cbxBusinessGroup, I hit the OpenReport Button. But then I was prompted immediately with a box YearMonth. I don't want the user to enter the YMV twice. "KenSheridan via AccessMonster.com" wrote: If you are getting the expression [Forms]![frm_BusinessGroup]![txtYMV] (not formula, that's a spreadsheet term) in the text box on the report it sounds rather like its been entered in the ControlSource property as a string expression rather than a reference to the control, but that seems unlikely and you do not include any delimiting quotes characters around the expression as posted to suggest this is the case. However, you can achieve the same end result without any references to the control as parameters in the query or as the ControlSource property of a text box in the report. You can filter the report by means of the WhereCondition argument of the OpenReport method (which you are doing already, so its just a case of extending the expression), and you can assign a value to the text box on the report by passing it to the report as the OpenArgs argument of the OpenReport method: Const conMESSAGE = _ "Both a business group and YMV value must be provided" Dim strCriteria As String, strArgs as String If Not IsNull(Me.cbxBusinessGroup) And _ Not IsNull(Me.txtYMV) Then strArgs = Me.txtYMV strCriteria = _ "[Business Group I] = """ & Me.cbxBusinessGroup & _ """ And [YearMonth] = """ & strArgs & """" DoCmd.OpenReport "BusinessGroupReport_rpt", _ View:=acViewPreview, _ WhereCondition:=strCriteria, _ OpenArgs:=strArgs DoCmd.RunCommand acCmdPrint Else MsgBox conMESSAGE, vbExclamation, "Invalid Operation" End If This assumes that the field in the report's underlying recordset is named YearMonth and is of text data type. If it’s a number data type omit the delimiting quotes: strCriteria = _ "[Business Group I] = """ & Me.cbxBusinessGroup & _ """ And [YearMonth] = " & strArgs In the report's Open event procedure assign the value passed to the report as its OpenArgs property to an unbound text box in the report: Me.txtYMV = Me.OpenArgs As with this approach you are passing values to the report rather than referencing controls on the form you can if you wish automatically close the form after the button is clicked by adding the following to its code: DoCmd.Close acForm, Me.Name Another possible enhancement would be to include two combo boxes cbxYear and cbxMonth, on the form for the YMV value rather than a single text box, one for the year and one for the month (rather like when entering the expiry date of a credit card in an online form), the former with values over a suitable range of years, the latter with values 01 to 12. You'd then amend the code: If Not IsNull(Me.cbxBusinessGroup) And _ Not IsNull(Me.cbxYear ) And _ Not IsNull(Me.cbxMonth ) Then strArgs = Me.cbxYear & Me.cbxMonth You could if you wish by default set the combo boxes to the current year/month in the form's Open event procedure with: Me.cbxYear = Format(VBA.Date,"yyyy") Me.cbxMonth = Format(VBA.Date,"mm") Ken Sheridan Stafford, England mlieberstein wrote: Thank You very Much. I appreciate your advice. After I updated the query, [Forms]![frm_BusinessGroup]![txtYMV] I wanted to put the textbox data (YearMonthValue) into my report. I created a text box and tried the following: =[Forms]![frm_BusinessGroup]![txtYMV] (The formula came through, no data) =Forms!frm_BusinessGroup!txtYMV (Again, The formula came through, no data) Any ideas to get this to work? The only improvement I can think of is to avoid the message box and put the criteria in a textbox on the form. The query criteria will need to be [quoted text clipped - 28 lines] Button to use both the text box and the combo box data as filters to OpenReport. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200906/1 |
#7
|
|||
|
|||
Better way to utilize my form
It sounds like you either still have the parameter in the report's underlying
query, in which case remove it as its no longer required; or the column name is not YearMonth as I'd assumed for my example , but something else. If the latter change the reference to YearMonth in the code to the real name of the column. Ken Sheridan Stafford, England mlieberstein wrote: Ken, the code worked great. But there was one problem. After I entered the 200907 in the text box txtYMV, and chose a Business Group in the cbxBusinessGroup, I hit the OpenReport Button. But then I was prompted immediately with a box YearMonth. I don't want the user to enter the YMV twice. If you are getting the expression [Forms]![frm_BusinessGroup]![txtYMV] (not formula, that's a spreadsheet term) in the text box on the report it sounds [quoted text clipped - 89 lines] Button to use both the text box and the combo box data as filters to OpenReport. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/200907/1 |
#8
|
|||
|
|||
Better way to utilize my form
Perfect. Yes, I changed YearMonth to my field: yearmonthvalue, and I removed
the parameter from my query. I'm good now. thanks for the advice "mlieberstein" wrote: I have a user go into a Form which allows the user to select a BusinessGroup with a combo box. Then the user has a command button on the form which launches an event procedure. What happens 1st is: An expression in a Query that says [Enter a YearMonthValue: yyyymm] (comes via a msgbox) Then the following code is executed: Private Sub cmd_OpenReport_Click() DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, , "[Business Group I] = '" & Me.cbxBusinessGroup & "'" DoCmd.RunCommand acCmdPrint End Sub This works fine, but is there a better way? ---I’d like for the user to 1st enter the YearMonthValue in a text box on my form, then select a BusinessGroup in the ComboBox. Then use a command Button to use both the text box and the combo box data as filters to OpenReport. |
Thread Tools | |
Display Modes | |
|
|