selected criteria from form in report header

[lil2009]

Hi,

After reviewing the many answers to this question, I still can't get the
criteria selected in the list box of a form to show on a report. I added a
textbox to the report and used builder to set the control source to
=[Forms]![frmQBF]![lboData]

Does anyone have an idea why it isn't working?

Thanks.

 

[John Spencer]

Is the listbox's Multi Select property set to None? If the property is set to
"simple" or "extended" you cannot get the value of the control.

If the Form is not open, then you should get an error message or a prompt for
the value. The form must be open (it can be visible or not visible) and there
must be somethng selected in lboData in order for a value to be returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

Lil,

Whenever I need to get the "value" associated with a listbox that has its
multi-select property set to either "simple" or "extended", I call the
subroutine below.

In your case, it sounds like you have a listbox on your form, and probably a
command button to print or preview the report. Assuming you have the command
button, you would use the click event to open the report and pass the
OpenReport method a criteria string which will limit which records are
visible in your report.

Private Sub cmd_Preview_Click

dim strCriteria as string

strCriteria = "[fieldname] " & fnMultiList(me.listName)
docmd.OpenReport "reportname", acViewPreview,,strCriteria, acDialog

End Sub

You will need to create a standard code module (not tied to a form) and add
the following code to it. This code will concatenate the values of the bound
column of the items that are selected in your listbox. If no items are
selected, it acts as if all items are selected, so if you don't want that
behavior, you will have to modify the code. Otherwise, it will determine how
many items were selected and if it is only 1, will return a string that
starts with an equal sign (=). If multiple items are selected, it will
return a string that include the "IN ( )" clause, with the selected values
wrapped in the ( ).