?
Solved

Multiple list box items - save to a table Access VBA

Posted on 2014-01-20
6
Medium Priority
?
5,105 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 200 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 48

Expert Comment

by:Dale Fye
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 48

Accepted Solution

by:
Dale Fye earned 1800 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

765 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