Insert data for multiple records with looping

[sandya]

Hi Friends!

Can anyone help on writing a Insert code with looping.

I have designed a Invoice form with subform and using Insert code for enter the data from forms to tables as per following code.

Private Sub btn_Save_Click()

Dim i As Integer
Dim frmSE_B As Recordset
Set frmSE_B = frmSE_B


CurrentDb.Execute "INSERT INTO SE_A(Inv_No,Inv_Date,Inv_Mode,Customer,Pro_Add,Pro_Work,Pro_Disc,Add_Disc,G_Total,Remarks) VALUES('" & txtInvoiceNo & "','" & txtInvoiceDt & "','" & txtInvoiceMode & "','" & txtCustomer & "','" & txtPromoterAddress & "','" & txtWorkArea & "','" & txtProDisc & "','" & txtAddDisc & "','" & txtGTotal & "','" & txtRemarks & "')"


For i = 0 To frmSE_B - 1 Step 1
      CurrentDb.Execute "INSERT INTO SE_B(Item_ID,Qty,MRP,LP) VALUES('" & frmSE_B.txtItem(0, i) & "','" & frmSE_B.txtQty(1, i) & "','" & frmSE_B.txtMRP(2, i) & "','" & frmSE_B.txtLP(3, i) & "')"
      Next
      MsgBox "Successfully done", vbInformation, "Sales"
      
    
End Sub

But in that need to set looping for subform multiple records.

I'm not well to writing VBA code, please can anyone help on this.

Please find below details If require.

Main from : frmSE_A
Subform : frmSE_B

Main form Inputs: txtInvoiceNo, txtInvoiceDt, txtInvoiceMode, txtCustomer, txtPromoterAddress, txtWorkArea, txtProDisc, txtAddDisc, txtGTotal, txtRemarks
Subform Inputs: txtItem, txtQty, txtMRP, txtLP


Thanks,
Sandhya

 

[June7]

Why would you need to use VBA to create records? Form bound to table will pass data directly to table, no code required.

 

[sandya]

Thanks for reply

In the form, all fields are unbound fields. There is no data source from form to table pass the data as directly.

I'm trying to Insert the data with purely VBA without bound fields.

Please help on coding.

 

[June7]

You aren't properly opening a recordset object. Since form does not have a RecordSource there is no way to base a recordset on the form. Code you have makes no since to me. Exactly what are you looping?

Don't use apostrophe delimiters for date/time or number type fields. Use # for date/time and nothing for number.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[sandya]

Yes, your correct I don't have to set any record source and recordset on the form.

I just wrote Insert code but did not declare any variables and recordset. Please guide me on how to declare variables and set recordset as per the attached DB.

In the DB, please open the form frmSE_A is a data entry form. I just want to enter the data manually and save it to table.

Please guide me how to set a proper code for data entry to table.

Thanks.

 

[June7]

Could you answer questions in previous posts?

Why are you using UNBOUND forms?

 

[MajP]

I hope not to sound mean, but I am going to give you some tough love.

I just wrote Insert code but did not declare any variables and recordset. Please guide me on how to declare variables and set recordset as per the attached DB.
In the DB, please open the form frmSE_A is a data entry form. I just want to enter the data manually and save it to table.
Please guide me how to set a proper code for data entry to table.

If you are asking any of those questions, you are way over your head with attempting an unbound form and you will never get there. You need to go with a bound form. So explain in detail why you want an unbound form?

 

[sandya]

My thought, If design unbound forms then I can use in 2 ways as same requirements of Sales and Purchases. That is the reason I'm trying to go only with code. And I need to set some validations If data store into different tables.

In the form, both Insert codes are working well but need to set a loop in the subform for multiple rows of data inserting...

As well as need to set record source & recordset object. Please guide me.

 

[MajP]

My thought, If design unbound forms then I can use in 2 ways as same requirements of Sales and Purchases.

You can do that bound. So you want a bound form where you set the recordsource different depending on if it is a sales or purchase. I do not see enough here to determine how to set the recordsource for a purchase vs a sales.

 

[arnelgp]

I go with the rest of the guys, go for Bound form.
you just create one form for Sales and the other for Purchases.

you can do it and we have enough time during lockdown.

 

[sandya]

You can do that bound. So you want a bound form where you set the recordsource different depending on if it is a sales or purchase. I do not see enough here to determine how to set the recordsource for a purchase vs a sales.

Thanks for suggestions... I will try with bound form.