ListBox containing field column names

[fergler]

I have a dialog form used to filter data to a report. I want to be able to use a list box to decide which field columns from a table or query I want on the report. I would guess it would work like the Access form wizard. The listbox choices would be a single column of the the actual field column names, ie:

FirstName
LastName
JobTitle

rather than the usual output: John Doe Supervisor
Mary Doe Principal

Does this make any sense, and is it possible to do?

Thanks.

 

[pbaldy]

It is possible but a little tricky. First, you'll have to build the query in code, looping through the listbox to get each field name. Searching here on multiselect should turn up the code. Secondly, your report will have to be dynamic enough to accept the different fields. In other words, if you have a field with a control source of "FirstName" and the user doesn't select that field, it will throw an error.

 

[fergler]

Paul:

I already have a report created that includes all the possible fields that could be selected. Would it be possible to write the code so that any fields not selected would be "invisible." My immediate problem is how do I get the column names in the list box? Would I just use a value list? I'm also planning to use the AddItem and Remove item code to move the chosen selections from the main list box into another box.

Thanks.

Jennifer

 

[pbaldy]

There's actually a third row source type option called Field List that would probably work for you. You could code them to be invisible, though that probably leaves you with a spacing problem. If I were foolish enough to try and design a dynamic report I'd probably use unbound controls (Text1, Text2 etc) and code in the open event that examined the source query and assigned the control source in order, so that my fields would fill in on the report from left to right.

 

[fergler]

That's cool. I never knew you could bring in the field list names that way. Now I guess I'm going to have to change the field names in my query to something user friendly.

I've previously tried to design a dynamic report, based upon various code I've found on the internet, to no avail, but I'm game to try again .

Thanks for you help.

Jennifer

 

[pbaldy]

Happy to help. I don't think you're getting what I mean about the report. You say you designed one with all possible fields. What's going to happen when you open that report with a source query that doesn't contain all fields? It will error on every one (more accurately, pop up a parameter box).

I was kidding about it being foolish to create a dynamic report by the way. I've designed several, but all of them were crosstab reports, which have to be dynamic, since the field names change based on the criteria selected. I use code from the Access Developer's Handbook, but there's also:

http://support.microsoft.com/kb/328320/en-us

I think the ADH method is better, but this may give you some ideas.