Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-04-2013, 07:22 AM   #1
Bridiewms
Newly Registered User
 
Join Date: Nov 2013
Location: South Wales
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
Bridiewms is on a distinguished road
Unhappy Generate Number of Rows In Report Depending on a Number in Another Field

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?

Bridiewms is offline   Reply With Quote
Old 11-04-2013, 07:26 AM   #2
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Generate Number of Rows In Report Depending on a Number in Another Field

How about 'Create and use subreports' ?
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 11-04-2013, 08:10 AM   #3
Bridiewms
Newly Registered User
 
Join Date: Nov 2013
Location: South Wales
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
Bridiewms is on a distinguished road
Re: Generate Number of Rows In Report Depending on a Number in Another Field

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.

Bridiewms is offline   Reply With Quote
Old 11-04-2013, 08:20 AM   #4
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Generate Number of Rows In Report Depending on a Number in Another Field

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, '' As signatureColumn, '' As printNameColumn, '' As signDateColumn
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 !
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 11-05-2013, 02:14 AM   #5
Bridiewms
Newly Registered User
 
Join Date: Nov 2013
Location: South Wales
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
Bridiewms is on a distinguished road
Re: Generate Number of Rows In Report Depending on a Number in Another Field

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

Thank you
Bridiewms is offline   Reply With Quote
Old 11-05-2013, 06:19 AM   #6
Bridiewms
Newly Registered User
 
Join Date: Nov 2013
Location: South Wales
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
Bridiewms is on a distinguished road
Re: Generate Number of Rows In Report Depending on a Number in Another Field

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
Attached Images
File Type: png Example Report.png (44.4 KB, 65 views)
Bridiewms is offline   Reply With Quote
Old 11-11-2013, 07:44 AM   #7
Bridiewms
Newly Registered User
 
Join Date: Nov 2013
Location: South Wales
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
Bridiewms is on a distinguished road
Re: Generate Number of Rows In Report Depending on a Number in Another Field

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

Is what I am trying to achieve possible?

Thanks

Bridiewms is offline   Reply With Quote
Old 11-11-2013, 08:10 AM   #8
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Generate Number of Rows In Report Depending on a Number in Another Field

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)
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
The Following User Says Thank You to pr2-eugin For This Useful Post:
Bridiewms (11-13-2013)
Old 11-13-2013, 05:37 AM   #9
Bridiewms
Newly Registered User
 
Join Date: Nov 2013
Location: South Wales
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
Bridiewms is on a distinguished road
Post Re: Generate Number of Rows In Report Depending on a Number in Another Field

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!
Attached Images
File Type: png Example Report.png (44.4 KB, 63 views)
Attached Files
File Type: accdb Stripped Database.accdb (1.16 MB, 54 views)
Bridiewms is offline   Reply With Quote
Old 11-13-2013, 07:57 AM   #10
David R
I know a few things...
 
David R's Avatar
 
Join Date: Oct 2001
Location: Kansas City, MO, USA
Posts: 2,628
Thanks: 86
Thanked 149 Times in 148 Posts
David R is on a distinguished road
Re: Generate Number of Rows In Report Depending on a Number in Another Field

http://www.access-programmers.co.uk/...d.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.
__________________
Regards,
David R
David R is offline   Reply With Quote
Old 11-18-2013, 07:11 AM   #11
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Generate Number of Rows In Report Depending on a Number in Another Field

Not sure if this is the best way to go, but this will do what you want to achieve..
Attached Files
File Type: accdb Stripped Database.accdb (868.0 KB, 63 views)
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
The Following User Says Thank You to pr2-eugin For This Useful Post:
Bridiewms (11-20-2013)
Old 11-20-2013, 06:07 AM   #12
Bridiewms
Newly Registered User
 
Join Date: Nov 2013
Location: South Wales
Posts: 19
Thanks: 3
Thanked 0 Times in 0 Posts
Bridiewms is on a distinguished road
Re: Generate Number of Rows In Report Depending on a Number in Another Field

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.

Bridiewms is offline   Reply With Quote
Reply

Tags
coding , forms , repeat , repeated fields , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto generate invoice number on report Adriano85 Reports 2 07-02-2013 11:38 AM
Auto generate a number in where data field is text Traceyann General 1 07-11-2012 11:19 PM
number rows based on value of a field lala Modules & VBA 10 12-09-2011 09:45 AM
Auto increasing a number, depending on maximum number when condition is met dannewestis General 3 09-22-2006 10:00 AM
number of rows depending on the value TheBud Queries 2 05-28-2006 02:57 PM




All times are GMT -8. The time now is 03:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World