Inserting List box items into Table

[dccjr3927]

I am having an issue inserting listbox items into a table. I have the following code. Ihave a table with OrderNo, FilePath0, FilePath1,...File Path4. My users should be able to select up to 5 items, and have them copy the file paths into each field from the listbox.

If Len(Me.lstAttach & vbNullString) = 0 Then
     For i = 1 To Me.lstAttach.ListCount
      strFileName = "E:\Databases-DO NOT MOVE\LE Tracking System\DefectAttach\" & Me.tbxOrderNum.Value & "-" & i
      strListSource = Me.lstAttach.ListIndex = (i)
      iAttachSQL = "INSERT INTO DefectAttach (OrderNo, FilePath" & (i) & ") VALUES (" & Me.tbxOrderNum & "," & Me.lstAttach.ListIndex = (i) & ");"
      CurrentDb.Execute iAttachSQL
      FileCopy strListSource, strFileName
    Next i
Else 
End If

I am getting a Run Time 3078 "The Microsoft Access database engine cannot find the input table or quesry 'False'. " The table name is correct and the link is refreshed. Any ideas? Is the code itself correct?

 

[theDBguy]

Hi. Suggest you add a Debug.Print iAttachSQL line to your code to see what you're trying to execute and help figure out the cause of the error.

 

[dccjr3927]

It is returning 'False'

Since the list box items are not selected, do I need to cycle the selection, and remove each item as it does so? If so, how I code that?

 

[Mark_]

Welcome to the FORUMS!!!

What is returning "False"?

More specific, for your code you want to go through every entry in a list box and then add it to a table AND copy files?

I'm not understanding why you have the line

 strListSource = Me.lstAttach.ListIndex = (i)

in your code. What are you trying to do with this?

 

[dccjr3927]

The debug line for iAttachSQL is returning 'False' in the Immediate Window.

Yes. The attachments (file paths) added to the listbox are on each users PC, and I am wanting to save the order number and file path for each attachment to the table. I also want to copy the attachment from their computer to a shared drive on the server. I know that is isn't a good idea to store the actual attachment in the database, so this just stored the file path.

The intent of the line of code in question was to use the file path in the listbox as the "From" argument of the FileCopy method, and the strFileName as the "To" argument. I was trying to iterate through each one as I saved to the table.

 

[MajP]

Need to look at the itemsselected property

https://docs.microsoft.com/en-us/office/vba/api/access.listbox.itemsselected

Example of looping the selected items and getting the value.

Sub BoundData() 
 Dim frm As Form, ctl As Control 
 Dim varItm As Variant 
 
 Set frm = Forms!Contacts 
 Set ctl = frm!Names 
 For Each varItm In ctl.ItemsSelected 
 Debug.Print ctl.ItemData(varItm) 
 Next varItm 
End Sub

 

[dccjr3927]

How would I select all of the added items in the code? They are selecting the attachments from a File Picker, but none are "selected". Is there a way to code to select the first item, complete that iteration, delete that item, then repeat for the next?