Bounded Columns

[jm112]

Hey guys,

I have a combo box (Man1_AB) with 2 columns -> ID and name. The combo box is bounded to column 1 - ID.

I want to open a report filtering results for the combo box's non bounded column. Is there a way to do this?

I know this works for the bounded column:

DoCmd.OpenReport "rpt_install", acViewNormal, , "[Man1_AB] Like '*'&[Enter Search String]&'*'"

Where the parameter search string is the ID.

Is there a way to do the same same thing but for the unbounded column (the person's actual name)?

Thanks!

 

[RuralGuy]

You can access all of the columns of a ComboBox display using the Column property.
Me.ComboBoxName.Column(0)
...would be the 1st column since it is zero based.

 

[jm112]

Thanks for the response.

Is there any way to apply this to the control source for a where criteria in a query for the DoCmd.OpenReport?

"Me.[Control_Source_of_cbo].Column(1) Like .... "

doesn't work.

 

[RuralGuy]

You could certainly assign the value to a variable and then use the variable in the WhereCondition argument.

 

[jm112]

I made a variable and assigned .column(1) to it and it still doesn't work.

Can you not use .column(1) in a query criteria?

 

[RuralGuy]

Show us the code you used when you used the variable, please.

 

[boblarson]

I made a variable and assigned .column(1) to it and it still doesn't work.

Can you not use .column(1) in a query criteria?

That is correct - you cannot use that in a query criteria. If you want it in a query you will have to build the query by code. But you don't need to modify the query. Just use the where clause of the report as it was suggested earlier.

For the open report code, if you are looking for the text, the syntax is:

DoCmd.OpenReport "ReportNameHere", acViewPreview, , "[FieldToLimitByHere] = " & Chr(34) & Me.YourComboBoxName.Column(1) & Chr(34)

 

[jm112]

Appreciate the responses and the patience.

I think I'm close in the various stuff I've tried that's been recommended but it still isn't quite working.

DoCmd.OpenReport "rpt_install", acViewReport, , "[Man1_AB] = Like '*'&[Enter Name]&'*'"

Is what I have.

The [Man1_AB] is the control source of the combo box (with 2 columns) that I want to only look at column 1 (column 0 is the ID). The combo box is called cboMan1_AB in the report I'm trying to open.
The [Enter Name] is the parameter I want to pop up when the user clicks this button on the switchboard.

Sorry if this was unclear. Usually I can figure this stuff out by trial and error but this really has me stumped. Thanks again.

 

[boblarson]

1. Don't use a parameter prompt. USE A FORM for input.

2. Why not use a combo to select a valid name. Then you don't need to use LIKE.

 

[jm112]

1. Don't use a parameter prompt. USE A FORM for input.

2. Why not use a combo to select a valid name. Then you don't need to use LIKE.

For whatever reason it never occurred to me to launch a small form to do this with a combo box for a selector. I was too focused on trying to do everything from a single button. Thanks for the help, I should be good now.