By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,799 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,799 IT Pros & Developers. It's quick & easy.

Adding list of files to listbox using open file dialog

P: 55
Not sure how to do that, but will try.
I had a text box that I used to upload files to the database, it works fine. I have since changed the text box to a listbox. I am having a problem though with trying to add multiple files to a listbox using the file open dialog so that they can be added to the database.

On running I can an error and am sure the error is on this part....
Expand|Select|Wrap|Line Numbers
  1. result = .Show
  2.         If (result <> 0) Then
  3.             fileName = Trim(.SelectedItems.Item(1))
  4.             Me![MyFile].Visible = True
  5.             Me![MyFile].SetFocus
  6.             Me![MyFile].Text = fileName
  7.             Me![Company_Name].SetFocus
  8.             Me![MyFile].Visible = True
  9.         End If
Please help me..!

Lebbsy
Jun 12 '07 #1
Share this Question
Share on Google+
24 Replies

P: 55
I had a text box that I used to upload files to the database, it works fine. I have since changed the text box to a listbox. I am having a problem though with trying to add multiple files to a listbox using the file open dialog so that they can be added to the database.

On running I can an error and am sure the error is on this part....
Expand|Select|Wrap|Line Numbers
  1. result = .Show
  2.         If (result <> 0) Then
  3.             fileName = Trim(.SelectedItems.Item(1))
  4.             Me![MyFile].Visible = True
  5.             Me![MyFile].SetFocus
  6.             Me![MyFile].Text = fileName
  7.             Me![Company_Name].SetFocus
  8.             Me![MyFile].Visible = True
  9.         End If
Please help me..!

Lebbsy
Please help me with the above, I have a presentation tomorrow at 11 am(in less than 24hrs) and that functionality is one of the key functionalities that my bosses want to see.

Regards
Jun 14 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
Please help me with the above, I have a presentation tomorrow at 11 am(in less than 24hrs) and that functionality is one of the key functionalities that my bosses want to see.

Regards
Lebbsy,

I found an excellent reference for you with source code. See this link, starting with the topic, "Finding more than one file"
http://vb123.com/toolshed/03_docs/filedialog.htm

I tried adaptng the relevant portions of the code supplied in the reference in place of your code. However, you did no supply all of your code, so I don't whether more adaptation is needed, particularly with respect to the file dialog object. To quote from the article:"I use the FileDialog object for is to select the file paths of multiple picture files. In Figure 4, not only have I turned on multi-select but also I have changed the the file picker to preview mode to see samples of the images before I select them." Let me know.

Set the multi-slelect property of your listbox to true.

Expand|Select|Wrap|Line Numbers
  1. Dim vrtSelectedItem As Variant
  2. Dim lstStr As String
  3.  
  4.   result = .Show
  5.         If (result <> 0) Then
  6.                     lstStr = ""
  7.             For Each vrtSelectedItem In .SelectedItems
  8.                  lstStr = lstStr & vrtSelectedItem & ";"
  9.            Next vrtSelectedItem
  10.        End If
  11.        lstFilesSelected.RowSource = lstSt
  12.  
Jun 14 '07 #3

FishVal
Expert 2.5K+
P: 2,653
I had a text box that I used to upload files to the database, it works fine. I have since changed the text box to a listbox. I am having a problem though with trying to add multiple files to a listbox using the file open dialog so that they can be added to the database.

On running I can an error and am sure the error is on this part....
Expand|Select|Wrap|Line Numbers
  1. result = .Show
  2.         If (result <> 0) Then
  3.             fileName = Trim(.SelectedItems.Item(1))
  4.             Me![MyFile].Visible = True
  5.             Me![MyFile].SetFocus
  6.             Me![MyFile].Text = fileName
  7.             Me![Company_Name].SetFocus
  8.             Me![MyFile].Visible = True
  9.         End If
Please help me..!

Lebbsy

Try this.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnPickFiles_Click()
  3.  
  4.     Dim dlgPickFiles As Office.FileDialog
  5.  
  6.     Set dlgPickFiles = Application.FileDialog(msoFileDialogFilePicker)
  7.  
  8.     With dlgPickFiles
  9.         .AllowMultiSelect = True
  10.         .Show
  11.     End With
  12.  
  13.     For Each FileSelected In dlgPickFiles.SelectedItems
  14.         Me.lstFileNames.AddItem FileSelected
  15.     Next
  16.  
  17.     Set dlgPickFiles = Nothing
  18.  
  19. End Sub
  20.  
  21.  
Good luck.
Jun 14 '07 #4

P: 55
Thank you for your help guys. Here is the function that I ended up with from http://vb123.com/toolshed/03_docs/filedialog.htm. It can select files but is not looping to pick up more than one file and the selected file is not updating in the database.

