By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,142 Members | 792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,142 IT Pros & Developers. It's quick & easy.

Issue with Insert data from listbox to table

P: 30
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_Save_Click()
  2.  
  3.  
  4.     For i = 0 To ItemsListBox.ListCount - 1 Step 1
  5.     CurrentDb.Execute "INSERT INTO tblSE_B( Inv_No, " & _
  6.                                            "Item_ID," & _
  7.                                            "Qty," & _
  8.                                            "LP, " & _
  9.                                            "MRP," & _
  10.                                            "GST," & _
  11.                                            "IGST," & _
  12.                                            "C_Dsic," & _
  13.                                            "LPR_Total," & _
  14.                                            "R_Total)" & _
  15.                         " VALUES('" & txtInvNo & "','" & _              
  16.                                     ItemsListBox.Column(0, i) & "'," & _
  17.                                     ItemsListBox.Column(1, i) & "," & _ 
  18.                                     ItemsListBox.Column(2, i) & "," & _ 
  19.                                     ItemsListBox.Column(3, i) & "," & _ 
  20.                                     ItemsListBox.Column(4, i) & "," & _ 
  21.                                     ItemsListBox.Column(5, i) & "," & _ 
  22.                                     ItemsListBox.Column(6, i) & "," & _ 
  23.                                     ItemsListBox.Column(8, i) & "," & _ 
  24.                                     ItemsListBox.Column(9, i) & ")"     
  25.           Next i
  26.           MsgBox "Successfully done", vbInformation, "Sales"
  27.  
  28. End Sub
Attached Images
File Type: jpg INV_Form.jpg (142.2 KB, 23 views)
Attached Files
File Type: zip EleProject.zip (824.3 KB, 17 views)
Apr 22 '20 #1

✓ answered by 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).

Share this Question
Share on Google+
9 Replies

twinnyfo
Expert Mod 2.5K+
P: 3,521
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub btn_Save_Click()
  5.     Dim db      As DAO.Database
  6.     Dim rst     As DAO.Recordset
  7.     Dim intI    As Integer
  8.  
  9.     Set db = CurrentDb()
  10.     Set rst = db.OpenRecordset("tblSE_B")
  11.     With rst
  12.         If Not (.BOF And .EOF) Then
  13.             With Me.ItemsListBox
  14.                 For intI = 0 To Me.ItemsListBox.ListCount - 1
  15.                     Call rst.AddNew
  16.                     rst!Inv_No = .Column(0, intI)
  17.                     rst!Item_ID = .Column(1, intI)
  18.                     rst!Qty = .Column(2, intI)
  19.                     rst!LP = .Column(3, intI)
  20.                     rst!MRP = .Column(4, intI)
  21.                     rst!GST = .Column(5, intI)
  22.                     rst!IGST = .Column(6, intI)
  23.                     rst!C_Dsic = .Column(7, intI)
  24.                     rst!LPR_Total = .Column(8, intI)
  25.                     rst!R_Total = .Column(9, intI)
  26.                     Call rst.Update
  27.                 Next intI
  28.             End With
  29.         End If
  30.         Call .Close
  31.     End With
  32.     Call db.Close
  33.     Set rst = Nothing
  34.     Set db = Nothing
  35.  
  36.     MsgBox "Successfully done", vbInformation, "Sales"
  37.  
  38.     Exit Sub
  39.  
  40. End Sub
Hope that hepps!
Apr 22 '20 #2

P: 30
@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.
Attached Images
File Type: jpg InvError1.jpg (99.0 KB, 22 views)
Apr 22 '20 #3

twinnyfo
Expert Mod 2.5K+
P: 3,521
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.
Apr 22 '20 #4

P: 30
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!
Apr 22 '20 #5

twinnyfo
Expert Mod 2.5K+
P: 3,521
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.
Apr 22 '20 #6

P: 30
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.
Apr 22 '20 #7

twinnyfo
Expert Mod 2.5K+
P: 3,521
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).
Apr 22 '20 #8

P: 30
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!
Apr 24 '20 #9

twinnyfo
Expert Mod 2.5K+
P: 3,521
Glad to hear it. Sometimes Access has a mind of its own.
Apr 24 '20 #10

Post your reply

Sign in to post your reply or Sign up for a free account.