Access 2010 reports hassle 2

[Domino2]

Can someone tell me how to overcome problems I am getting with Microsofts latest creation 2010.

Grouping and Sorting.
All the work is done in the lower panel, when more than a couple of lines of instructions, there is no scrollbar to navigate up and down the Grouping and Sorting instructions. So is this what we have to work with?


When I have a report thats generated from the contents of a listbox on a form (query by form) then when I try to work on the report itself I get numerous error messages as the grouping and sorting instructions/data is not in a joined query.(table fields)

Lastly, is it feasable to design a report based on a query first to get whats wanted before going over to an SQL driven, list filled rowsource origin?

Thanks

 

[Domino2]

Bit more, basically I am trying to have a report layout which is broken up into 3 groups. First group is Partner(Company Name) wanting each Partner Name to appear once. Second Group is Date Raised (Date just appears once) together with related data ascending below. Third Group is Updated On, where Updated On date only appears once whith relative data in ascending below.

I have been moving things around and getting nowhere. Should I have 3 header sections and 3 detail sections? I only seem to have 3 headers on 1 detail. I cannot see how to add detail lines/sections if this is so. Anyone have any guidamce, thanks

 

[dhookom]

You can only have one detail section unless you use subreports.

If you pasted the datasheet view of your records into Excel, could you sort them in the order you want?

Duane
Hook'D on Access
MS Access MVP

 

[sxschech]

Depending on the type of data you are displaying, you could treat a header section like a detail section. I have done this for reports where I have grouped data by various levels or alternatively as dkhookum suggested, you could use subreports. Fortunately, or unfortunately, I haven't had to work much with the sorting and grouping panel in 2010 as the reports were created in 2003 before we upgraded.

An example is a report that shows Ethnicity (Header/Footer) and within Ethnicity, Gender (Header/Footer) and finally detail, also the report has a subreport in the Ethnicity Footer.

 

[Domino2]

Thanks both, sorry for delay getting back, struggled though it. It's a shame the report cannot produce some form of code to allow showing problems I had. I ended up making a query first to drive the report, as in an unbound mode the levels of grouping and sorting are not referenced/labeled, so if you make a mistake it's not easy to see what line to remove. In addition, when the report is unbound you get error messages saying the expressions used in grouping sorting are wrong. Anyway I managed to get where I wanted, and it was helpful having confirmation on the number of detail sections being the norm, and subreports. So thanks both very much.

 

[Domino2]

Still struggling with reports in Access 2010. I have a report which works fine if it has an inbuilt query, but if its source is a listbox on a form its shot.

Query:
SELECT PARTNERS.Partner, ACTIVITIES.[Date Raised], PROGRESS.[Updated On]
FROM (PARTNERS INNER JOIN ACTIVITIES ON PARTNERS.ID1 = ACTIVITIES.ID1) LEFT JOIN PROGRESS ON ACTIVITIES.ID3 = PROGRESS.ID3;

The report layout contains a report header, and 3 Group headers:

=[Partners]![Partner]. Header

=[Activities]![Date Raised].Header

= [Progress]![Updated On].Header

Detail - Blank area

This report produces everything where it should be and all records.

If I refer the report that has a list filled with the same query it's no good at all.

I refer my report to the listbox on the form with:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Forms!TabbedForm!LP.RowSource
End Sub

Can anyone tell me where its going wrong. I want to be able to design reports 1st from an inbuilt query, so ll the hassle of unbound data is not there until the report is finished.

Thanks

 

[dhookom]

I've never seen this type of solution but it probably meets your needs.

Consider
Prior to opening the report, set the SQL property of a saved query to match the Row Source of the listbox:

CurrentDb.QueryDefs("qselMyReportRS").SQL = Me.LP.RowSource

Make sure your reports record source is set to qselMyReportRS.

Duane
Hook'D on Access
MS Access MVP

 

[Domino2]

Many thanks for the suggestion. My report is linked to the rowsource of the listbox LP, and is filled by SQL statements comming from query by form.

I don't know why it works from a query okay but not from a listbox's rowsource on a form, when to try out the report I make both queries identicle.

I did wonder if I could reference my report to my SQL in some way which is the SQL statement that is used for the rowsource of LP, to see if that would work.

I was not too sure how to implement your idea, and whether it would have related to a fixed, saved query.

 

[dhookom]

You never explained what you meant by "it's no good at all". We also can only guess if the saved query solution I suggested works for you or if you are still looking for more assistance.

Duane
Hook'D on Access
MS Access MVP

 

[Domino2]

Sorry, if I could cut and paste what my problem was it would be easier. If the rowsource of the report itself contains a query, the report displays the fields Partner, Date Raised, Updated on, for every record in the database. If the same query syntax is put into the rowsource of a listbox LP, the listbox shows all the rows, columns in the list, but the report if sourced from LP's rowsource just produces just part of one record in.

I did not understand how to implement "CurrentDb.QueryDefs("qselMyReportRS").SQL = Me.LP.RowSource

"set the SQL property of a saved query " What do I put in the query?.

Sorry I look a bit dumb but having never done this before I don't know where to begin.

Thanks again

 

[dhookom]

Create a saved query with SQL that might match the RowSource of the listbox. Name the query "qselMyReportRS". Set the Record Source of your report to [qselMyReportRS].

Can we assume you are opening the report from the form that contains the listbox? If so, add code like:

CurrentDb.QueryDefs("qselMyReportRS").SQL = Me.LP.RowSource
debug.Print "Record Source: " & CurrentDb.QueryDefs("qselMyReportRS").SQL
DoCmd.OpenReport "YourReportNameHere", acViewPreview

Duane
Hook'D on Access
MS Access MVP

 

[Domino2]

I just tried two things. The holding string variable for my SQL code which is the rowsource for the form listbox LP is MySql. I just saved the variable globally in a module. I then found 1 page in print preview was filled. I then set my page to landscape and now all pages of the report are being produced.

Does this make sense, should I have to declare the variable I am using as a rowsource in a listbox globaly?

Why do I get more pages in report preview when changing to landscape from portrait when I am not exceeding data across a page in portrait.

 

[dhookom]

I don't know. You didn't see fit to follow my instructions and haven't shared the code or the SQL view or screen shots or much else.

Duane
Hook'D on Access
MS Access MVP

 

[Domino2]

I think I just got away as your post was arriving. I will try your idea out as need something better than the hit or miss situation at present. It's so difficult designing a report with unbound data first, throws up errors when the grouping and sorting section is open at the bottom of the page. Also you cannot seem to scroll down that section to get access to a grouping section, and being unbound nothing is labelled in the tree structure.
I will get back

 

[dhookom]

I don't see any reason to create an unbound report.

Duane
Hook'D on Access
MS Access MVP