Expand|Select|Wrap|Line Numbers
  1. Function getFileName(Optional FileName, _
  2. Optional FolderPath, Optional FileType) As String
  3.     ' Displays the Office File Open dialog to choose a file name
  4.     ' for the current employee record.  If the user selects a file
  5.     ' display it in the image control.
  6. Dim fd As FileDialog, lstStr As String
  7. Const ALLFILES = "All Files"
  8. Const ALLFILETYPES = "*.jpg;*.bmp;*.gif;*.tif;*.xls;*.*"
  9.  
  10. On Error GoTo Error_getFileName
  11. lstStr = ""
  12.  
  13. 'Create a FileDialog object as a File Picker.
  14. Set fd = Application.FileDialog( _
  15. msoFileDialogFilePicker)
  16.  
  17. Dim vrtSelectedItem As Variant
  18.  
  19. With fd
  20.   .Filters.Clear
  21.   .InitialView = msoFileDialogViewPreview
  22.   .InitialFileName = ""
  23.   .Filters.Add ALLFILES, ALLFILETYPES
  24. If .Show = True Then
  25.     lstStr = ""
  26.     For Each vrtSelectedItem In .SelectedItems
  27.       lstStr = lstStr & vrtSelectedItem & ";"
  28.     Next vrtSelectedItem
  29.   Else
  30.     'The user pressed Cancel.
  31.   End If
  32. End With
  33.  
  34. Exit_getFileName:
  35. 'Set the object variable to Nothing.
  36. Set fd = Nothing
  37. MyFile.RowSource = lstStr
  38. Exit Function
  39.  
  40. Error_getFileName:
  41.   MsgBox "File not saved, pls try again" & Err.Description
  42.   cmdselect = ""
  43.   'GoTo cmdselect_Exit
  44.   Resume Exit_getFileName
  45.  
  46. End Function
Regards,
Jun 15 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
Lebbsy,
Glad you got your problem resolved in time to impress your boss!! I had a feeling you were going to like that link to the ToolShed.
Jun 15 '07 #6

P: 55
Lebbsy,
Glad you got your problem resolved in time to impress your boss!! I had a feeling you were going to like that link to the ToolShed.
Thanx...! Looking at the code though, I have a problem with error handling. Please check to see if I have handled my errors correctly because I think that's what's contributing to my problem.

Regards,
Jun 18 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
Thanx...! Looking at the code though, I have a problem with error handling. Please check to see if I have handled my errors correctly because I think that's what's contributing to my problem.

Regards,
Hi Lebbsy,

You forgot to tell me what is wrong with your error code, but I think I found it. Also, make sure you have no missing vb Library references, and that you've set the Allow Multi-select propery to True.

Change this:
Expand|Select|Wrap|Line Numbers
  1. Error_getFileName:
  2.   MsgBox "File not saved, pls try again" & Err.Description
  3.   cmdselect = ""
  4.   'GoTo cmdselect_Exit
  5.   Resume Exit_getFileName
  6.  
To this:
Expand|Select|Wrap|Line Numbers
  1. Error_getFileName:
  2.   MsgBox "File not saved, pls try again" & Err.Description
  3.   Resume Exit_getFileName
  4.  
Jun 18 '07 #8

P: 55
Hi Lebbsy,

You forgot to tell me what is wrong with your error code, but I think I found it. Also, make sure you have no missing vb Library references, and that you've set the Allow Multi-select propery to True.

Change this:
Expand|Select|Wrap|Line Numbers
  1. Error_getFileName:
  2.   MsgBox "File not saved, pls try again" & Err.Description
  3.   cmdselect = ""
  4.   'GoTo cmdselect_Exit
  5.   Resume Exit_getFileName
  6.  
To this:
Expand|Select|Wrap|Line Numbers
  1. Error_getFileName:
  2.   MsgBox "File not saved, pls try again" & Err.Description
  3.   Resume Exit_getFileName
  4.  
It can select only 1 file but is not looping to pick up more than one file and the selected file is not updating in the database. The Allow Multi-select property is set to Extended.
Jun 18 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
It can select only 1 file but is not looping to pick up more than one file and the selected file is not updating in the database. The Allow Multi-select property is set to Extended.
Lebbsy,
In addition to the correction I gave you in my prior post, I think you need to correct this line: Your code is in a public module...therefore, all references have to be fully qualified as shown. Replace YourFormName with your actual form name.

MyFile.RowSource = lstStr

to:

Forms!YourFormName!MyFile.RowSource = lstStr

Try the above changes and let me know what happens.
Jun 18 '07 #10

P: 55
Lebbsy,
In addition to the correction I gave you in my prior post, I think you need to correct this line: Your code is in a public module...therefore, all references have to be fully qualified as shown. Replace YourFormName with your actual form name.

