Solved

Multiple list box items - save to a table Access VBA

Posted on 2014-01-20
6
4,675 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 35

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

821 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