Execute non-query related Listbox on Report

[ML]

I have used Allen Brown's technique for filling a listbox on a form with the
names of files in a certain disc folder.
It works well.

I am now giving the user the option to print the form contents in a report
which has the same fields as the form.

All fields derived from report query are OK
A listbox with design mode rowsource setting, works fine BUT...

I wish to use the following (the same call as in the form), to fill a
listbox on the report ...

Private Sub Report_Open()
Dim sTaskPath As String
sTaskPath = "c:\randd\lrs\documents\P_001\SP_001_01\TA_001 "
Call ListFiles(sTaskPath, "*.*", , Me.lstTaskDocuments)
End Sub

It does not work in any report and report section event (including above).

I get a message telling me effectively that setting the rowsource of the
lisbox is inappropriate at this stage of the report.
(Error 2191)

Is there an event I've missed, is this impossible???

Is there no way to dynamically, from code, set the contents of this,
non-bound, listbox, on the fly?

TIA
Michael

[Allen Browne]

Instead of using the AddItem method, you could add the items to a great long
string, separated by semicolons, and assign that to the RowSource.

Replace:
For Each varItem In colDirList
lst.AddItem varItem
Next
with:
For Each varItem In colDirList
strOut = strOut & """" & varItem & """;"
Next
lst.RowSource = Left(strOut, Len(strOut) - 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"ML" <md*****@bigpond.net.auwrote in message
news:v%*******************@news-server.bigpond.net.au...

>I have used Allen Brown's technique for filling a listbox on a form with
the names of files in a certain disc folder.
It works well.

I am now giving the user the option to print the form contents in a report
which has the same fields as the form.

All fields derived from report query are OK
A listbox with design mode rowsource setting, works fine BUT...

I wish to use the following (the same call as in the form), to fill a
listbox on the report ...

Private Sub Report_Open()
Dim sTaskPath As String
sTaskPath = "c:\randd\lrs\documents\P_001\SP_001_01\TA_001 "
Call ListFiles(sTaskPath, "*.*", , Me.lstTaskDocuments)
End Sub

It does not work in any report and report section event (including above).

I get a message telling me effectively that setting the rowsource of the
lisbox is inappropriate at this stage of the report.
(Error 2191)

Is there an event I've missed, is this impossible???

Is there no way to dynamically, from code, set the contents of this,
non-bound, listbox, on the fly?

TIA
Michael

[ML]

Thanks, Allen

It would appear that re-setting the rowsource is OK at Open, but trying to
independently populate - as opposed to sourcing, on the fly, is something
the
report engine - designed to interpret its report query - and any "pre
sourced" listboxes, won't do.

The 2191 runtime error message, made me think that the problem was with the
rowsource assignment at Open, which was odd because I have set rowsource at
Open before.
The real problem was trying to carry out some independent assignment
execution on a report control - the explicit item by item filling of the
listbox control.

Michael

"Allen Browne" <Al*********@SeeSig.invalidwrote in message
news:45**********************@per-qv1-newsreader-01.iinet.net.au...

Instead of using the AddItem method, you could add the items to a great
long string, separated by semicolons, and assign that to the RowSource.

Replace:
For Each varItem In colDirList
lst.AddItem varItem
Next
with:
For Each varItem In colDirList
strOut = strOut & """" & varItem & """;"
Next
lst.RowSource = Left(strOut, Len(strOut) - 1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"ML" <md*****@bigpond.net.auwrote in message
news:v%*******************@news-server.bigpond.net.au...

>>I have used Allen Brown's technique for filling a listbox on a form with
the names of files in a certain disc folder.
It works well.

I am now giving the user the option to print the form contents in a
report which has the same fields as the form.

All fields derived from report query are OK
A listbox with design mode rowsource setting, works fine BUT...

I wish to use the following (the same call as in the form), to fill a
listbox on the report ...

Private Sub Report_Open()
Dim sTaskPath As String
sTaskPath = "c:\randd\lrs\documents\P_001\SP_001_01\TA_001 "
Call ListFiles(sTaskPath, "*.*", , Me.lstTaskDocuments)
End Sub

It does not work in any report and report section event (including
above).

I get a message telling me effectively that setting the rowsource of the
lisbox is inappropriate at this stage of the report.
(Error 2191)

Is there an event I've missed, is this impossible???

Is there no way to dynamically, from code, set the contents of this,
non-bound, listbox, on the fly?

TIA
Michael

[Allen Browne]

It would not be too hard to populate a temporary table with the file names
instead of adding to a list box.

At the top of the code, clear the table, and open it:
Dim dbAs DAO.Database
Dim rs As DAO.Recordset
Set db = dbEngine(0)(0)
db.Execute "DELETE FROM tblFile;"
Set rs = db.OpenTable("tblFile")

Instead of adding the the collection, add to the recordset:
rs.AddNew
rs!TheFile = varItem
rs.Update

At the end of the code:
rs.Close
Set rs= Nothing
Set db = Nothing

You can now use tblFile as the RecordSource for your report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ML" <md*****@bigpond.net.auwrote in message
news:1T*****************@news-server.bigpond.net.au...

Thanks, Allen

It would appear that re-setting the rowsource is OK at Open, but trying to
independently populate - as opposed to sourcing, on the fly, is something
the
report engine - designed to interpret its report query - and any "pre
sourced" listboxes, won't do.

The 2191 runtime error message, made me think that the problem was with
the rowsource assignment at Open, which was odd because I have set
rowsource at Open before.
The real problem was trying to carry out some independent assignment
execution on a report control - the explicit item by item filling of the
listbox control.

Michael

"Allen Browne" <Al*********@SeeSig.invalidwrote in message
news:45**********************@per-qv1-newsreader-01.iinet.net.au...

>Instead of using the AddItem method, you could add the items to a great
long string, separated by semicolons, and assign that to the RowSource.

Replace:
For Each varItem In colDirList
lst.AddItem varItem
Next
with:
For Each varItem In colDirList
strOut = strOut & """" & varItem & """;"
Next
lst.RowSource = Left(strOut, Len(strOut) - 1)

"ML" <md*****@bigpond.net.auwrote in message
news:v%*******************@news-server.bigpond.net.au...

>>>I have used Allen Brown's technique for filling a listbox on a form with
the names of files in a certain disc folder.
It works well.

I am now giving the user the option to print the form contents in a
report which has the same fields as the form.

All fields derived from report query are OK
A listbox with design mode rowsource setting, works fine BUT...

I wish to use the following (the same call as in the form), to fill a
listbox on the report ...

Private Sub Report_Open()
Dim sTaskPath As String
sTaskPath = "c:\randd\lrs\documents\P_001\SP_001_01\TA_001 "
Call ListFiles(sTaskPath, "*.*", , Me.lstTaskDocuments)
End Sub

It does not work in any report and report section event (including
above).

I get a message telling me effectively that setting the rowsource of the
lisbox is inappropriate at this stage of the report.
(Error 2191)

Is there an event I've missed, is this impossible???

Is there no way to dynamically, from code, set the contents of this,
non-bound, listbox, on the fly?