MyFile.RowSource = lstStr

to:

Forms!YourFormName!MyFile.RowSource = lstStr

Try the above changes and let me know what happens.
There is still no difference. It only select one file and is still not looping.

Rgds
Jun 18 '07 #11

puppydogbuddy
Expert 100+
P: 1,923
There is still no difference. It only select one file and is still not looping.

Rgds

Lebbsy,
Change the Form Name on line #42 to your actual form name. Then try the code below and let me know what happens:


Expand|Select|Wrap|Line Numbers
  1. Function getFileName(Optional FileName, _
  2. Optional FolderPath, Optional FileType) As String
  3.     ' Displays the Office File Open dialog to choose a file name
  4.     ' for the current employee record.  If the user selects a file
  5.     ' display it in the image control.
  6. Dim fd As FileDialog, lstStr As String
  7. Const ALLFILES = "All Files"
  8. Const ALLFILETYPES = "*.jpg;*.bmp;*.gif;*.tif;*.xls;*.*"
  9.  
  10. On Error GoTo Error_getFileName
  11. lstStr = ""
  12.  
  13. 'Create a FileDialog object as a File Picker.
  14. Set fd = Application.FileDialog(msoFileDialogFilePicker)
  15.  
  16. Dim vrtSelectedItem As Variant
  17.  
  18. If IsMissing(FileName) Then
  19.   FileName = ""
  20. End If
  21.  
  22. If IsMissing(FolderPath) Then
  23.   FolderPath = ""
  24. Else
  25.   If Right(FolderPath, 1) <> "\" Then
  26.     FolderPath = FolderPath & "\"
  27.   End If
  28. End If
  29.  
  30.  
  31. With fd
  32.   .Filters.Clear
  33.   .InitialView = msoFileDialogViewPreview
  34.   .InitialFileName = ""
  35.   .Filters.Add ALLFILES, ALLFILETYPES
  36.   .AllowMultiSelect = True
  37. If .Show = True Then
  38.     lstStr = ""
  39.     For Each vrtSelectedItem In .SelectedItems
  40.       lstStr = lstStr & vrtSelectedItem & ";"
  41.     Next vrtSelectedItem
  42.     Forms!YourForm!MyFile.RowSource = lstStr
  43.   Else
  44.     'The user pressed Cancel.
  45.   End If
  46. End With
  47.  
  48. Exit_getFileName:
  49. 'Set the object variable to Nothing.
  50. Set fd = Nothing
  51. Exit Function
  52.  
  53. Error_getFileName:
  54.   MsgBox "File not saved, pls try again" & Err.Description
  55.   Resume Exit_getFileName
  56.  
  57. End Function
  58.  
Jun 18 '07 #12

P: 55
I did exactly what you said above and it can now add the selected file to the database. The problem though remains not being able to select and add multiple files to the database.
Jun 19 '07 #13

puppydogbuddy
Expert 100+
P: 1,923
I did exactly what you said above and it can now add the selected file to the database. The problem though remains not being able to select and add multiple files to the database.

Lebbsy,
1. The code looks ok to me. Put this message box between line 42 and 43 above so it will display the contents of lstStr:

MsgBox "lstStr = " & lstStr


2. Make sure references are set for msoFileDialogFilePicker and the Microsoft Office Object Library

3. Change multi-select property of the listbox from Extended to Simple.


Let me know what happens.
Jun 19 '07 #14

P: 55
Lebbsy,
1. The code looks ok to me. Put this message box between line 42 and 43 above so it will display the contents of lstStr:

MsgBox "lstStr = " & lstStr


2. Make sure references are set for msoFileDialogFilePicker and the Microsoft Office Object Library

3. Change multi-select property of the listbox from Extended to Simple.


Let me know what happens.
It displays lstStr =C:\<filepath>\Lebbsy.pdf; The office object library is on and the multi-select property is set to simple
Jun 19 '07 #15

puppydogbuddy
Expert 100+
P: 1,923
It displays lstStr =C:\<filepath>\Lebbsy.pdf; The office object library is on and the multi-select property is set to simple
Lebbsy,
Does lstStr contain a list of the files you selected? I can't tell from the answer you provided.

What happened after you changed Multi-select to simple? Please provide enough detail so I can tell what is happening.

Thanks.
Jun 19 '07 #16

P: 55
Lebbsy,
Does lstStr contain a list of the files you selected? I can't tell from the answer you provided.

What happened after you changed Multi-select to simple? Please provide enough detail so I can tell what is happening.

Thanks.
After selecting a file lstStr displays the complete filepath and selects the file, but on trying to select another file, the first selected file is displaced and the new one is displayed. The same thing happens when Multi-select is set to simple.

