Passing the contents of a listbox from a form into a report

[tonygg]

Hi. I have a listbox populated with calculated data. I would like to display it on a report but cant seem to reference it from the report.

I have a series of textboxes on the form that I can easily reference from the report by setting the control source of the field in the report to:

=Forms![Form Name]![TextBox Name]

These appear on the report once it is launched from the form using the command:

DoCmd.OpenReport "Report Name", acViewPreview

If I use the same construct on a listbox object in the report it does not work. I guess I am using the wrong syntax when setting the control source:

=Forms![Form Name]!
[Listbox Name]

Please could someone help me get the syntax correct.



Many thanks

[pbaldy]

That should work, unless the listbox is multiselect (and even if single select, an item would need to be selected). Also, that would return the bound column, if different than the displayed column(s).

[tonygg]

Sorry but I am being a bit slow.

I created the listbox in the report and called the report from the form. All other text boxes populated but this listbox remained blank.

The source listbox has 22 columns and a variable number of columns (depends on what is selected in the form). I ideally only want/need to display the 1st 5 columns.

I am not familiar with multi select and single select. Please could I trouble you to let me know where I check these values (on the report or form or both and where in the field properties?)

Many thanks

Tony

[pbaldy]

You mean a variable number of rows (records)? When you say you have the listbox on the report, are you wanting it to display the same records as displayed on the form? If so, you'd need to give it the same row source as the one on the form.

[tonygg]

Sorry to be clear the listbox contains calculated data and user selections that cannot be recreated by a query.

It it has a fixed number of fields but there could be between 1 and 30 rows in the listbox.

i just need to display the first 5 columns of the listbox on the report.

thanks

[pbaldy]

Again, if you want the listbox on the report to display the same records as the listbox on the form, they need to have the same row source. Perhaps this will work in the format event of the section of the report containing the listbox:

Me.ListboxName.RowSource = Forms!FormName.ListboxName.RowSource

[tonygg]

Thank you. That gets the data over to the report if I open the report independently (after running the form). However when I try to open the report from the form using:

DoCmd.OpenReport "LUCY BARCLAY RECON REPORT", acViewPreview

I get the error: "You cant set rowsource property in print preview or after printing has started."

Any suggestions to get around this would be gratefully received.

Thanks

Tony

[June7]

What exactly is the SQL statement for the listbox RowSource property?

[tonygg]

It is populated from a value list not an sql query. Thanks

[June7]

Therein lies the issue.

I don't even see how the report opened manually could work, unless maybe it is opened in Report or Layout view.

Listboxes are not even intended to be used on reports. I have done that only once.

[tonygg]

Ok but it does work if opened in report view with all the data sourced from the form using the method pbaldy helpfully gave me (above) and can be printed by using a DoCmd.PrintOut command in the report itself.

The only issue I have is that I cant launch it from the form it references. If anyone can help with this issue of launching from the form then I would be grateful.

[pbaldy]

Can you post the db here?

[tonygg]

OK. This link:

https://onedrive.live.com/redir.aspx...int=file%2cmdb

gets you the DB (Its too big to attach here as it is from a system - I have just added some functionality). It has 1 form and 1 Report and the full set of tables (I have removed the rest of the forms and reports).

In order to correctly run the report you need to open the form and you will see at the top of the form two combo boxes (Batch from and batch To). You need to select a batch number from each of the drop down lists. I suggest selecting batch 1 (from) and batch 5 (to). You will see a button next to these called "Get Batches". Press this button and the form will be populated. Once done if you open the report you will see it is correctly populated with the same data (or a cut down version of the data).

What I would really like to do is have a button on the form to launch the report and print it. I have tried but it crashes the system with the errors in the above posts

Hope it helps

Thanks a lot

Tony

[June7]

Maybe Paul can help. I can't download from file share sites at this location and if the file is large won't be able to at home on my slow internet.

How can a db with 1 form and 1 report be too large? Did you remove most data, Compact & Repair, zip (2MB zip allowed)?

What does 'from a system' mean?

[pbaldy]

Moving the code to the report open event seems to work; it opens in preview mode without error anyway.