How do I Use the FileDialog Control Properly

[San134]

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 :

Private Sub Command2_Click()
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
   ' Clear listbox contents. '
   Me.List3.RowSource = ""
 
 
   ' Set up the File Dialog. '
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
 
      ' Allow user to make multiple selections in dialog box '
      .AllowMultiSelect = False
 
      ' Set the title of the dialog box. '
      .Title = "Please select Access database"
 
      ' Clear out the current filters, and add our own.'
      .Filters.Clear
      .Filters.Add "Access Databases", "*.ACCDB"
      .Filters.Add "All Files", "*.*"
 
      ' Show the dialog box. If the .Show method returns True, the '
      ' user picked at least one file. If the .Show method returns '
      ' False, the user clicked Cancel. '
      If .Show = True Then
 
         'Loop through each file selected and add it to our list box. '
         For Each varFile In .SelectedItems
            Me.List3.AddItem varFile
 
         Next
 
 
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With
End Sub
 
 
 
Private Sub Command5_Click()
 
'If IsNull(List3.Value) Then
'MsgBox "Please select the soruce file and try again.", vbOKOnly, "No file selected!"
'Else
 
Dim strSQL  As String
Dim strvalue As String
 
strvalue = Me.List3.Value
 
strSQL = "INSERT INTO [Patient Level data]" & _
"SELECT *FROM [Patient Level data] t2 IN '" & strvalue & "'" & _
"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])))"
 
DoCmd.Hourglass True
        'Turns off the Access warning messages
        DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
    DoCmd.Hourglass False
        'Turns the Access warning messages back on
        DoCmd.SetWarnings True
 
DoCmd.Close acForm, "Append", acSaveYes
DoCmd.OpenForm "WelcomePage"
 
'End If
 
End Sub

[Rabbit]

After the file dialog populates the listbox, did you select a file from the listbox before clicking the button?

[San134]

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

[Rabbit]

No, I mean did you physically click on one of the files in the listbox?

[San134]

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

[Rabbit]

A listbox has no selected value unless you actually click on one.

[San134]

so is there any other way to get the file path and database name from where I need to get the records

[Rabbit]

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.

[San134]

I figured it out.

Thanks a lot for your help

[NeoPa]

You may find Select a File or Folder using the FileDialog Object helpful, though Rabbit seems to have helped with your basic confusion already.