Only records of the listbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I have one form with a list box that show us some fields of the records
selected through comboboxes in same form.
With a command button, in same form too, open a report with all the fields
of the selected records in listbox.
But when report open, appear all records of query that it gives support to
the listbox.
It would like to be able to print only records showed in listbox.

In command button to print, I have a next code:

Dim strFilter As String

DoCmd.OpenReport "R_1", acPreview, , strFilter
strFilter = "NAI = Forms!F_MultiField!ListBox!NAI"

(Where NAI is one number to edentify each record).

Thanks in advance.
an
 
First of all, you are defining your filter after the OpenReport command.
You need to switch the order. But even if you do, it would only show the
record that was SELECTED in the listbox, not all of the values in the
listbox. In order to do that, you have to step through the List in code,
write the values into a string, then put that string into your filter.
Something like this:

Dim strFilter As String
Dim i As Integer

For i = 0 To ListBox.ListCount - 1
strFilter = strFilter & ListBox.Column(0, i) & ", "
Next i
strFilter = Left(strFilter, Len(strFilter) - 4)

strFilter = "NAI in (" & strFilter & ")"
debug.print strFilter
DoCmd.OpenReport "R_1", acPreview, , strFilter

The above assumes that the listbox is named ListBox and that it has only one
column. If there are multiple columns, change the number of the Column
property to the correct column (minus 1 -- columns are zero-based). For
instance, if it was the 3rd column, you would use
strFilter = strFilter & ListBox.Column(2, i) & ", "

It also assumes the numbers are actually numbers. If the numbers are text
values, it would look like this:

Dim strFilter As String
Dim i As Integer

For i = 0 To ListBox.ListCount - 1
strFilter = strFilter & ListBox.Column(0, i) & "', '"
Next i
strFilter = Left(strFilter, Len(strFilter) - 4)

strFilter = "NAI in ('" & strFilter & "')"
debug.print strFilter
DoCmd.OpenReport "R_1", acPreview, , strFilter

Also, notice the debug.print line. This can help you see exactly what you
are producing in your filter string. If you put a breakpoint on the DoCmd
line, you can see what the value of strFilter looks like before it's
executed.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
RC,

Thank you for help.
an

Roger Carlson said:
First of all, you are defining your filter after the OpenReport command.
You need to switch the order. But even if you do, it would only show the
record that was SELECTED in the listbox, not all of the values in the
listbox. In order to do that, you have to step through the List in code,
write the values into a string, then put that string into your filter.
Something like this:

Dim strFilter As String
Dim i As Integer

For i = 0 To ListBox.ListCount - 1
strFilter = strFilter & ListBox.Column(0, i) & ", "
Next i
strFilter = Left(strFilter, Len(strFilter) - 4)

strFilter = "NAI in (" & strFilter & ")"
debug.print strFilter
DoCmd.OpenReport "R_1", acPreview, , strFilter

The above assumes that the listbox is named ListBox and that it has only one
column. If there are multiple columns, change the number of the Column
property to the correct column (minus 1 -- columns are zero-based). For
instance, if it was the 3rd column, you would use
strFilter = strFilter & ListBox.Column(2, i) & ", "

It also assumes the numbers are actually numbers. If the numbers are text
values, it would look like this:

Dim strFilter As String
Dim i As Integer

For i = 0 To ListBox.ListCount - 1
strFilter = strFilter & ListBox.Column(0, i) & "', '"
Next i
strFilter = Left(strFilter, Len(strFilter) - 4)

strFilter = "NAI in ('" & strFilter & "')"
debug.print strFilter
DoCmd.OpenReport "R_1", acPreview, , strFilter

Also, notice the debug.print line. This can help you see exactly what you
are producing in your filter string. If you put a breakpoint on the DoCmd
line, you can see what the value of strFilter looks like before it's
executed.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...
 
Ooops!

Sorry, but I don't see the difference between the code for number and the
code for text.

Thanks.an
 
There are two places where it is different:
Numeric:
strFilter = strFilter & ListBox.Column(0, i) & ", "
Text:
strFilter = strFilter & ListBox.Column(0, i) & "', '"
Numeric:
strFilter = "NAI in (" & strFilter & ")"
Text:
strFilter = "NAI in ('" & strFilter & "')"

The difference is in the additional apostrophes that delimit text data.

Oh, there is one additional change that I did NOT include. I believe this
line in the Numeric example:
strFilter = Left(strFilter, Len(strFilter) - 4)
should be:
strFilter = Left(strFilter, Len(strFilter) - 2)

This line removes the comma and apostrophe that is left at the end of the
line when the loop ends. Since the Text example leaves an apostrophe at the
end, it needs to be removed (hence 4). However, the Numeric example does
not have a trailing apostrophe, so only the comma needs to be removed (hence
2).


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
RC,

Thank you for your explanation.
an

Roger Carlson said:
There are two places where it is different:
Numeric:
strFilter = strFilter & ListBox.Column(0, i) & ", "
Text:
strFilter = strFilter & ListBox.Column(0, i) & "', '"
Numeric:
strFilter = "NAI in (" & strFilter & ")"
Text:
strFilter = "NAI in ('" & strFilter & "')"

The difference is in the additional apostrophes that delimit text data.

Oh, there is one additional change that I did NOT include. I believe this
line in the Numeric example:
strFilter = Left(strFilter, Len(strFilter) - 4)
should be:
strFilter = Left(strFilter, Len(strFilter) - 2)

This line removes the comma and apostrophe that is left at the end of the
line when the loop ends. Since the Text example leaves an apostrophe at the
end, it needs to be removed (hence 4). However, the Numeric example does
not have a trailing apostrophe, so only the comma needs to be removed (hence
2).


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...
 
Back
Top