Generate Number of Rows In Report Depending on a Number in Another Field (1 Viewer)

Bridiewms · Nov 4, 2013

I have a form where we fill in information for supply of equipment to employees.

Each item must be signed for on a printed report.

I am encountering problems trying to create enough rows in my report detail for each signature of the items supplied.

For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.

What would be the best way to do this?

pr2-eugin · Nov 4, 2013

How about 'Create and use subreports' ?

Bridiewms · Nov 4, 2013

Thank you for the suggestion.

Unfortunately I don't think this will help in this situation. The Print Name, Signature & Date are all unbound fields not allocated to any tables or queries. They will have no real significance in the database. This report will only be relevant for paper use so the rows need to be blank fields.

I have looked at loop coding but wouldn't know where to start.

pr2-eugin · Nov 4, 2013

No ! I should have been a bit more clear.. You can edit the the recordsource of the Report to have three additional UNBOUND columns, something like..

Code:

SELECT someField1, anotherField2, [COLOR=Blue][B]'' As signatureColumn, '' As printNameColumn, '' As signDateColumn[/B][/COLOR]
FROM theMainTable INNER JOIN theChildTable ON theMainTable.someID = theChildTable.commID;

you can use this three unbound columns to be the "boxes" you wish it to appear on the SubReport section..

You do not have to go into complicating the process with huge coding yet !

Bridiewms · Nov 5, 2013

Ok no problem I will give this a go today and let you know how I get on.

Thank you

Bridiewms · Nov 5, 2013

Sorry if I'm being dull. Any chance you can explain using my data...

The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)

IssueID DateProvided Item_Name AmountIssued
0001 01/11/2013 Gloves (Orange) 10
0001 01/11/2013 Hard Hats (Black) 2
0002 02/11/2013 Hi Viz (Large) 5

The report I aim to generate from this should look as attached - Please excuse my drawing!

The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.

Please could you provide me with as much information as possible in regards to how to achieve this, I am struggling to put something together.

Thank You

Bridiewms · Nov 11, 2013

Had a few views but no responses. Amy reason why?

Is what I am trying to achieve possible?

Thanks

pr2-eugin · Nov 11, 2013

Show the Current Query you have Or better Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

Bridiewms · Nov 13, 2013

Hi,

I have attached the stripped down database. The tables are usually attached to a back end but for this purpose I have copoed over the required tables.

The information has been taken out and 2 records for examples have been entered.

There is only one report on the database, this is the one I am having problems with. I want rows to add depending on the amount of PPE supplied as described in the photo which i have also attached.

If you go into VB there is a comment for where I am hoping to put the code.

Further information is detailed on the first post.

if you require any addition information please let me know.

Thank you!

David R · Nov 13, 2013

http://www.access-programmers.co.uk/forums/showthread.php?t=250517 combined with a subreport might be useful - it shows some ways to generate a 'loop' of dummy records, but you should be able to adapt it to a row.

pr2-eugin · Nov 18, 2013

Not sure if this is the best way to go, but this will do what you want to achieve..

Bridiewms · Nov 20, 2013

Thank you so much. This really clearly things up and gave me another perspective to resolve my issued.

In the end I used the table you produced with the numbers and just linked these numbers to the "amount issued" field in a query which produced the required number of rows. This query then worked to produce the report as designed.

Generate Number of Rows In Report Depending on a Number in Another Field (1 Viewer)

Bridiewms

Registered User.

pr2-eugin

Super Moderator

Bridiewms

Registered User.

pr2-eugin

Super Moderator

Bridiewms

Registered User.

Bridiewms

Registered User.

Attachments

Bridiewms

Registered User.

pr2-eugin

Super Moderator

Bridiewms

Registered User.

Attachments

David R

I know a few things...

pr2-eugin

Super Moderator

Attachments

Bridiewms

Registered User.

Similar threads

Users who are viewing this thread