Adding New Entries / SQL Update
in [Help]
|
From: Prakash on 12 Jun 2010 09:00 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.
From: Allen Browne on 12 Jun 2010 09:34 -- 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(a)gmail.com> wrote in message news:353ec710-6d99-4664-a947-b941836f0115(a)32g2000prq.googlegroups.com... > 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.
From: Allen Browne on 12 Jun 2010 09:45 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#DAORecordsetExample 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(a)gmail.com> wrote in message news:353ec710-6d99-4664-a947-b941836f0115(a)32g2000prq.googlegroups.com... > 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.
From: Prakash on 12 Jun 2010 10:51 On Jun 12, 5:45 pm, "Allen Browne" <AllenBro...(a)SeeSig.invalid> 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#DAORecordsetExample > 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" <prakashwadhw...(a)gmail.com> wrote in message > > news:353ec710-6d99-4664-a947-b941836f0115(a)32g2000prq.googlegroups.com... > > > > > 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. 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.
|
Pages: 1 Prev: Java Developer , Jersey City, NJ Next: Access 2007 bug with SQL Server Back End |