Record Cloning - Form Coding

[TomHo]

I need assistance understanding how to get Access 2010 to replicate data after it has been entered into a form.

There are four fields to be completed on the form:
1. Type
2. Part Number
3. Part Subnumber
4. Number of Items

The entry will be performed for the master item (type = "Case")
When entering the "Case" information the "Number of Items" field will represent the number of items to be placed in a case

After entering the "case" information and upon exit...

1. Post the entered information into the table
2. Create additional records based upon the number set in Number of Items field (Maximum of 6 items for a total of 7 records)
3. Replicate the Part Number and Part Subnumber fields
4. Place "Item" in the Type field for each replicated entry
5. Place the item number 1-6 in the Number of items field (minimum of 1 and maximum of 6).
6. Item numbers are to be consecutive

There are many options in Access 2010 but I am unsure how to make this happen in an efficient manner.

Any assistance is appreciated.

 

[MikeLeBen]

I'm assuming you might use something along the line of:

private sub cmdEnterInfo_click()

dim intloop as integer

if IsNull(me.[numberofitems]) Then
    msgbox "Please insert number of items first!", 0, "Notification"
else
    'DoCmd.SetWarnings False
    for intloop = 1 to Me.[NumberofItems]
        DoCmd.RunSQL "INSERT INTO tblname (Type, [Part Number], " & _
                             "[Part Subnumber], [Number of Items]) VALUES(" & _
                             "Item, " & Me.txtPartNumber & _
                             ", " & Me.txtPartsubnumber & _
                             ", " & intloop & ");"
    next intloop
    'DoCmd.SetWarnings True
end if
end sub

 

[TomHo]

Thank you. I'll check it out.