Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

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

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Transfer data from list box

Resolved · Urgent Priority · Version 2003

Reply Thu 4 Sep 2008, 10:05Delegate Dawn said...

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

For upcoming training course dates see: Pricing & availability

Reply Thu 4 Sep 2008, 13:59Delegate Dawn said...

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

Access articles

· Access Database Optimization: First Normal Form
· 3 Useful Things You'll Learn in Microsoft Access Training Courses
· How To Use Lookups For Data Entry In Microsoft Access
· A Brief Guide To Table Relationships In Access
· Microsoft Access and Customer Surveys

Please browse our web site to find out more about
access+training and other Microsoft training courses.

Access tip:

Choosing data types in Access

Not sure which data type to use for your Access fields? Here are some guidelines to help you choose a data type to assign to a field.

- The Text data type can accept up to 255 characters. For information that will be recorded in paragraphs, use Memo.

- Also use Text for numbers that aren't going to be used in calculations, e.g. phone numbers.

- Use the Currency data type for monetary amounts.

- Use Date/Time for dates.

- Most other numbers can use the Number data type, but the Field Size property may have to be altered.

- For fields that have only two alternatives (yes, no) use Yes/No data type. If there is likely to be a third entry option (e.g. maybe or don't know) use Text instead.

View all Access hints and tips