Solved

Multiple list box items - save to a table Access VBA

Posted on 2014-01-20
6
4,723 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

827 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