Issue with Insert data from listbox to table

[Sandhya1988]

Hi Guys!

Please can anyone help on the following code

As an attached Invoice form screenshot, I'm trying to Insert the product details from Listbox to the table with a loop as per below VBA, but it is not working.

Please help where I'm going wrong.

Also, I have attached Db, If it requires more details.


Backend table fields & datatypes:

ID: AutoNumber
Inv_No: Short Text
Item_ID: Short Text
Qty: Number
LP: Number
MRP: Number
C_Disc: Number
GST: Number
IGST: Number
LPR_Total: Number
R_Total: Number

Private Sub btn_Save_Click()


    For i = 0 To ItemsListBox.ListCount - 1 Step 1
    CurrentDb.Execute "INSERT INTO tblSE_B( Inv_No, " & _
                                           "Item_ID," & _
                                           "Qty," & _
                                           "LP, " & _
                                           "MRP," & _
                                           "GST," & _
                                           "IGST," & _
                                           "C_Dsic," & _
                                           "LPR_Total," & _
                                           "R_Total)" & _
                        " VALUES('" & txtInvNo & "','" & _              
                                    ItemsListBox.Column(0, i) & "'," & _
                                    ItemsListBox.Column(1, i) & "," & _ 
                                    ItemsListBox.Column(2, i) & "," & _ 
                                    ItemsListBox.Column(3, i) & "," & _ 
                                    ItemsListBox.Column(4, i) & "," & _ 
                                    ItemsListBox.Column(5, i) & "," & _ 
                                    ItemsListBox.Column(6, i) & "," & _ 
                                    ItemsListBox.Column(8, i) & "," & _ 
                                    ItemsListBox.Column(9, i) & ")"     
          Next i
          MsgBox "Successfully done", vbInformation, "Sales"

End Sub

[twinnyfo]

Sandhya1988,

1) You don't say exactly what is "not working". How is it not working?

2) I would take a different approach, with (should be) identical intended result:

Option Compare Database
Option Explicit

Private Sub btn_Save_Click()
    Dim db      As DAO.Database
    Dim rst     As DAO.Recordset
    Dim intI    As Integer

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblSE_B")
    With rst
        If Not (.BOF And .EOF) Then
            With Me.ItemsListBox
                For intI = 0 To Me.ItemsListBox.ListCount - 1
                    Call rst.AddNew
                    rst!Inv_No = .Column(0, intI)
                    rst!Item_ID = .Column(1, intI)
                    rst!Qty = .Column(2, intI)
                    rst!LP = .Column(3, intI)
                    rst!MRP = .Column(4, intI)
                    rst!GST = .Column(5, intI)
                    rst!IGST = .Column(6, intI)
                    rst!C_Dsic = .Column(7, intI)
                    rst!LPR_Total = .Column(8, intI)
                    rst!R_Total = .Column(9, intI)
                    Call rst.Update
                Next intI
            End With
        End If
        Call .Close
    End With
    Call db.Close
    Set rst = Nothing
    Set db = Nothing

    MsgBox "Successfully done", vbInformation, "Sales"
 
    Exit Sub

End Sub

Hope that hepps!

[Sandhya1988]

@twinnyfo, thanks for replay.

When I tried as of my code, the records are not inserting into the table and did not get any message and also not works debug.

I just tried as you posted code, but getting the only message as per the attached screenshot, records are not inserted into the table and getting same problem no error message and no debug works.

[twinnyfo]

So, please allow me to redirect you again....

Is there a particular reason that you are using a list box to display (and add) your records?

This would be (should be) done so much more easily using a sub-form bound to tblSE_B, with a relationship between it and your Sales Table based upon Inv_No.

This would be a standard design in Access.

[Sandhya1988]

Yes, I know about the bound form process, It's a better process of inserting the data as directly into table. But I have some validations and other requirements. That's the reason I'm trying to thru Listbox.

Please help!

[twinnyfo]

Well, based upon your image, it looks like you have validation in place before you add the data to the List Box. I can only imagine that using a Sub-Form would save some of those steps.

There's no substitute for sound DB design. It looks like you have the basics in place. On the surface, there is no advantage to using a list box.

At the same time, the simple fact that NOTHING happens with this code indicates that SOMETHING is awry. Are you sure the code is even executing? You should be able to step through the code while it is running and see what is happening.

[Sandhya1988]

Yes, I need to place validations before adding the data. Before set the validations I should check the code execution if once it works well, I will set all validations.

Mostly, I'm using only bound subforms in this type of cases. But this is the deferent requirement.

And Sure, the code is even not executing and did not hit the breakpoint when I'm trying to debug.

Is it not possible to go with the only Listbox? I just want to Insert the data from Listbox.

[twinnyfo]

Well, if it's not hitting the breakpoint when you step through the code, obviously something else is going wrong. Is the code even entering the sub? If so, where does it stop? If not, double check to make sure that this sub is associated with the button you are clicking.

Other than that, we can make it work with a list box, but a bound subform will and should and ought to do things just the same. Validation of your data has nothing to do with it. It is just a better design to use a bound subform. In fact, I've never seen anyone try to do things this way. It could be that your list box isn't bound to anything, and thus, it may not be storing the data properly for usage (I don't know terribly much about list boxes as I use them so infrequently).

[Sandhya1988]

Yes, Something is going wrong in my database. I did copy and past both Forms and tables into a new database and tested the debug then it is working well!

Also, I tried as you posted code and my code, both are executing as well!

Thanks for your suggestions and help!

[twinnyfo]

Glad to hear it. Sometimes Access has a mind of its own.