Can you Group&Sort in a form like in a report

[Big Pat]

Hi,

I have a nicely formatted report, but I want to have a clickable event. I'm using Access 2003 and the IT dept has no plans to upgrade to Access 2007. So I searched a bit and found this thread, where the suggestion is to create a form that looks just like the report.

So, I've made a start on this, but I ran into a problem. My report 'GROUP's on one field in the underlying query, by showing that "header" once and then 'SORT's on another field. Is it possible to duplicate this behaviour in a form?

I clicked View>Toolbars>Customise and dragged the "Sorting and Grouping" button onto the toolbar, but it's greyed out so I'm guessing it's not possible. The forms needs to be in continuous view, just in case that makes any difference.

Thank you,

 

[CBrighton]

You should be able to have a continuous main form with the recordset being a distinct list of what you want to group by, then have a continuous subform in the detail displaying the records which relate to that field.

As for sorting, that's defined by the query behind the form so you can set that however you want.

That should replicate the grouping & sorting from your report.

 

[Big Pat]

Hi,

Thanks a lot for your help.

I didn't think you could have a subform in a continuous form, but it seems people have got round that by putting it in the header or footer (so I've learned something potentially useful already) I've tried that and I can see it kind of works, but it's not quite what I need.

Using this method, I have to click on the record in the main form and then wait while the records below update (which is a little slow as it's reading them over the web). But even if I could speed this up, the end users would really prefer to be able to see all records returned by the query in one long scrollable screen. That's why I was going with the report view before, but then realised it's not clickable.

The item I wanted to click is a hyperlink generated by concatenating "http://www.ourwebaddress..." and the value of a certain field.

For now, I'm "printing" the report to a PDF file and it turns out that when you open that file the link IS clickable (Hurray!) but it puts several extra steps into the process, so I'd really like to improve it.

If I can't make further headway with this method, I may post a new question about how to automate selecting a particular printer i.e. the PDF printer and then printing the file to a certain location.

Further suggestions welcomoe though!!

Thanks again.

 

[CBrighton]

I assume you have detail at the grouped level, totals or some such?

If not then if you can do away with a header / footer for the group you could simply sort by whichever field you want to group by before sorting by whatever field you want the group to be sorted by.

I also assume you need all of whatever your grouping by on one form rather than having a combobox allowing the user to select which one to view which would update the rowsource of a continuous subform.

 

[Big Pat]

The query returns records relating to clinical trials matching text the user searches for. Most fileds will be different for each record, but there's one field, like a "Category" that may repeat several times.

The nature of the task is that some users will be very focused on the Category and will want to ignore all others, so that is the only field in the header. Everything else is at the detail level. It did occur to me to bring the header down into the detail level too, but it's actually quite a long piece of text (often well ove100 characters) and it would make formatting a bit tricky.

And yes, it works far better with having all "hits" displayed together. Users may not spare the time to click each "header" in turn and then read the detail records.

It's a shame, as the report layout is EXACTLY what they need, but I have to jump through a few hoops to make it clickable.