Spreadsheet style Report

[drschlong]

Hi all,

I've hit a bit of a problem while finishing off a report for a database I've been doing.

I have a table which for example hold Addresses and Units (this field is just a number which shows how much of any product that address has ordered). What I am trying to achieve in my report is, say for instance Address1 has 34 Units, on my report I would like to see the address details for Address1 on the top (this bit I will be able to set up myself) and then underneath this I want 34 blank rows (so this part of the report will kind of look like a blank spreadsheet with 34 blank rows) this is the bit I'm stuck on. Then if Address2 has say 90 units the report will then on a new page show the address details for Address2 and 90 blank rows.

I would be extremly grateful if anybody could help me with this.

Thanks,

Steven. C.

 

[mark curtis]

Not 100% sure how to do this but I would look to do something like set a count field up to count the number of orders and then grow the detail section appropriate to the count by cm.


I'm sure some one will offer a better way!

 

[drschlong]

O.K. Looks like I'm gonna have to resort to bribery to help me solve this one.

I will buy and send the first person who can help me solve this problem a DVD (I don't mean boxed sets, my job doesn't pay that well ) of their choice (as long as its not some really obscure one that is impossible to get hold of).

I know there have been posts on other parts of this website concerning people who come on here to more or less get other people to create their databases for them, but as someone who was thrown in at the deep end with Access with their job I have found this site invaluable as a teaching aid. It is thanks to all the experts on this site that I am now quite comfortable working with Access and can accomplish most things asked of me in work without having to ask other people how to go about it.

It's only when I get really stuck that I ask questions and of course when somebody does help that mean that's an extra bit I've learned that I don't have to ask about in the future.

Thank you all for your help - Past, Present and Future.

Steven.

 

[mark curtis]

post your db here and I will look at it, no DVD required!

 

[drschlong]

Mark,

Even when zipped the zip file is 110KB so I can't attach it. Can I send it to you via E-mail.

Cheers,

Steven.

 

[Mile-O]

Have you compacted it first?

 

[drschlong]

I've taken out some of the forms and reports so I can attach it now.

The names I used in the original question are different as I tried to make the question as easy to understand as possible. The table which holds the data is called tblSiteAddress, the report in question is called rptSchedule and this is based on the query qrySiteAddress. As you can see from the report each site has a number to indicate how many gas units there are for that site, so if there are 34 gas units there will be 34 blank rows.

You will need to have the form frmHTAMSupplepointOffer open when running the report as the query uses a reference number from that form as one of it's parameters.

Cheers,

Steven.

P.S. Ignore the color scheme of the form, this is what the person
who is going to be using the database wanted.

 

[Mile-O]

I don't ever use reports so I can't help you there but one design problem for a user interface viewpoint that I've noticed was that when I clicked on add site I was presented with a modal form.

You have a "save record and close form" button but what happens if the user accidentally clicked on that button or decides that they don't want to add a site after all? You are forcing them to enter a site before you can let them cancel it - a cancel button would be useful for your users.

 

[drschlong]

Mile-Oh-Phile,

When you click the Save Record and Close Form button it then asks if you want to save it, you can then click either Yes which will save the record or you can click No to cancel it. It should work on the copy I previously attached.

Cheers,

Steven.

 

[Mile-O]

Yeah, it does - but I still have to enter the number of units before I can cancel it, and I don't actually have to put a site address in.

If I, as a user, clicked on Add Site in error you are effectively asking me to enter details that I don't want to enter.

I'm just thinking easy to use!

 

[drschlong]

I see what you mean now.

Try this one, I've added a cancel button to it.

 

[directormac]

Looking briefly at your first attachment, I'm not sure WHY you want a report with all those blanks on it. BUT, I'd probably use a bit of VBA code to create a temp table with a # of records equal to your # of units, then whip up a subreport with one "blank line" in the detail sourced to the temp table, and insert that sub into the detail listing of your main report.

There's got to be a better way, but that's what I'd do with my little bit of knowledge...

What's the POINT of this report? It seems there must be something more useful than a page with a series of lines on it... heck you could use a legal pad and save a bunch of hassle... What will be done with these pages once they are printed out?

--Confused Mac

 

[drschlong]

directormac,

The report will be included in documentation which is sent to my company's customers. The need for the blank lines is that the customer will then fill out the blank lines of this report with certain information and then send the documentation back to us.

So if for example there were 34 new houses being built on a particular site the customer would have 34 lines to fill out (1 line for each house).

Hope that makes sense.

Steven.

 

[directormac]

Makes perfect sense. If there is any kind of identifier for each of the houses (or whatever) that you have recorded before the client fills out the paperwork, I'd suggested adding that to each line to help them organize. Otherwise, just:

#1: _________________
#2: _________________

etc., etc., which you can achieve with the temp table idea.

--Less-Confused-Now Mac

 

[Rich]

http://support.microsoft.com/support/kb/articles/Q119/0/73.asp?PR=ACC

 

[drschlong]

Figured it out now,

I used a piece of code that was on Microsofts Knowledge base and adapted it slightly. This is what it looks like:

Option Compare Database 'Use database order for string comparisons
Option Explicit

Global TotCount As Integer

' Call the SetCount() function from the group header section's
' OnPrint property using the syntax: =SetCount(Report)

' Call the PrintLines() function from the detail section's OnPrint
' property using the syntax: =PrintLines(Report).

Function PrintLines(R As Report)
TotCount = TotCount + 1
If TotCount = R![Gas_Units] Then
R.NextRecord = True
ElseIf TotCount < R![Gas_Units] Then
R.NextRecord = False
End If
End Function

Function SetCount(R As Report)
TotCount = 0
End Function

On my report I have a text box which shows the number of [Gas_Units] and then the above code prints out the correct number of lines to match that number.

Thanks for everybody's help with this.

Cheers,

Steven