transfer data list box

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Transfer data from list box

Transfer data from list box

resolvedResolved · Urgent Priority · Version 2003

Dawn has attended:
Access VBA course

Transfer data from list box

I have a multi column list box where I want the user to select the items that they want and for the selected items to be inserted into a table. This is the code I'm using:

For i = 0 To List3.ListCount - 1

If Me.List3.Selected(i) = True Then

CurrentDb.Execute "INSERT INTO tblOrderDetails (ODJobID, ODItemNo, ODQty, ODPrice, ODDesc,ODDrawingNo) VALUES (" & Me.Text8 & ", " & Me.List3.ItemData(i) & "," & Me.List3.Column(2) & ", " & Me.List3.Column(3) & ", '" & Me.List3.Column(4) & "','" & IIf(IsNull(Me.List3.Column(5)), "", Me.List3.Column(5)) & "')"

End If
Next i

If there are 2 items select from the list it will insert 2 rows into the table but it's the same 2 rows, ie:

Item No Quantity Description Drawing No (+) Unit Price Line Total Req Del Date Act Del Date Entered Entered By
4 1 Test quote 2 item 2 175

RE: Transfer data from list box

Managed to find the solution.

For i = 0 To List3.ListCount - 1

If Me.List3.Selected(i) = True Then

CurrentDb.Execute "INSERT INTO tblOrderDetails (ODJobID, ODItemNo, ODQty, ODPrice, ODDesc,ODDrawingNo) VALUES (" & Me.Text8 & ", " & Me.List3.Column(1, i) & "," & Me.List3.Column(2, i) & ", " & Me.List3.Column(3, i) & ", '" & Me.List3.Column(4, i) & "', '" & Me.List3.Column(5, i) & " ')"

End If
Next i

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Access tip:

Create An Inaccessible Field On A Form

There is sometimes a need to make a field in a form look like a normal field but be completely inaccessible to the user. To do this:

In the Field's Properties
Set the ENABLED property to No
Set the LOCKED property to Yes

The field will not grey out, but the user will not be able to do anything with it.

View all Access hints and tips


Server loaded in 0.07 secs.