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

How do I Use the FileDialog Control Properly

P: 5
Hi,

I need your expert advise on MS access/VB issue. I am very much new to Access and VB programming. I have created a Access database to store information of the children assessed for preventive dental treatment. This database will be used by various field nurses to collect the data. Eventually, after certain period of time, these different databases will be combined to create a master database. And this master database will be updated with new records periodically. I have created one form in the master database where user can select 'field(client) database and append records to master database. I used listbox to select the access database file. The issue is when I select the file via Office.FileDialog then try to append the records, a VB error message appears saying 'invalid use of null'. It appears that the value of listbox is null even though I have selected the file.

I would really appreciate if you could help me figure out what is wrong with code

Here is my code :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.  
  3.    Dim fDialog As Office.FileDialog
  4.    Dim varFile As Variant
  5.  
  6.    ' Clear listbox contents. '
  7.    Me.List3.RowSource = ""
  8.  
  9.  
  10.    ' Set up the File Dialog. '
  11.    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  12.  
  13.    With fDialog
  14.  
  15.       ' Allow user to make multiple selections in dialog box '
  16.       .AllowMultiSelect = False
  17.  
  18.       ' Set the title of the dialog box. '
  19.       .Title = "Please select Access database"
  20.  
  21.       ' Clear out the current filters, and add our own.'
  22.       .Filters.Clear
  23.       .Filters.Add "Access Databases", "*.ACCDB"
  24.       .Filters.Add "All Files", "*.*"
  25.  
  26.       ' Show the dialog box. If the .Show method returns True, the '
  27.       ' user picked at least one file. If the .Show method returns '
  28.       ' False, the user clicked Cancel. '
  29.       If .Show = True Then
  30.  
  31.          'Loop through each file selected and add it to our list box. '
  32.          For Each varFile In .SelectedItems
  33.             Me.List3.AddItem varFile
  34.  
  35.          Next
  36.  
  37.  
  38.       Else
  39.          MsgBox "You clicked Cancel in the file dialog box."
  40.       End If
  41.    End With
  42. End Sub
  43.  
  44.  
  45.  
  46. Private Sub Command5_Click()
  47.  
  48. 'If IsNull(List3.Value) Then
  49. 'MsgBox "Please select the soruce file and try again.", vbOKOnly, "No file selected!"
  50. 'Else
  51.  
  52. Dim strSQL  As String
  53. Dim strvalue As String
  54.  
  55. strvalue = Me.List3.Value
  56.  
  57. strSQL = "INSERT INTO [Patient Level data]" & _
  58. "SELECT *FROM [Patient Level data] t2 IN '" & strvalue & "'" & _
  59. "WHERE NOT EXISTS ( SELECT*FROM [Patient Level data] t1 WHERE ((t2.PatientID<>t1.[PatientID]) AND (t2.First_Name=t1.[First_Name]) AND (t2.[Last Name]=t1.[Last Name])))"
  60.  
  61. DoCmd.Hourglass True
  62.         'Turns off the Access warning messages
  63.         DoCmd.SetWarnings False
  64.             DoCmd.RunSQL strSQL
  65.     DoCmd.Hourglass False
  66.         'Turns the Access warning messages back on
  67.         DoCmd.SetWarnings True
  68.  
  69. DoCmd.Close acForm, "Append", acSaveYes
  70. DoCmd.OpenForm "WelcomePage"
  71.  
  72. 'End If
  73.  
  74. End Sub
Jan 23 '12 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,324
After the file dialog populates the listbox, did you select a file from the listbox before clicking the button?
Jan 23 '12 #2

P: 5
Hi Rabbit,

Thanks for your quick reply.

Yes, I tried to selecting the item with as follow

strvalue = List3.Selected(0)

but it doesn't work


-San
Jan 23 '12 #3

Rabbit
Expert Mod 10K+
P: 12,324
No, I mean did you physically click on one of the files in the listbox?
Jan 23 '12 #4

P: 5
I don't want it to be physically selected. If I select the file via FileDialog I need the value of list box to be that file path.

Thanks
Jan 23 '12 #5

Rabbit
Expert Mod 10K+
P: 12,324
A listbox has no selected value unless you actually click on one.
Jan 23 '12 #6

P: 5
so is there any other way to get the file path and database name from where I need to get the records
Jan 23 '12 #7

Rabbit
Expert Mod 10K+
P: 12,324
You can use the ItemData property of the listbox to iterate through all items in the listbox. But if the goal is to go from file dialog to insert, you can skip the listbox altogether and just use the results from the file dialog.
Jan 23 '12 #8

P: 5
I figured it out.

Thanks a lot for your help
Jan 23 '12 #9

NeoPa
Expert Mod 15k+
P: 31,263
You may find Select a File or Folder using the FileDialog Object helpful, though Rabbit seems to have helped with your basic confusion already.
Jan 23 '12 #10

Post your reply

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