Like I said, selecting a file is no problem, the problem comes when I want to add another file to the listbox, instead of adding the file to the next row, it displaces the already selected file.

Regards,
Jun 20 '07 #17

puppydogbuddy
Expert 100+
P: 1,923
After selecting a file lstStr displays the complete filepath and selects the file, but on trying to select another file, the first selected file is displaced and the new one is displayed. The same thing happens when Multi-select is set to simple.

Like I said, selecting a file is no problem, the problem comes when I want to add another file to the listbox, instead of adding the file to the next row, it displaces the already selected file.

Regards,
Lebbsy,
Did you set the RecordSourceType property of the list box to Value List as per the instructions? If you did not, that might be part of the problem. Let me know.
Jun 20 '07 #18

P: 55
Lebbsy,
Did you set the RecordSourceType property of the list box to Value List as per the instructions? If you did not, that might be part of the problem. Let me know.
Yes the row source type property of the listbox is set to Value List.
Jun 20 '07 #19

puppydogbuddy
Expert 100+
P: 1,923
Yes the row source type property of the listbox is set to Value List.
Ok, try inserting the following code between lines 36 and 37 (the line before "If show = True then")
Expand|Select|Wrap|Line Numbers
  1. .Show = True
  2.  
tell me what happens now
Jun 20 '07 #20

FishVal
Expert 2.5K+
P: 2,653
After selecting a file lstStr displays the complete filepath and selects the file, but on trying to select another file, the first selected file is displaced and the new one is displayed. The same thing happens when Multi-select is set to simple.

Like I said, selecting a file is no problem, the problem comes when I want to add another file to the listbox, instead of adding the file to the next row, it displaces the already selected file.

Regards,
Hi!

I beg your pardon for my silly question.
Do you want to add to the listbox multiple files selected in a single FileDialog or add files to the listbox successively via several FileDialog openings?
Jun 20 '07 #21

P: 55
Hi!

I beg your pardon for my silly question.
Do you want to add to the listbox multiple files selected in a single FileDialog or add files to the listbox successively via several FileDialog openings?
The question is not silly, you are just trying to help. I am trying to do the latter: Add files to the listbox successively via several FileDialog openings
Jun 21 '07 #22

FishVal
Expert 2.5K+
P: 2,653
The question is not silly, you are just trying to help. I am trying to do the latter: Add files to the listbox successively via several FileDialog openings
Thanks. Let me start explanation. I will reference to the code posted in msg#12.

Pay attention to row #42. What does it do? It simply replaces listbox rowsource with a new value. Every time you open FileDialog and select files it will replace a previous files list with a new one. To have an opportunity to add files successively change the code to smthng like

Expand|Select|Wrap|Line Numbers
  1. Forms!YourForm!MyFile.RowSource = Forms!YourForm!MyFile.RowSource & lstStr
  2.  
Symbols like ";" and "," generally acceptable in file names, are reserved symbols in Access.ListBox.RowSource used to separate rows and columns unless they are wrapped with quotation marks. This means that if you try to add "file;name.xxx", two rows "file" and "name.xxx" will be added, but "'file;name.xxx'" is added properly. So make changes to code in row#40.

Expand|Select|Wrap|Line Numbers
  1. lstStr = lstStr & "'" & vrtSelectedItem & "';"
  2.  
Good luck.
Jun 21 '07 #23

P: 55
Thanks. Let me start explanation. I will reference to the code posted in msg#12.

Pay attention to row #42. What does it do? It simply replaces listbox rowsource with a new value. Every time you open FileDialog and select files it will replace a previous files list with a new one. To have an opportunity to add files successively change the code to smthng like

Expand|Select|Wrap|Line Numbers
  1. Forms!YourForm!MyFile.RowSource = Forms!YourForm!MyFile.RowSource & lstStr
  2.  
Symbols like ";" and "," generally acceptable in file names, are reserved symbols in Access.ListBox.RowSource used to separate rows and columns unless they are wrapped with quotation marks. This means that if you try to add "file;name.xxx", two rows "file" and "name.xxx" will be added, but "'file;name.xxx'" is added properly. So make changes to code in row#40.

Expand|Select|Wrap|Line Numbers
  1. lstStr = lstStr & "'" & vrtSelectedItem & "';"
  2.  
Good luck.
It works perfectly, one again thanks. Its not updating the database though, but will try to work it out overnight I let you know whats happens tomorrow.

Bye for Now.....!
Jun 21 '07 #24

FishVal
Expert 2.5K+
P: 2,653
It works perfectly, one again thanks. Its not updating the database though, but will try to work it out overnight I let you know whats happens tomorrow.

Bye for Now.....!
Glad my advice was helpful.
Jun 21 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.