[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5403
  • Last Modified:

Multiple list box items - save to a table Access VBA

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
gtmathewDallas
Asked:
gtmathewDallas
  • 3
  • 2
2 Solutions
 
PatHartmanCommented:
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
 
gtmathewDallasAuthor Commented:
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
 
Dale FyeCommented:
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
Technology Partners: 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!

 
gtmathewDallasAuthor Commented:
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
 
Dale FyeCommented:
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
 
gtmathewDallasAuthor Commented:
Perfect.. Thanks a lot... fyed and PatHartman
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now