Dynamic List Box

[handlebarry]

Hi,

I have a report that lists properties in Blocks. I have set up a group header that seperates each block. In the header is a list box that is supposed to show the number of properties in each block. Currently I have the below as the list box source:

SELECT Count([1].Block_Code) AS CountOfBlock_Code
FROM 1
GROUP BY [1].Block_Code;

1 is a query that limits the number of blocks.

running this above query gives me the required list, eg:

12
10
11

However the list box only shows the first record (e.g. 12) in each of the groups

Can anyone point me in the right direction please

 

[pdldavis]

Hi, that is an interesting idea. I played around with it and this is what worked for me:

I restricted the list box output by using a textbox in the group header as the filtering criteria.

Then in the groupheader event:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Me.List0.Requery
End Sub

The listbox content changed when the criteria in the text box changed.

Hope that helps.

 

[handlebarry]

cheers for that but not sure I fully understand. Where would the source of the text box come from. The source of the report is Query: 1

I have actually used a subform with a record source as the list box but would prefer your solution as there is about 5 subforms required

thanks

 

[pdldavis]

Hi, I was assuming you had a text box in the group header that showed the block information, which would come from query 1, your report recordsource.

If that is correct, then if you used that text box as the filtering criteria in the list box. It should work and only display the properties for the block in the group header.

So, in your list box rowsource, your block field would =
the text box in the group header.

Does that sound about right?

 

[handlebarry]

I'm with you now

I did actually try that with

SELECT Count([1].Block_Code) AS CountOfBlock_Code, [1].Block_Code FROM 1 GROUP BY [1].Block_Code HAVING ((([1].Block_Code)=[Reports]![contract properties report].[block_code]));

but couldn't work out why it wasn't working

Requery in the group header!
THANKS!!

 

[pdldavis]

Glad it worked. That list box idea of yours will be useful in the future.