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