Help with ListBox on report using VBA code

[Steve Perrins]

I have a report which contains a listbox that I need to fill with data
collected from VBA code. The report is based on a form that does the same
thing. I have set an OnOpen event within the report to fire off the code.

Code snippet:-
Me.List0.AddItem "Development Name;No. Units;Completed;Exchanged;Value
Of Exchange;Under Offer;Available;Est Comp;Contract Out"

When I run the report I get the following error message :-

Run-time error 2191
You can't set the Row Source property in print preview or after printing has
started.

I can understand the reason for this as the system needs to know what has to
be printed/previewed before the report is displayed, but, according to the
help the Open Event for reports occurs before a report is previewed or
printed.

I have also tried setting the reports listbox from within a form event and
then opening the report with the do command but you can't access the reports
controls before it is opened.

Any suggestions on how to dynamicaly build a listbox in a report from VBA
code would be greatly appreciated.

Thanks,

Steve Perrins.

 

[Albert D.Kallal]

hum, where are those values coming from?, What value out of the list box do
you want to display?

It seems to me that you just type the values into a table, and base the
listbox on that.

Something seems not right here, as filling up a list box in code don't make
too much sense, since that code has to get the values from some where
(hard coding them don't make sense either).

You perhaps just want a text box on a report...and fill it with your
choices????

Is the report to display ONE choice from the list box for each record...or
are you trying to just display a bunch of values in the listbox?

 

[Steve Perrins]

Albert,

Thanks for your reply.
Just to clarify, the vb code is calculating totals from a fairly complex
query across two tables and entering these nine totals for each group of
records in a list box.
It's the same code as I am using for the form that does the same thing and
works great, the trouble with a form is that it does not produce suitable
output for printing.

I have now bypassed the problem by using the vb code to produce a recordset
which I procees in the code. The code then builds the formatted data for the
report.

Regards,

Steve Perrins
SCP Computers
http://www.scp-computers.co.uk

 

[Albert D.Kallal]

yes...just want more info before i send you down the wrong path..


Often, just a txt box on the reprot will do the trick. You set the text box
to can grow = yes, and in the reprots detail

on-format, you simply load up the text box

for i = 1 to 10

strBuf = strBuf & i & vbcrlf

next i

me.MyTextBox = strBuf

The above would display a text box with 1 to 10 in it.......

You could well do the same in your case.....

Note that you could also program fill the listbox using call backs also,
but I don't recommend that for report....

You can also consider building a sub-report...and dropping that into the
detail section of your existing report...

 

[Steve Perrins]

Albert,

Thanks for your help and suggestions for this. I might well use your idea of
using a text box as it will simplify the code somewhat. This is just as well
as last night Access crashed whilst I was working on it and had very nearly
completed the project. The result of the crash being it corrupted all the
VBA code and wouldn't let me open the code editor for that database. All
recovery failed to get the code back so I am now starting from the begining.
Last weeks back up has given me a start point though.

Thanks again,

Steve Perrins
SCP Computers
http://www.scp-computers.co.uk