code to select various fields on a report

[daveonion]

hi, i have quite a problem with a user. I'm migrating lotus approach databases to access. The user can't understand how access works and its becoming really tricky. Anyway what he normally does is create reports in approach by selecting the fields and inserting them on the report and then deciding which column to sort by. Approach works differently to access because you don't have to go into the back end so much anyway i want a piece of code which will allow the user to select various fields which he wants included in his report, and then the code will layout the fields in the report. A bit like the way a wizard works only i want it as a user interface, is that too ambitious?
heres hopin

 

[SBendBuckeye]

Here's one thought about how you might do that.

1. Give your user a form with a multiselect listbox. Select FieldList as RowSourceType on the listbox Data tab. This will allow him to select the fields he wants on the report.

2. Create a couple comboboxes based on selected fields above which would allow him to select primary sort, secondary sort, etc.

3. Create a query from the listbox selected fields and set your Order By clause from the comboboxes, if specified.

4. Autocreate a report based on the query and you have a starting point.

Sorry this is so brief but I'll be late for work. Hope this helps and good luck!

PS. How would you characterize your coding comfort level? Some of the above will involve some code.

 

[daveonion]

Thanks for your reply, my coding level is not very advanced, although i do use some programming in my databases i would be grateful if you provided an example but meanwhile i'll have a go of what you said.
once again thanks

 

[SBendBuckeye]

Look up the selected property for a list box. The combobox idea was just to allow him to select his sort order if he wanted to do so. If you choose to go that route, you can set the RowSourceType for a combobox to ValueList, which is a semicolon delimited list.

In your listbox, go to the properties Events tab and select the OnLostFocus line and then select the option for either Code Builder or [Event Procedure]. This will generate a little block of code which will run after your listbox is changed.

In that block of code you would spin through the selected lines from the list box and build a semicolon delimited list something like this. You may need to change the Column number to something other than 0 depending on your listbox:

Dim strList As String
Dim intRow As Integer

For intRow = 0 To YourListBoxName.Listcount - 1
If YourListBoxName.Selected(intRow) Then
strList = strList & _
YourListBoxName.Column(0, intRow) & ";"
End If
Next intRow
If Len(strList) > 0 'the user selected something
YourComboBox1.RowSource = strList 'Sort options
YourComboBox2.RowSource = strList
Else
YourComboBox1.Enabled = False 'Disable the sort
YourComboBox2.Enabled = False 'comboboxes if the
End If 'user selected nothing

Hope this helps get you started and good luck!