Results 1 to 3 of 3
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    Inserting List box items into Table


    This is probably a simple issue, but none of my search results seem to have a solution. I am trying to insert 4 text or combo box values and an item from a list box. The values for the 4 combo/text boxes stays the same for each listbox item. However all of the solutions seem use the .ItemSelected property. I need it to go though each item without the user having to select items. Here is where I am so far.

    Code:
    For intRowCtr = 0 To lst.ListCount - 1
            strShipSQL = "INSERT INTO Shipping (ShippedBy, Company, OrderNo, TrackingNo, ShipMethod, ShipDate) VALUES ('" _
                       & Me.cbxShippedBy & "','" & Me.cbxCompany & "','" & lst.Column(0, intRowCtr) & "','" & Me.tbxShipTracking & "','" & Me.cbxShipMethod & "',#" & dtShipDate & "#)"
            CurrentDb.Execute strShipSQL
    Next

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try it this way:
    Code:
    Dim sOrderNo as string
    For intRowCtr = 0 To lst.ListCount - 1
            sOrderNo = lst.column(0, intRowCtr)
            strShipSQL = "INSERT INTO Shipping (ShippedBy, Company, OrderNo, TrackingNo, ShipMethod, ShipDate) VALUES ('" _
                       & Me.cbxShippedBy & "','" & Me.cbxCompany & "','" & sOrderNo & "','" & Me.tbxShipTracking & "','" & Me.cbxShipMethod & "',#" & dtShipDate & "#)"
            CurrentDb.Execute strShipSQL
    Next
    Also note that if any of the fields in Shipping table are numeric, you don't want them enclosed in single quotes.

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Perfect! Thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Inserting and deletinc items in a database with VBA
    By Business in forum Programming
    Replies: 7
    Last Post: 04-15-2016, 02:30 PM
  2. List of items within a table row
    By hoodoo in forum Database Design
    Replies: 2
    Last Post: 09-21-2015, 10:32 AM
  3. Replies: 5
    Last Post: 04-26-2013, 09:16 PM
  4. List in Report Shows All Items in Table, But I Only Want...
    By italianfinancier in forum Programming
    Replies: 1
    Last Post: 05-28-2011, 02:42 AM
  5. Highlighting Items in List Box from Table Data
    By swalsh84 in forum Programming
    Replies: 2
    Last Post: 01-25-2010, 08:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums