ListBox.AddItem in report

[Javawag]

Hi,

I'm not too good with queries so with forms I usually populate ListBox controls on the Form_Load event. The problem comes now as I'm trying to do the same thing in a report, in the Detail_Print event. I get an error telling me I can't change the RowSource while it's printing or previewing.

How can I programmatically populate a ListBox in a report like this?!

Thanks,
- Javawag

 

[KenHigg]

IMHO listboxes have no place in a report. Maybe you can do what you need with a sub-report.

 

[Javawag]

IMHO listboxes have no place in a report. Maybe you can do what you need with a sub-report.

That's my second option. I'll probably go with that, unless it turns out that there is some way to use a ListBox.

- Javawag

 

[boblarson]

Listboxes and combos are really more for forms. They don't really do anything on reports.

 

[Javawag]

Listboxes and combos are really more for forms. They don't really do anything on reports.

I understand that, because they're designed more for input than displaying data.

I've tried using a subreport, but due to the way my tables are designed, some records are showing up more than once as duplicates! I was wondering, is there any kind of control I could use instead of a listbox which can just show a list of items in a column layout? Like a table or something (EDIT: Just to clarify that I mean a table as in a table from a Word Document or webpage, not as in a database table)?

- Thanks,
Javawag

 

[boblarson]

I've tried using a subreport, but due to the way my tables are designed, some records are showing up more than once as duplicates!

Could that mean you have a non-normalized design? If so, I'm not surprised you are having problems. Perhaps if you would enlighten us to the table structure and what you are trying to pull (what needs to be shown) and how it relates to the other data, we might be able to help.

Screenshots or even the database being uploaded could help immensely.

 

[Javawag]

Could that mean you have a non-normalized design? If so, I'm not surprised you are having problems. Perhaps if you would enlighten us to the table structure and what you are trying to pull (what needs to be shown) and how it relates to the other data, we might be able to help.

Screenshots or even the database being uploaded could help immensely.

I can't remember what normalization means - but I believe it means that the database uses multiple linked tables instead of just 1 table, which is true.

I'd love to upload the database, but it's for work an has sensitive information on certain individuals, so I can't. However, I'll try to explain what it does.

I work in a web team that carries out jobs creating and maintaining sections of a government website. The system is designed to log jobs which the web team receives and which elements the job contains (website feature, global email, etc) and it also allows (through forms) certain staff members to be assigned to certain elements of these jobs (See the attached entity relationship diagram!).

The report is designed to take a time period as an input, and output a list of jobs that each staff member has been allocated to. The problem is that very often a staff member may be allocated to more than one element of a job - so the job appears multiple times, once for each element. If I could handle this through code, I could easily filter this out, but it's more difficult with a report!

I'll also attach how the report looks, and how I'd like it to look - whether it's a subreport or a listbox.

Hope this helps,
- Javawag

 

[boblarson]

Okay, your table structure IS normalized and looks good to me. What is happening on the report is that it looks like you would need to

Use a grouping query so you get the grouping for the Title, ServiceName, Date, and Alloc. You could then use an IIF to display the Element if no dups and in the IIF you could use a DCount if it isn't all.

Or you can use the Grouping to create grouping levels and put certain controls in the grouping instead (but this would put them at different levels of the line so I'm thinking you don't want that).

 

[Javawag]

Okay, your table structure IS normalized and looks good to me. What is happening on the report is that it looks like you would need to

Use a grouping query so you get the grouping for the Title, ServiceName, Date, and Alloc. You could then use an IIF to display the Element if no dups and in the IIF you could use a DCount if it isn't all.

Or you can use the Grouping to create grouping levels and put certain controls in the grouping instead (but this would put them at different levels of the line so I'm thinking you don't want that).

Your first suggestion sounds good to me! Is that just using a SQL query with "GROUP BY" in it, and then showing in the elements column something like (psuedo-code):

if elementID = 0 'all elements

show "all elements"
​

else if no dups 'just one element... use DCount to check this

show "<element name>" 'using function to get name from elementID
​

else

show "<n> elements"
​

end if

Using I imagine DCount to count how many elements are allocated to one person - and my built-in GetElementName() function to get the element name.

Tell me if there's anything wrong in my thinking here! I'll have to wait until Tuesday to try it out at any rate as I'm off work for Easter

Thanks for your help,
- Javawag

 

[Javawag]

Sorry to double post, but i thought i would explain how i solved this. I did try the suggestion of using a SubReport with a GROUP BY clause, which seemed to sort of work, but to have more power and control over how the data is pulled out and presented, I committed the ultimate sin. I actually created a form instead with a ListBox. Now i just use DoCmd.OpenForm "frmReport", acPreview to view a print preview like a report! Perfect!

- Javawag