Generate multiple records from each line of

[techi1961]

Hi,

I have a form called Jobs and a subform called Delivery Dockets. In the the subform I have a the following fields called pieces and description. In the field called pieces our user can enter any numbers from 1 to 20. I need to some how create a code that can generate multiple records for this one field. This will be used to generate multiple delivery reports. For example if the user enters 20 in this field I need to generate 20 delivery notes each with it's own 9 digit unique ID. I was hoping someone here could possibly give me some ideas to approach this.

 

[randy700]

Perhaps (not tested):

Dim x As Integer
x = 1
Do Until x = 10
     [I]code to generate records/reports[/I]
     x = x + 1
Loop

Or

Dim x As Integer
For x = 1 To 9
     [i]code to generate records/reports[/i]
Next x

Randy

 

[dhookom]

You might not need code. You can create a table of numbers [tblNums] with one numeric field [Num] and values from 1 to 20 (or more). Then create a query like:

SELECT [Pieces], [Description], [Num], ...other fields...
FROM [YourTableName], tblNums
WHERE [Num] <= [Pieces]

What do you mean by "for this one field"?
Do you actually need to create and save records or only generate records in a query for you report?
How would you create the "9 digit unique ID"?

Duane
Hook'D on Access
MS Access MVP

 

[techi1961]

Hi Randy,

Thanks, will toy around with your example.

Hi Duane,

Yes, I was thinking the same approach
Basically, there is a data field named "Pieces" on the subform datasheet. Users can enter a number from 1 to 20 or more. What I need to do is generate a delivery note for however many number is entered into this field. So if a user enters 5 I need to print a 5 delivery notes each with its unique number like an autonumber. But the unique number cannot exceed 9 digits. So maybe using the autonumber of the main form which is the JobID and field named "Deliver Docket No" of the subform which is generated using this code:
Private Sub Form_AfterInsert()
Dim LineNo As Integer
Dim rstDetails As Recordset
Set rstDetails = Me.RecordsetClone
If rstDetails.RecordCount = 0 Then
LineNo = 0
Else
LineNo = rstDetails.RecordCount
End If
Me![DeliveryDocketNo] = LineNo

End Sub

And using it in a query of a field

Field: "000" & Jobs.JobID & "" & [DeliveryDocketNo]

Just seem to be stuck on how to keep it as "9 digit unique ID" and to print the number of delivery notes to corresponding number entered into the "Pieces" field.
I don't think I am explaining myself to clearly so I attached a link to the sample db

http://www5.zippyshare.com/v/34689068/file.html

 

[dhookom]

It would really help if you shared some table and field names and data types. Apparently there is a JobID that can be used. If the DeliveryDocketNo is also available you can concatenate the JobID, DeliveryDocketNo and [Num] from the query I suggested.

Again, "Do you actually need to create and save records or only generate records in a query for you report?"

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

If you are going to keep 'delivery notes' in a table, you can use an AutoNumber field, and display it as 9 digit 'number':

Dim intUID As Integer

For intUID = 1 To 50
    Debug.Print Format(intUID, "000000000")
Next intUID

You are going to get:[tt]
000000001
000000002
000000003
000000004
000000005
000000006
000000007
000000008
000000009
000000010
000000011
...
[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[techi1961]

@dhookom
No I don't need to create and save records. only generate records in a query to be used in a report....

Main Form: Jobs
Datasource, Table: Jobs
JobID - Autonumber

Subform: Delivery Dockets
Datasource, Table: Delivery Dockets
Delivery DocketID - Autonumber
JobID - Number
DeliveryDocketNo - Number
Pieces - Text
GoodsDescription - Text

@Andrzejek
No delivery notes' will not be in a table and I can't use an AutoNumber field as the AutoNumber is already generated in each line of the datasheet subform. It's the field "Pieces" with data type text on the line

 

[dhookom]

You should be able to create a report with a record source like:

SELECT Pieces, GoodsDescription, Right("0000" & JobID,4) & Right("0000" & [Delivery DocketID],3) & Right("00" & [Num],2) as UniqueDelivID 
FROM [Delivery Dockets], tblNums
WHERE Num <= Int(Pieces)

Duane
Hook'D on Access
MS Access MVP