What I would do is to create a table of field names that can be used (plus a form to edit this list for when users wish to add new fields.) I would then present a list of those fields in a list box, with its MultiSelect property set to either Simple or Extended so that the user can select multiple entries in the list box. Then you can loop through the ItemsSelected collection and construct an SQL string to select the correct records from your table or query, concatenating it for each item. For example, if the user has selected Field1, Field5 and Field9 in the list box, write a loop like this:
Dim SQLString as string
SQLString = "SELECT "
Dim varItem as Variant
For Each varItem in YourListBoxName.ItemsSelected
SQLString = SQLString & YourListBoxName.ItemData(varItem) &","
The value of SQLString after running this would be "SELECT Field1,Field5,Field9,". You can easily chop the final comma off by using the Left and Len functions:
SQLString = Left(Len(SQLString)-1)
Now you could add to the SQL string like this:
SQLString = "INSERT INTO TempTableName " &SQL String & " FROM TableOrQuery "
This would copy data from your source into a table called TempTableName, but only those fields that the user has selected. You can then use this table as a basis for your reports.
I hope this helps and isn't too confusing!! Good luck.
Elmhurst Solutions Limited
Database Development and Consultancy