Form Design - All fields in listbox

[aravindhan_3]

Hi,

I have table which has 5 Fields
Week No, Sales, Volume, Profit & Cost

I wanted to create a query where the user inputs the Week number and the results comes out. this works fine.

What I need to do is:-
create a Form, in that I have a list box which lists out all the week numbers,
then create another Listbox which should have all the fields like Sales, Volumes, Profit & Cost.

When the user select any week from List box1 and select the fields that he wants from the List box 2, then result should come up.
User may select Week 1, Week 2 etc & Select only Fields Profit & cost and when click ok, the results should come.

Can you guys give me a guidance how do I do this

Regards
Arvind

[orange]

Can you post a copy of your database in mdb format?
Remove anything confidential/personal.

[aravindhan_3]

Hi Orange,

Attached the sample file with form design.

[aravindhan_3]

Can anyone help me on this please?

[jonh]

Hi!

This example uses a sub form displayed as a datasheet where the columns are displayed/hidden as required.

Hope it helps...

++edit : simplified

create a form
add two list boxes
call one WeekNum and the other FieldList
set both list boxes multiselect property to Simple
set rowsource for WeekNum to load the week numbers
set rowsource for FieldList to your table's name and set row source type to Field List
add a sub form control called SubFrm
set its source object to your first table name
add the code below to your list box click events
save the form

NOTE : I called my week number field 'WeekNo'. I've marked it in the code where you need to change it.

Private Sub FieldList_Click()
For i = 0 To FieldList.ListCount - 1
[SubFrm](FieldList.ItemData(i)).ColumnHidden = Not FieldList.Selected(i)
Next
End Sub

Private Sub WeekNum_Click()
For Each v In WeekNum.ItemsSelected
If s <> "" Then s = s & ","
s = s & WeekNum.ItemData(v)
Next
SubFrm.Form.Filter = "[WeekNo] in (" & s & ")" '<< CHANGE FIELDNAME AS REQUIRED
SubFrm.Form.FilterOn = True
End Sub

[aravindhan_3]

Hello John,

Thanks for your help,

I created a form as you said below:-

When i select any one week, it asks to input the week number, when I give week number, I select the fields from listbox 2, and that gets the details in subform,
but it gives data for all the weeks even if I input any one week number?

is there a way to change this like :-
just select week number ( no input), and select the field lists that I need and click ok and then the results in subform

Thanks for your help

[jonh]

It sounds like the field you are trying to filter on isn't in your recordset or has been misnamed.

You generally get an input box like that in Parameter Queries. And if Access gets confused it can think you're trying to run a Parameter Query when you're not.


So, make sure you changed the field name in the second piece of code as I said, or if you've used a query instead of a table, make sure that the week number field is part of the output.