By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,053 Members | 1,595 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,053 IT Pros & Developers. It's quick & easy.

Need to Pull Data from a ListBox to a Control

P: n/a
I have a form that does the following:

- User inputs a serial number
- VBA code does a lookup (SQL Query) and populates a listbox with
results (4 columns in the Listbox)
- Start Over (Listbox can contain up to 11 items)

All that works perfectly.

However, the next step is to read the data OUT of the listbox and into
a report. However, I am unsure how to obtain all of the data from the
listbox into some sort of array and populate the report.

I can leave the form open while the report is generated so that the
listbox is accessible. However, I need help in what to do next.

Any help is appreciated.

Jason

May 17 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Why don't you use the SQL query to directly populate the listbox
(choose SQL query as rowsource). Then you can read out the rowsource
(Me.MyListBox.RowSource), being a valid SQL statement, and use that as
basis for your Report.

May 17 '06 #2

P: n/a
"DartmanX" <fe***********@gmail.com> wrote in
news:11**********************@j33g2000cwa.googlegr oups.com:
I have a form that does the following:

- User inputs a serial number
- VBA code does a lookup (SQL Query) and populates a listbox
with results (4 columns in the Listbox)
- Start Over (Listbox can contain up to 11 items)

All that works perfectly.

However, the next step is to read the data OUT of the listbox
and into a report. However, I am unsure how to obtain all of
the data from the listbox into some sort of array and populate
the report. I can leave the form open while the report is generated so
that the listbox is accessible. However, I need help in what
to do next.

Any help is appreciated.

Jason

Hopefully one of the 4 columns represents the primary key to the
table. Once you have your items selected into the listbox, you
need to walk the listbox and make a comma delimited list of
those primary key values. open the report, with a where clause
consisting of the primary key's field name IN (list)

mykeylist = "123,345,765,012" ' if numeric
mykeylist = """12A"",""33G"",""Z45""" ' if text.
(note the double doublequotes)

stWhere = "[myTableKeyField] IN (" & mykeylist & ")"
docmd.openreport"myReport,acpreview,,stWhere

--
Bob Quintal

PA is y I've altered my email address.
May 17 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.