I have a small form with 4 text boxes viz:

FromNumber, ToNumber, Form_Type, Insurance_Company

When the user enters the parameters & presses a "Generate" button on
my form, I'd like the form to create entries from the "FromNumber" to
the "ToNumber".

eg. if the user has entered: 18, 29, Renewal, Axa ... the form should
create the foll entries:

18, Renewal, Axa
19, Renewal, Axa
20, Renewal, Axa
21, Renewal, Axa
...
...
29, Renewal, Axa

The 3 fieldnames are: FormNumber, Form_Type, Insurance_Company

Could someone kindly post some code using both methods:
a) SQL Update
b) a single statement in a for loop

Also suggestions as to which method would be preferable to use and
why.


Rgds,
Prakash.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Prakash" <prakashwadhwani (AT) gmail (DOT) com> wrote

The simplest way to do this would be to create a counting table: say one
field named CountID of type Number, marked as primary key, and then save the
table as tblCount.

Now enter records from zero to the maximum number of inserts you ever need
to do at once. If you wish, use the code at the end of this page to populate
the table:
http://allenbrowne.com/ser-39.html

Now create a query using tblCount as the source table. In the Field row,
type:
FormNumber: [Forms].[Form1].[FromNumber] + tblCount.CountID
In the Criteria row under this, enter:
<= [Forms].[Form1].[ToNumber]

In the next column, in the Field row, enter:
[Forms].[Form1].[Form_Type]
and in the next column:
[Forms].[Form1].[Insurance_Company]

Now turn it into an Append query (Append on Query menu/ribbon.)
Access will ask what table to append to.
Map these fields to those in your table.

For accuracy, it may help to ensure Access understands the data types of the
fields correctly. Click Parameters (on the menu/ribbon), and enter 2 rows in
the dialog:
[Forms].[Form1].[FromNumber] Long
[Forms].[Form1].[ToNumber] Long
Then set the Format property of both boxes on your form to General Number,
so that only valid numbers are accepted. Finally, make sure you exit those
boxes on the form before you run the query, to ensure the values are
updated.

The alternative approach is to OpenRecordset and loop through them. I don't
believe that would be as efficient, but for an example of looping through a
recordset see:
http://allenbrowne.com/func-DAO.html...cordsetExample
You'll need to use .Edit and .Update.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Prakash" <prakashwadhwani (AT) gmail (DOT) com> wrote

On Jun 12, 5:45*pm, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) invalid> wrote:
Hi Allen,

Thank you so much for your response. I had already gone ahead & tried
fiddling around and managed to get this to work. I'm posting my code
here. Please correct me if required. Also, not more than 200 entries
at a time will be appended. Nevertheless, I've tried upto 5000 entries
and it only took 2 to 3 secs.

Private Sub Cmd_Generate_Click()
Dim rst As Object
Set rst = CurrentDb.OpenRecordset("ROP_Forms")

Dim Current_Number As Long
Current_Number = txt_from_no

Do While Current_Number <= txt_to_no
With rst
.AddNew
!Form_No = Current_Number
!Form_Type = Cmb_Form_Type
!Ins_Co = Cmb_Ins_Co
.Update
End With
Current_Number = Current_Number + 1
Loop

Forms("ROP_Forms").Requery 'refresh underlying form
'Position the underlying form at the 1st record generated !
Forms![ROP_Forms].Form.Recordset.FindFirst "[Form_No] = " &
txt_from_no
MsgBox "Forms Generated !!", vbOKOnly + vbInformation,
"Message..."

End Sub


One more small request ... Before actually appending the entries I'd
like to ensure the field Form_No is not being duplicated. It is a
primary key but I'd like to trap the error rather than Access doing it
at table-level. I'd greatly appreciate any help.


Warm Regards.