VBA code for Adding Additional Rows to Details

[Bridiewms]

I have a form where we fill in information for supply of PPE 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 number of 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.

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

 

[Cronk]

Others more proficient with queries might be able to solve your need that way.

I'd be using VBA to populate a temporary table by appending as many records as there are occurrences of each item and base the report on that table.

 

[Bridiewms]

I have read up on temporary tables but cannot seem to get an understanding on how to produce the results I want. I have tried docmd. to add new records, I have tried Do Until Loops and tried to research subreport as suggested by another user but am not getting anywhere.

Any change you can provide me with additional information on how to acheive these results?

Thanks

 

[Cronk]

Open a recordset based on your query. Loop through that recordset. Set up another loop or For/Next construct repeating as many times as there times the item has been issued. In the inner loop, add a record with the itemID to the temporary table.

Try that and if you want more help, come back and post your code.

 

[Bridiewms]

http://www.access-programmers.co.uk/forums/showthread.php?t=256295

This thread solves the issues.