Solved

Multiple list box items - save to a table Access VBA

Posted on 2014-01-20
6
4,808 Views
Last Modified: 2014-01-20
Hi Experts I am trying to add items [with multiple columns(3)] in a list box to a table fields. Can you please help me to get it finished, Please help,
Thanks
0
Comment
Question by:gtmathewDallas
  • 3
  • 2
6 Comments
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
ID: 39794744
Usually, you only need to save the ID column.  Sometimes, you want to copy columns from the combo or list box for reference to unbound controls.  To do that:

Me.somecontrol = Me.cboMyCombo.Column.(1)
Me.somecontrol2 = Me.cboMyCombo.Column.(2)

The Columns of a combo or listbox are a zero-based array.  That means that the first column is (0) and is usually the bound column.  The second column is (1), the third is (2), etc.  Open the RowSource query and count the columns to determine what its column number will be.
0
 

Author Comment

by:gtmathewDallas
ID: 39794793
Its an unbound form, purely using vba code to insert the data, I think i have to use some loops in vba to save it.. please help
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39795008
Try something like:
Dim varItem as Variant
Dim strSQL as string

for each varItem in me.lstBoxName.ItemsSelected

    strSQL = "INSERT INTO yourTable (field1, field2, field3) " _
                & "Values(" & me.lstBoxName.column(0, varItem) & ", " _
                                    & me.lstBoxName.column(1, varItem) & ", " _
                                    & me.lstboxname.column(2, varItem) & ")"
    currentdb.Execute strsql, dbfailonerror

Next

Open in new window

If you declare a variant (varItem) you can then loop through the list of records returned by the ItemsSelected property of the list, and use that value as the [Row] argument of the listboxes column property.

Note the code above assumes that each of those values is numeric, which is rarely the case.  and like Pat mentions above, you rarely need to write all of the field values of the list to a new table, generally just the PK associated with each of those items.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gtmathewDallas
ID: 39795155
Its working to add one data, but I have to add all the data from the list box.
There is no selected items in this listbox(List2).  the items in this list box(List2) is added from another list box (list1).

I used the method in the below link to move the item from List1 to List2.
http://support.microsoft.com/kb/278378

Here is my current code,

        Dim varItem1 As Variant
        Dim strSQL1 As String

        For Each varItem1 In Me.List2.ItemsSelected

        strSQL1 = "INSERT INTO Employee_Room_Status (L_Name, F_Name,M_Name, Person_Number, Room_ID, Date_Added) Values ('" & Me.List2.Column(0, varItem1) & "', '" & Me.List2.Column(1, varItem1) & "', '" & Me.List2.Column(2, varItem1) & "', '" & Me.List2.Column(3, varItem1) & "','" & lastID & "',Date())"
         
        CurrentDb.Execute strSQL1, dbFailOnError

        Next
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 450 total points
ID: 39795249
If there are no items selected in the list, then you just need to loop through the rows in the list:
Dim intLoop as integer

for intLoop = 0 to me.list2.listcount - 1

        strSQL1 = "INSERT INTO Employee_Room_Status " _
                 & "(L_Name, F_Name, M_Name, Person_Number, Room_ID, Date_Added) " _
                 & "Values ('" & Me.List2.Column(0, intLoop) & "', '" _
                               & Me.List2.Column(1, intLoop) & "', '" _
                               & Me.List2.Column(2, intLoop) & "', '" _
                               & Me.List2.Column(3, intLoop) & "','" _
                               & lastID & "',Date())"
         
        CurrentDb.Execute strSQL1, dbFailOnError

Next intLoop

Open in new window

Seems odd though that all of those field types are strings.  If they are not strings, then you do not need to wrap them in single quotes the way you have.
0
 

Author Closing Comment

by:gtmathewDallas
ID: 39795301
Perfect.. Thanks a lot... fyed and PatHartman
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question