Setting Report Criteria to Form Control Value

[Question Boy]

I am trying to set a report's record source to use a form's control value as
a criteria. I have done this before successfully for simple control. this
time i need to use th 2nd column of a combo box. hence I tried

Forms![frm_Labels]![cboCompType].Column(1)

but i keep getting an error
"Undefined function 'Forms![frm_Labels]![cboCompType].Column' in expression

Can someone point out my mistake.

Thank you.

QB

 

[Question Boy]

Also, I try the code in the VBA editor and it returns the proper value.

 

[Marshall Barton]

I am trying to set a report's record source to use a form's control value as
a criteria. I have done this before successfully for simple control. this
time i need to use th 2nd column of a combo box. hence I tried

Forms![frm_Labels]![cboCompType].Column(1)

but i keep getting an error
"Undefined function 'Forms![frm_Labels]![cboCompType].Column' in expression

Queries do not recognize that construct.

Instead of using a parameter in the report's record source
query, you can/should use the OpenReport method's
WhereCondition agrument to filter the report. For a number
type field, the form's report command button's Click event's
code would look something like:

Dim strCriteria As String
strCriteria = "somefield=" & Me.cboCompType.Column(1)
DoCmd.OpenReport "name of report", acviewPreview, _
WhereCondition:= strCriteria

 

[Duane Hookom]

I generally try to use the method suggested by Marsh. If the "somefield" is
not in the report's record source then you must choose a different solution.
I would place an invisible text box in the form:
Name: txtCompType
Control Source: =[cboCompType].Column(1)

Then set the criteria to:
Forms![frm_Labels]![txtCompType]

You might also be able to set the criteria to:
=Eval([cboCompType].Column(1))

--
Duane Hookom
Microsoft Access MVP

I am trying to set a report's record source to use a form's control value as
a criteria. I have done this before successfully for simple control. this
time i need to use th 2nd column of a combo box. hence I tried

Forms![frm_Labels]![cboCompType].Column(1)

but i keep getting an error
"Undefined function 'Forms![frm_Labels]![cboCompType].Column' in expression

Queries do not recognize that construct.

Instead of using a parameter in the report's record source
query, you can/should use the OpenReport method's
WhereCondition agrument to filter the report. For a number
type field, the form's report command button's Click event's
code would look something like:

Dim strCriteria As String
strCriteria = "somefield=" & Me.cboCompType.Column(1)
DoCmd.OpenReport "name of report", acviewPreview, _
WhereCondition:= strCriteria