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
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.
