SELECT MULTIPLE RECORDS TO PRINT 1

[VBACT]

Would appreciate help on the following.

On a form I have a combo box that requeries a list box. When the user selects an item from the list box and clicks OK it generates a report for that "record" only. If he makes no selection all records are printed.

I would like to give the user the ability to select multiple records by setting the list box to Multi select. How do I code the Open Report "where" clause for multiple criterias?

Please help.

THANKS

 

[jebry]

Hi!

Make an Open Report button and use the following code in the click procedure:

Dim varRow As Variant
Dim strWhere As String

strWhere = ""

For Each varRow In YourListBox.ItemsSelected
strWhere = strWhere & "YourField = '" & YourListBox.Columns(0, varRow) & "' Or "
Next varRow

strWhere = Right(strWhere, Len(strWhere) - 4)

If strWhere = "" Then
DoCmd.OpenReport "YourReport"
Else
DoCmd.OpenReport "YourReport", , , strWhere
End If

This code assumes that your field is a text field, if it is numeric then leave out the single quotes.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[VBACT]

Thanks, Jeff

For my purpose,

strWhere = Right(strWhere, Len(strWhere) - 4)

should read as...

strWhere = LEFT(strWhere, Len(strWhere) - 4)

However, if the user makes no selection now, I get an Invalid Procedure Call or Argument (Run Time #5). Do I need to nest an additional IF within the first IF.

THanks for your help.

 

[VBACT]

Jeff,

Thanks, again! Resolved the error message by nesting the strWhere definition under the "else" in the IF statement.

Question?

The "YourField" is a global variable. How would I substitute the strWhere definition using the global variable definition?