Listbox Value for Report

[JC]

I'm trying something new and don't seem to be able to get it to work. I have
a listbox in the middle of the form. The form is set up to take down
customer data and store it in a table. However, I want a listbox on the form
that will show previously entered customer data (orders). Then, I want to
double click on one of the items in the list box and have it bring up that
particular order as a report.

I have everything working great, with the exception of the report will only
to the specified order when the first item in the list is selected (it will
open to that specific item). If I select one of the subsequent items in the
listbox, it will open a blank report.

This is the chunk of code I'm using to do this with:


Private Sub ListBox1_DblClick (Cancel As Integer)

DoCmd.OpenReport "rptOrder", acViewPreview, , "Order_Number =
[Forms].[frmOrderEntry].[ListBox1]"

End Sub



Thanks in advance
- Jc

 

[Barry Gilbert]

I have everything working great, with the exception of the report will only
to the specified order when the first item in the list is selected (it will
open to that specific item). If I select one of the subsequent items in the
listbox, it will open a blank report.

This is the chunk of code I'm using to do this with:


Private Sub ListBox1_DblClick (Cancel As Integer)

DoCmd.OpenReport "rptOrder", acViewPreview, , "Order_Number =
[Forms].[frmOrderEntry].[ListBox1]"

Assuming you have the ListBox's MultiSelect property set to None, change
your code to this:

DoCmd.OpenReport "rptOrder", acViewPreview, , "Order_Number = " &
Me.ListBox1.ItemData(Me.Listbox1.ItemsSelected(0))

You need to use the ItemData to find get the value from a listbox and the
ItemsSelected is a collection of all items that are currently selected. If
you only allow a single item to be selected (by setting MultiSelect to none),
this collection will only contain one value, which is in position zero.

Barry

 

[JC]

Barry,

It actually had the same result. If I select the first order in the
listbox and double click it, it opens the report for that order just fine.
If I select the second, third, etc. order in the listbox and double click it,
I still get a blank report.

Also, I did confirm that I had the MultiSelect property set to None.

- Jeff C.

 

[Barry Gilbert]

Does your listbox have multiple columns? If so, what is the setting for the
Bound Column property? The bound column is the one that will be picked up by
the ItemData property.

You can debug what this is returning by putting a debug statement above the
OpenReport statement:
Debug.Print Me.ListBox1.ItemData(Me.Listbox1.ItemsSelected(0))

This will print the ItemData value to the immediate window. You could also
put a break on that line (select the line and click F9) to be able to step
the code.

Barry

 

[JC]

There are 2 columns in the listbox. The first is the order number, the
second is the order's notes. The bound column value is set to 1 and when I
try the debug, I get the correct order number on each one I click.

- Jeff C.

 

[JC]

Barry,

No need to reply. I found what was wrong. There was a bad join in the
record source query for the listbox that was causing "phantom" orders to
populate the listbox. So, when I clicked them, they weren't there to open a
report.

Thanks for all the help!
- Jeff C.

 

[Barry Gilbert]

It sounds like you're getting the correct value from the ListBox. The next
thing I would evaluate is the way the report is picking up it's record
source. Does your report have any other filters/criteria, like in a query,
that would conflict with the Where Condition you're passing in?

Barry